[RESOLVED] use request.form input in strSQL WHERE clause

[lyriclover]

Hello, I'm new to ASP and VBscript. I've been adding some new pages to our website, although I seem to be having some difficulties.

On Page 1, I have dropdowns for the user to select criteria, and upon clicking the Accept button on the webpage, it sends the user to Page 2 (a report page which should display a table of information from an Access database based on the criteria select by the user). I have an strSQL statement as follows, which is currently pulling all the records from the FeedStocks_Query. I would like to add a WHERE clause to have the SQL display only the data that matches the criteria the user entered from Page 1.

WORKING: but displaying all query records available
strSQL = "SELECT Commodity, Location, Low, Avg, High, LowFut, HighFut, AvgFut FROM FeedStocks_Query;"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open xDb_Conn_Str
Set rsfeedtbl = conn.Execute(strSQL)

WHAT I'M AIMING FOR:
strSQL = "SELECT Commodity, Location, Low, Avg, High, LowFut, HighFut, AvgFut FROM FeedStocks_Query WHERE Commodity = " & Request.Form("feed_commodity") & ";"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open xDb_Conn_Str
Set rsfeedtbl = conn.Execute(strSQL)

Using the second SQL statement gives me the following error:
Microsoft JET Database Engine error '80040e07'
Data type mismatch in criteria expression.

Any suggestions would be greatly appreciated! Thanks!

[rory]

do this and see what it sais ..

strSQL = "SELECT Commodity, Location, Low, Avg, High, LowFut, HighFut, AvgFut FROM FeedStocks_Query WHERE Commodity = " & Request.Form("feed_commodity") & ";"

Response.Write Request.Form("feed_commodity") & "<br>"
Response.Write strSQL
Response.End

Also is that field a Text or Number .. or other ..?

[lyriclover]

Thanks Rory,

With a little tweaking it worked! FYI, the data is text. I'm still a little confused on the quotation marks (', ") and ampersands (&) and where they are supposed to go though. It seems to be the hardest part of and SQL statement.

Thanks again!

[rory]

no prob .. always open and close a string with "

the & is just placed inbetween the " and a Variable.

This is all VB related stuff . as for SQL, have to have the ' around text fields.