Select Stmt Request.Form "Syntax Error" 1

[endoflux]

Having troubles today...

For the code below, I'm getting the following error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Packaging_Lot_Number = 05AD17'.
/sterilization/update_pick_sln.asp, line 11

An item of note is that the error shows that my value is coming through fine, and as a redundant check I have a Response.Write statement on the page displaying the value in "packnumber". Problem seems to be with placement of quotes or something...

Full Code:

<%
Dim adoCon
Dim rsSterile
Dim strSQL
Dim PNum
Pnum = Request("packnumber")
Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("sterilization.mdb")
Set rsSterile = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM SterilizationLog WHERE Packaging_Lot_Number = " & Request.Form("packnumber")
rsSterile.Open strSQL, adoCon
%>

<% Response.Write Request.Form("packnumber") %><br><br>
<%
Do While not rsSterile.EOF
Response.Write ("<br>")
Response.Write ("<a href=""update_view_sln.asp?ID=" & rsSterile("Sterile_Lot_Number") & "&PN=" & rsSterile("Packaging_Lot_Number") & """>")
Response.Write rsSterile("Sterile_Lot_Number")
Response.Write ("</a>")
Response.Write ("<br>")
rsSterile.MoveNext
Loop

rsSterile.Close
Set rsSterile = Nothing
Set adoCon = Nothing
%>
</td></tr></table></body></html>

 

[onpnt]

packing number sure looks like it is alphanumeric which means it isn't a number as you are representing it

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[endoflux]

It is an alphanumeric...the field type is 'text' in the database. I don't see where I specified that in the code though...have I missed something?

 

[DotNetGnat]

then you need single quotes around it...

-DNG

 

[onpnt]

There is a few FAQ's on using SQL tools to help you with your SQL statements along with a long winded ASP for beginners that covers data types in SQL string builds.

The other long winded version of your problem is your database no matter what it is needs to know what the data type is you are sending to it in your values. By doing that ' ' means string and not having quotes means numeric to name the two most used. So if you say

SELECT value FROM table WHERE thiscolumn = 'test'

you want a value of a data type string (varchar, nvarcahr, texxt, memo etc...)

is you wrote your SQL statement in Access before getting it into your ASP script you would probably write

SELECT * FROM SterilizationLog WHERE Packaging_Lot_Number = '05AD17'

That would return what you need (hopefully). You ASP script is giving it this

SELECT * FROM SterilizationLog WHERE Packaging_Lot_Number = 05AD17

which is in all a error.

Short answer:
You'll need to enclose the form value in quotes in order to send it across as it should be.

example
'" & Request.Form("value") & "'

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]