Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Using a variable in a SQL statement

Posted on 2001-09-06
19
Medium Priority
?
284 Views
Last Modified: 2013-12-24
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!!
0
Comment
Question by:slink9
19 Comments
 
LVL 6

Expert Comment

by:JonFish85
ID: 6462917
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
 
LVL 23

Author Comment

by:slink9
ID: 6463527
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
 
LVL 23

Author Comment

by:slink9
ID: 6463666
I put single quotes around everything except for the variable name.  It didn't help.  Any other suggestions?
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 23

Author Comment

by:slink9
ID: 6463887
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
 
LVL 9

Expert Comment

by:msdixon
ID: 6464314
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
 
LVL 23

Author Comment

by:slink9
ID: 6464379
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
 
LVL 9

Expert Comment

by:msdixon
ID: 6464403
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
 
LVL 23

Author Comment

by:slink9
ID: 6464443
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
 
LVL 9

Expert Comment

by:msdixon
ID: 6464655
CommandText = "select Orders.OrderID, Customers.CompanyName, Orders.ShippedDate from Orders, Customers
where Orders.CustomerID = Customers.CustomerID and Orders.OrderID = '" & OID2 & "'"
0
 
LVL 9

Expert Comment

by:msdixon
ID: 6464661
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
 
LVL 23

Author Comment

by:slink9
ID: 6464682
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
 
LVL 9

Expert Comment

by:msdixon
ID: 6464737
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
 
LVL 23

Author Comment

by:slink9
ID: 6464797
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
 
LVL 23

Author Comment

by:slink9
ID: 6464804
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
 
LVL 9

Expert Comment

by:msdixon
ID: 6464840
BECAUSE YOU'RE NOT USING THE DOUBLE QUOTES TO END YOUR STRING BEFORE YOUR VARIABLE AND TO START YOUR STRING AFTER YOUR VARIABLE!!!!!!!
0
 
LVL 23

Author Comment

by:slink9
ID: 6464872
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
 
LVL 9

Expert Comment

by:msdixon
ID: 6464909
well, if you're using a dtc good luck. those things are nothing but problems.
0
 
LVL 23

Author Comment

by:slink9
ID: 6465054
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
 

Accepted Solution

by:
Lab1 earned 0 total points
ID: 6465191
Points reduced and moved to PAQ
Lab1
CS Moderator
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When deciding to adopt any help desk solutions many factors should be explored before taking decisions. This will change from business to another but in general there are some kind of rule of thumb. Here are some quick tips: Do we need only ticket…
In this short web based tutorial, I wanted to show users how they can still use the powers of FrontPage in conjunction with Expression Web 3.  Even though Microsoft eliminated the use of Web components, we can still use them with FrontPage and edit …
The purpose of this video is to demonstrate how to exclude a particular blog category from the main blog page. This is can be used when a category already has its own tab, or you simply want certain types of posts not to show up on the main blog. …
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Go t…

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question