Syntax error with an access query in ASP

Sipex · #1 (**permalink**) 08-14-09, 15:54

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.

gvee · #2 (**permalink**) 08-17-09, 08:49

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?

Sipex · #3 (**permalink**) 08-17-09, 10:57

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.

Teddy · #4 (**permalink**) 08-17-09, 12:21

Look in to a prepared statement. Then you won't have to worry about it, for the most part.

Sipex · #5 (**permalink**) 08-17-09, 14:06

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

Internet Services

Web Development

Digital Marketing

Consumer Tech