(missing operator) in query expression
Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=800
Printed Date: 29 March 2026 at 2:55am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com
Topic: (missing operator) in query expression
Posted By: pedalcars
Subject: (missing operator) in query expression
Date Posted: 07 March 2003 at 9:56am
|
AARRGG!!! Help! Please! I'm at my wit's end...
strRaceID = Request.QueryString("RaceID") 'Plus a check that sets strRaceID = 0 if it's blank / null / non-numeric Set rsResultHeaders = Server.CreateObject("ADODB.Recordset") strSQL = "SELECT tblResultHeaders.* FROM tblResultHeaders WHERE tblResultHeaders.Date_ID = " & strYearID & " ORDER BY tblResultHeaders.Race_ID ASC;" rsResultHeaders.Open strSQL, strCon Do While NOT rsResultHeaders.EOF If rsResultHeaders("Race_ID") = strRaceID Then '(amongst other variables set) lngHeaderID = rsResultHeaders("HeaderID") rsResultHeaders.MoveNext Loop 'Then later on... Set rsResults = Server.CreateObject("ADODB.Recordset") strSQL = "SELECT tblResults.* FROM tblResults WHERE tblResults.HeaderID = " & lngHeaderID & " ORDER BY tblResults.Position ASC;" rsResults.Open strSQL, strCon Result? I have set up response.write statements that indicate that, at least once in the run through the recordset there's a match, for example: TEST rsResultHeaders("Race_ID") = 0 TEST strRaceID = 0 However, lngHeaderID is never set (indicating the IF statement ignores the match), so I get: Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'tblResults.HeaderID ='. Doesn't seem to matter whether xxxRaceID is str, lng or int, likewise with xxxHeaderID. Please, someone point out my stupid mistake before the monitor matches the Wright brothers for air time... ------------- http://www.pedalcars.info/ - www.pedalcars.info The most fun on four wheels 
|
Replies:
Posted By: pedalcars
Date Posted: 07 March 2003 at 10:39am
|
Oh, plus, just changed the If statement (as a test) to:
If rsResultHeaders("Race_ID") = "0" Then as I know that zero is one valid value. Then it works as it should. So this indicates incorrect syntax in the IF statement - what should it be? Ta ------------- http://www.pedalcars.info/ - www.pedalcars.info The most fun on four wheels
|
Posted By: MorningZ
Date Posted: 07 March 2003 at 10:40am
|
have you simply tried
strSQL = "SELECT tblResults.* FROM tblResults WHERE tblResults.HeaderID = " & lngHeaderID & " ORDER BY tblResults.Position ASC;" Response.Write strDQL Response.End rsResults.Open strSQL, strCon too make sure your logic works? becuase the error doesn't lie, it IS missing an operator (read: problem with SQL statement) ------------- Contribute to the working anarchy we fondly call the Internet |
Posted By: MorningZ
Date Posted: 07 March 2003 at 10:51am
|
Also to make
'Plus a check that sets strRaceID = 0 if it's blank / null / non-numeric a little easier, i'll share a function i use a whole lot Function CLng0( expr ) CLng0 = 0 On Error Resume Next CLng0 = CLng( expr) On Error Goto 0 End Function This will always guarentee a number coming out of it, stops SQL Injectiom, stops nulls, stops empty string... and if you run it against a DB, it'll come back with no records (assuming you are looking for a primary key) ------------- Contribute to the working anarchy we fondly call the Internet |
Posted By: pedalcars
Date Posted: 07 March 2003 at 11:21am
|
Thanks for this - I hadn't tried the R.W strSQL, but adding it gives:
SELECT tblResults.* FROM tblResults WHERE tblResults.HeaderID = ORDER BY tblResults.Position ASC; i.e., the part where the lngHeaderID number should be is blank (which corresponds to the variable never being set). This ties in with the fact that R.W'ing the lngHeaderID gives a big fat blank, because it's never assigned a value; again, I'm guessing because the IF statement never picks up the match - within that IF statement I've set a R.W statement to write "I've found a match" when it does. It never does, unless I put in the "0" as per above. Thanks for the function above though, I'll try that as well. ------------- http://www.pedalcars.info/ - www.pedalcars.info The most fun on four wheels
|
Posted By: pedalcars
Date Posted: 07 March 2003 at 12:25pm
|
WOW!
Thanks MorningZ - I stuck in your function (no other changes to the code) and, hey presto, it works! (not just with setting a null value to zero either - it works starting with a valid number, which it didn't use to) Brilliant. I think I owe you a beer. ------------- http://www.pedalcars.info/ - www.pedalcars.info The most fun on four wheels
|