Select statment Getting mismatch data error

turkle112 · #1 (**permalink**) 09-08-03, 10:53

Good morning everyone. I am working on selecting from a database and displaying the results, what should be an easy code to code is turned into a nightmare. My select statement is as follows

Code:

sql2="SELECT * FROM line_data WHERE customerNumber = " & customerNumber

Where CustomerNumber is passed in through a querystring like www.domain.com/page.asp?custNum=1234567

The error I am getting is

Code:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07' 

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

The field customerNumber in the database is text, does that make a difference? Is there a function to change this from a number to text, if the above is the case? Any help would be appreciated

Turk

fused · #2 (**permalink**) 09-08-03, 11:58

Code:

"SELECT * FROM line_data WHERE customerNumber = '" & Request.QueryString("custnum") & "'"

turkle112 · #3 (**permalink**) 09-08-03, 12:09

Sorry about that I request the string in an if statement above.

Code:

dim customerNumber
'if the customer number is empty assign message if not assign number to variable
if request.QueryString("custNum") <> "" then
	customerNumber = request.QueryString("custNum")
	set conn=Server.CreateObject("ADODB.Connection") 
	conn.Open "h_data"
	'setting up recordset and opening data table
	set rs1=Server.CreateObject("ADODB.recordset")
	'select all rows from table where customer number matches number passed in
	sql2="SELECT * FROM l_data WHERE customerNumber = " & customerNumber
	rs1.Open sql2, conn
	rs1 = conn.execute (sql2)
else 
	customerNumber = "Customer number not found"
end if

Theres the code for everything

bill_dev · #4 (**permalink**) 09-12-03, 21:16

...in Access what datatype is the "customerNumber" field? If it is Text then that is why fused suggested you add quotes which you may have overlooked.

Internet Services

Web Development

Digital Marketing

Consumer Tech