Using a variable in a SQL statement

This should be quite easy.
I have a recordset with a SQL statement defining the data that I get.  I am passing a variable from another web page and need to use it in the SQL statement.
The book I am working from has errors and the link to the fix is dead (and, of course, I can't talk to someone who has half an idea of what to do).  How can I use a variable in this SQL statement?

select Orders.OrderID, Customers.CompanyName, Orders.ShippedDate from Orders, Customers where Orders.CustomerID = Customers.CustomerID and Orders.OrderID = OID

OID is the variable name!!
LVL 23
slink9Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Lab1Connect With a Mentor Commented:
Points reduced and moved to PAQ
Lab1
CS Moderator
0
 
JonFish85Commented:
I am assuming you are using some code like this:

strSQL = "select Orders.OrderID, Customers.CompanyName, Orders.ShippedDate from Orders, Customers where Orders.CustomerID = Customers.CustomerID and Orders.OrderID=OID"

try changing that to this:

strSQL = "select Orders.OrderID, Customers.CompanyName, Orders.ShippedDate from Orders, Customers where Orders.CustomerID
= Customers.CustomerID and Orders.OrderID=" & OID

hope this helps!
0
 
slink9Author Commented:
The sql string was copied from the SQL builder in the recordset DTC in VI.  The book was (apparently incorrectly) using [] around the variable.  I have tried all of the keys in the shifted number row in front of the variable name with no success.  I will try quotes in the fashion you have suggested.  Thanks.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
slink9Author Commented:
I put single quotes around everything except for the variable name.  It didn't help.  Any other suggestions?
0
 
slink9Author Commented:
If I put single quotes around the variable itself I get an error about it not being data type int.  How do I convert it in the SQL statement?  I guess it comes from the other page and is assigned the type varchar.
0
 
msdixonCommented:
try:
' first escape the single quotes in the variable
OID = replace(OID, "'", "''")
strSQL = "select Orders.OrderID, Customers.CompanyName, Orders.ShippedDate from Orders, Customers where
Orders.CustomerID
= Customers.CustomerID and Orders.OrderID='" & OID & "'"

if this doesn't work, write out the sql statement and take a look at it.

if you put single qoutes around it, you shouldn't get an error. ie: select * from abc where columnName = '1' should work even if the column 'columnName' is an int.
0
 
slink9Author Commented:
I still get

Syntax error converting the varchar value '&OID2&' to a column of data type int

I have the code in script to convert a new variable to int type

OID2 = cint(OID)

If I change the SQL statement (in Recordset Properties in the SQL Statement box)

select Orders.OrderID, Customers.CompanyName, Orders.ShippedDate from Orders, Customers where Orders.CustomerID = Customers.CustomerID and Orders.OrderID = '&OID2&'

to have 10280 in place of '&OID2&' it works without error.
0
 
msdixonCommented:
ok, here's your problem. in the sql statement the database thinks you are comparing orderid to the value '&OID2&'

change your sql statement to:
strSQL = "select Orders.OrderID, Customers.CompanyName, Orders.ShippedDate from Orders, Customers where Orders.CustomerID
= Customers.CustomerID and Orders.OrderID = '" & OID2 & "'"

you were missing the double quotes surrounding & OID2 & 
0
 
slink9Author Commented:
I changed the single quotes to doubles and got the same results.  I took the quotes out and got an error in the SQL statement.  Below is the whole page returned.

EVENT TRACE:  thisPage fired oninit event.
EVENT TRACE:  OrderListRecordset fired onbeforeopen event.
SCRIPTING OBJECT MODEL EXCEPTION:
FILE:   recordset.asp
FUNCTION:   OrderListRecordset.open()
DESCRIPTION:   Err 418: Failed to open the ADO recordset. Check for the following possible causes:

An invalid SQL statement.
Missing or invalid database object name (check recordset DTC properties).
Missing parameters or parameter type mismatch (parameters must be set before open).
CommandType = 1
CommandText = "select Orders.OrderID, Customers.CompanyName, Orders.ShippedDate from Orders, Customers where Orders.CustomerID = Customers.CustomerID and Orders.OrderID = '& OID2 &'"

------------------------------
Microsoft OLE DB Provider for SQL Server error '80040e07'

Syntax error converting the varchar value '& OID2 &' to a column of data type int.

/NorthWindFoods/_ScriptLibrary/Recordset.ASP, line 636
0
 
msdixonCommented:
CommandText = "select Orders.OrderID, Customers.CompanyName, Orders.ShippedDate from Orders, Customers
where Orders.CustomerID = Customers.CustomerID and Orders.OrderID = '" & OID2 & "'"
0
 
msdixonCommented:
look at it this way...
CommandText = "string stuf..... string, string, string, " & variable & " string "

you must end the string, then concatenate the string and the variable together with an ampersand.
0
 
slink9Author Commented:
The problem is that it appears to want either a literal or a column.  I can't make it realize that this is a variable.  The help on the book is apparently non-existent since I have sent emails and can't even access the link for the fixed code.
0
 
msdixonCommented:
take a look at this:
CommandText = "select Orders.OrderID, Customers.CompanyName, Orders.ShippedDate from Orders, Customers
where Orders.CustomerID = Customers.CustomerID and Orders.OrderID = '& OID2 &'"

this is your current sql statement. look near oid2, you don't end the string, so the database thinks it's all one string. just put double quotes around the variable and you'll be fine.

CommandText = "select Orders.OrderID, Customers.CompanyName, Orders.ShippedDate from Orders, Customers
where Orders.CustomerID = Customers.CustomerID and Orders.OrderID = '" & OID2 & "'"

look closely at it now...
"select .... order.orderid = '" & OID2 & "'"

you will notice that right after the first single quote is a double quote, this ends the string in vbscript, then the ampersand which means a varaiable is coming up. then it's your variable, then another ampersand telling vbscript that another string is next, followed by a double quote, single quote, then a double quote.

just put it in EXACTLY as i had it two comments ago and it'll work.

trust me on this one.
0
 
slink9Author Commented:
Either way I use it with single quotes, I get

Syntax error converting the varchar value '& OID2 &' to a column of data type int.

I have tried with and without the &s.  Any suggestions?
0
 
slink9Author Commented:
Either way I use it with single quotes, I get

Syntax error converting the varchar value '& OID2 &' to a column of data type int.

I have tried with and without the &s.  Any suggestions?
0
 
msdixonCommented:
BECAUSE YOU'RE NOT USING THE DOUBLE QUOTES TO END YOUR STRING BEFORE YOUR VARIABLE AND TO START YOUR STRING AFTER YOUR VARIABLE!!!!!!!
0
 
slink9Author Commented:
The double quotes are automatically being added.  I can't control that.  It is in the SQL statement builder of the Recordset DTC object.  I thought I may be able to trick it and put a double quote in before the variable but I got double quotes at the beginning and end along with a single instance of the double quote where I put it along with an error.  I found something that indicated I should use @ to indicate that it is a variable but when I do that I get an error that I must declare the variable.  I previously DIMmed the variable and retyped it with CINT.
0
 
msdixonCommented:
well, if you're using a dtc good luck. those things are nothing but problems.
0
 
slink9Author Commented:
Here was the final answer.

I don't use design time controls, but did you try to put a question mark in for the parameter ("...where order =?") and add the parameter to the parameters tab on the recordset DTC properties tab? There is a value field that you could enter request.form("orderID").

This thread has quite useful info, so I will ask that the points be refunded and the question PAQed.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.