Confusing error - data type mismatch

[ai3rules]

Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression
.........line 214

Dim Releases: Releases = request.form("Releases")

Set compareProductRecordset = Server.CreateObject("ADODB.Recordset")


qryProducts = "SELECT * FROM ProductInfo WHERE ProductsID=" &productsID& " AND VendorID=" &vendorID& " AND Release="&Releases& " AND OSID=" &OSID& " AND StatusID=" &statusID& " AND DateOrdered=" &dateordered



line 214 is: compareProductRecordset.open qryProducts, oConn

The weird thing is I get that error only when I add the " AND Release="&Releases& " portion of the query.

The data type for the Release field in the database is "text".

I am fairly new to asp, sql, etc.. is that query wrong? Why would only that Release portion of it cause this error?

I appreciate any help.

[schizo]

Try adding single quotes around all your varaibles, SQL usually expects to read a string as "myVariable = 'value'" so for example, ProductsID should read...
"...ProductsID='" & productsID & "'..."

[ai3rules]

Hi, thanks for your reply.

I am still getting the error although I might be getting confused with the quotes. Is this right?

qryProducts = "SELECT * FROM ProductInfo WHERE ProductsID='" &productsID& "' AND VendorID='" &vendorID& "' AND Release='" &Releases& "' AND OSID='" &OSID& "' AND StatusID='" &statusID& "' AND DateOrdered='" &dateordered&"'"

[schizo]

The quotes look correct. As a debugging method, Response.Write out the sql string and post what it says... this is an easy way to tell if the quotes are correct and the variable values are there. Another thing you might want to try is adding CStr() around your Releases variable.

[silverbullet24]

try

qryProducts = "SELECT * FROM ProductInfo WHERE ProductsID=" &productsID& " AND VendorID=" &vendorID& " AND Release='" &Releases& "' AND OSID=" &OSID& " AND StatusID=" &statusID& " AND DateOrdered='" &dateordered&"'"

this is assuming all your ID fields are integer/number type fields. general rule of thumb, if they are numbers, no quotes. strings need quotes

[ai3rules]

Hi silverbullet24,

Thanks for your added input - that was actually the solution. Thanks again schizo for your help as well.