Problem with SQL command or Database field?

[Mania]

Hello all

this one has me stumped, the strid var is the value of the field and strsearch is the field name. The funny thing is if strsearch is the autonumber (named ID) then it works but if I try a different column name (named tooling) I get "No value given for one or more required parameters"
or "datatype mismatch" depending on the value of strid

am I missing some typo? all fields besides autonumber are text fields and database is access 2000

btw have done a response.write and the command looks good
it comes out " SELECT from tblremake where tooling = aaa1234 "

any help would be greatly appreciated!

Charles

<%
dim con, rs
dim strid, strsearch

strid= request.form(&quot;txtnum&quot
strsearch= request.form(&quot;searchtype&quot

set con = Server.CreateObject(&quot;ADODB.Connection&quot
set rs = Server.CreateObject(&quot;ADODB.Recordset&quot
con.open &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Jobs\ocar.mdb&quot;


Sql=&quot;SELECT * from tblremake WHERE &quot;
Sql=Sql & strsearch
Sql=Sql & &quot; = &quot;
Sql=Sql & strid

rs.open (Sql), con
%>

 

[SonOfEmidec1100]

When equating to strings you need to enclose these in quotes

Sql=&quot;SELECT * from tblremake WHERE &quot;
Sql=Sql & strsearch
Sql=Sql & &quot; = '&quot;
Sql=Sql & strid & &quot;'&quot;

ie &quot; SELECT from tblremake where tooling = 'aaa1234'&quot;

 

[Mania]

Thanks! it worked great for the &quot;tooling&quot; variable but when I go back to the original autonumber search that worked for my above command I get a &quot;data mismatch&quot; error.. I put in an if else to get it to work but I don't get the concept of what makes the data in &quot;tooling&quot; work with the quotes but not the &quot;ID&quot;... they are both fields with data in them after all, the only differance is &quot;ID&quot; is the autonumber and &quot;Tooling&quot; is a text field...

Thanks again!

Charles

 

[lespaul]

That's right, one is a number and when used in criteria is:

SELECT * FROM TABLE WHERE NUMBERFIELD = 1

When the criteria is text/string, it has to be surrounded by quotes:

SELECT * FROM TABLE WHERE STRINGFIELD = 'TEXT'

You will need two queries, or you will need to build the query dynamically depending on whether you are going to be searching for a NUMBER or for TEXT

HTH

Leslie

 

[Mania]

Thanks Leslie! Makes perfect sense, an if else will work for this one (only 2 variables) but next time I'll build dynamically as you suggest..

It's alway's the little things

Charles