How do I select and dislay a row from a recordset

[jjjjjj]

Hi

I am trying to display a row of data from a recordset using the input from a html form as the way of searching the recordset.

eg. My table has 3 columns ( LIQUID, PROPERTIES, FORMULA) and about 60 rows. I need to pass an inputted argument (from the client side html form) eg. WATER into the recordset so that it looks down the column LIQUID and finds WATER, then diplays whole row cotaining WATER, it's PROPERTIES and FORMULA in a table.

If WATER is not found in the table I would like to output "Sorry your input did not match any records" (and the input does not have to be case sensitive!).

If anyone could help me it would be much appreciated.

Cheers

John

 

[RushiShroff]

Dim SQLGet, objConn, objRs
SQLGet="select * from tblname where LIQUID='"& WATER &"'"
set objConn=server.createObject("ADODB.Connection")
set objRs=objConn.execute(SQLGet)
if objRs.EOF OR objRs.BOF then
      response.write "Sorry your input did not match any records"
else
      response.write objRs(&quot;PROPERTIES&quot;)&&quot;<be>&quot;
      response.write objRs(&quot;FORMULA&quot;)
End if
objConn.Close
set objConn=nothing
objRs.Close
set objRs=Nothing

in case of one row found if multiple found you have to loop through..
Rushi Shroff Rushi@emqube.com
"Life is beautiful."

 

[jjjjjj]

Thanks Rushi

I'm nearly there now, but the user could input anything into the input box (not just WATER), so the select statement needs to be the Request.Form("input&quot argument, whateverm the user types. I just can't get it to work????

 

[bperry]

SQLGet="select * from tblname where LIQUID='"& Request.Form("input&quot &"'"

 

[jjjjjj]

Thanks V.much

Just one last one.....if I wanted to select more than one input to match to.....e.g Request.Form("input&quot and Request.Form("input2&quot how would I change my SQL statement?

 

[bperry]

Just continue on the same in the same way.

SQLGet="select * from tblname where LIQUID='"
& Request.Form("input1&quot & "' AND SOLID='" & Request.Form("input2&quot & "'"


You are building a string that should end up looking like this:

Select * from tblName
where LIQUID='value1' and SOLID='value2'

rgrds, etc
bp

 

[bperry]

P.S.
The above examples all assume that the columns in the database (LIQUID and SOLID) are strings.

If they are numeric, just leave out the single quotes.
Here, LIQUID is string and SOLID is numeric:

SQLGet="select * from tblname where LIQUID='"
& Request.Form("input1&quot & "' AND SOLID=" & Request.Form("input2&quot

 

[jjjjjj]

Thanks v.much bp

It works ok now but the second column is
CHEMICAL PROPERTIES , the sql statement doesn't seem to like it when I have a space in between CHEMICAL and PROPERTIES. Do I have to put anything in between it in the Select statement?
I would also like to feed in things into the form like Water or water or water , so that it is not case sensitive & also if there is a space in between the input like "Hydrogen Peroxide". At the moment it does not recognise the chemical e.g hydrogen peroxide if it is not put in exactly as it is in the database (actually an Excel spreadsheet!).

Cheers

John