[thelist] SQL

Billy Hutton bhutton at effectnet.com
Fri Aug 11 17:42:44 CDT 2000


One thing that I like to do when I can't get a sql query to work from within
an asp page or whatever;

1. Write the query first within the rmdbs (mssql7 query analyzer) with no
variables, and some simple output that you can validate.

2. Copy the query into your asp code.  Instead of trying to get it to
execute, just do a Response.Write(yourQueryHere).  Make sure the output
matches your original query.

3. Begin replacing variables one at a time, and continue to test the query
in "output mode" only via Response.Write.

4. Once you have the query exactly like you want it, ie getting data from
form elements, other recordsets, etc. copy the Response.Write output off the
page and paste it back into the rmdbs.  Execute the query again.

5.  If everything goes as planned, just change the Response.Write to an
someConnObject.Execute() statement.  :)

- B

Umm, where exactly should I have the single quotes around the string value
request.form("minsize")? I'm not actually using that in the SQL statement, I
used that as an example of what did work. What I'm doing is following the
same pattern as I'm using to get a list of ticked check boxes i.e. passing a
comma seperated string to an SQL statement

("SELECT * FROM Deliveries where MinimumVehicleSize = ("& pcnum &") and
Available = 1")

so in the case I am testing it would literally read

("SELECT * FROM Deliveries where MinimumVehicleSize = ("Motorbike, Transit,
7 Ton Truck ") and Available = 1")

The string is being passed because it is showing up in the error statement

Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in
query expression 'MinimumVehicleSize IN (Motorbike, Transit, 7 Ton Truck)
and Available = 1'.

So what is the missing operator? And thanks Rudy for your input but I'm
afraid that didn't work either

SELECT * FROM Deliveries where MinimumVehicleSize IN ("&pcnum&")

Any other ideas peeps?





More information about the thelist mailing list