Using a passed variable in a SELECT statement ??? 1

[SuperComputing]

I am writing a page that reads a barcode number, searches a pervasive table and returns color, size, etc. When setup to search a mdb, I was doing fine. Now I am having syntax trouble with the SELECT query. Any assistance would make my head hurt just a little less. Here is what works:

Please be easy on me, I am new at this and have compiled this from tutorials trying to get it to work.

The "upc" comes from a form on my index.htm page:
<form action="results.asp" method="post">
...
<input type="text" name="upc"><input type="submit" value="Enter">

results.asp:
Dim upc, con, rsBarcodeData, strQuery
upc = Request.Form("upc")
Set con = Server.CreateObject("ADODB.Connection")
con.open "DSN=FTInventory"
strQuery = "SELECT * FROM Barcode WHERE Barcode = " & upc
...
<td><%=rsBarcodeData("Barcode") %></td>

If I put in an actual barcode in the query:
strQuery = "SELECT * FROM Barcode WHERE Barcode = '647484502103' "
Everything works fine, I just don't know the syntax to make it use the upc variable passed from my form.

Thanks in advance!

 

[gmmastros]

Try this...

strQuery = "SELECT * FROM Barcode WHERE Barcode = [!]'" & Replace(upc, "'", "''") & "'"[/!]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SuperComputing]

I don't understand what is happening now, this is what I get:

Error Type:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
/inventory/results.asp, line 73

Line 73: rsBarcodeData.Close

It's like it blows through the code without displaying any data...

 

[gmmastros]

It could be that there are no records returned by your query.

Response.Write the query to the screen and run it directly on your database.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Chopstik]

Or, it may be a case where you've done everything but actually open/create the recrodset. Can you publish your code to create the recordset? My guess is that you set everything up but then didn't actually create the recordset object itself.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[SuperComputing]

George,

A little more messing with it and your suggestion worked perfectly!

I have to comment out the 2nd line below to make it work:

con.Close
' rsBarcodeData.Close
Set con = Nothing
Set rsBarcodeData = Nothing

I don't understand why it won't let me close that one variable, but hey as long as it works!

 

[gmmastros]

Try closing the recordset object before closing the connection object.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SuperComputing]

That was it! Makes sense when you do things in order and not just arrange them aesthetically by size... Thank you again!

 

[gmmastros]

>> aesthetically by size

Uh... What?

I usually 'clean up' in the same order I use them.

Ex:

[tt][blue]
Create Connection Object
Open Connection Object
Create Recordset Object
Open Recordset Object

[green]' Code here....[/green]

Close Recordset Object
Set RecordsetObject = Nothing
Close Connection Object
Set Connection Object = Nothing
[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom