Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2009
    Posts
    3

    Unanswered: Syntax error with an access query in ASP

    Hi everyone, I'm new here and I've been having trouble with a query I've been working on lately.

    strUSQL = "INSERT INTO tblNotAvail(RTime,RDate) VALUES(SELECT tblTimes.RTime FROM tblTimes WHERE tblTimes.RTime NOT IN(SELECT tblRequests.RTime FROM tblRequests WHERE tblRequests.Rdate = " & strDate & "),#" & strDate & "#);"

    When this query is run I get a syntax error focused on the bolded section.

    Also, just for general information I have checked out the strDate variable and it seems ok. Example value for it would be: 8/19/2009

    I know I'm just overlooking it so a second pair of eyes will help immensely.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You cannot combine VALUES() and SELECT - they're pretty exclusive to one and other.
    Code:
    INSERT INTO tblNotAvail(RTime, RDate)
    SELECT tblTimes.RTime
         , strDate
    FROM   tblTimes
     LEFT
      JOIN (
            SELECT RTime
            FROM   tblRequests
            WHERE  Rdate = strDate
           ) As tblRequests
        ON tblRequests.RTime = tblTimes.RTime
    WHERE  tblRequests.RTime IS NULL

    P.S. you know what SQL Injection is, right?
    George
    Home | Blog

  3. #3
    Join Date
    Aug 2009
    Posts
    3
    Thanks for your input! I'll let you know if it works

    Actually, to be honest I didn't know what an Injection was until I googled it, but it makes perfect sense. It's an old app so there's a lot of violations like this one but I'll sanitize the input here and see if I can get approval to fix up the rest later.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Look in to a prepared statement. Then you won't have to worry about it, for the most part.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Aug 2009
    Posts
    3
    Alright, it worked although I did have to specify that within the JOIN the WHERE clause referred to tblRequests.Rdate and not tblTimes.Rdate

    Thanks a lot

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •