Solved

Using a variable in a SQL statement

Posted on 2001-09-06
19
277 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Now that Expression Web 4.0 (http://www.microsoft.com/expression/products/Upgrade.aspx) is free if you buy or have the full version of Expression Web 3.0, now is the best time to  migrate from FrontPage to Expression Web (http://www.frontpage-to-exp…
Objective of This Article In 1990’s, when I was a budding software professional, I had a lot of confusion about which stream or technology, I had to choose to build my career. In those days, I had lot of confusion like whether to choose System so…
The purpose of this video is to demonstrate how to add AdSense Ads to a WordPress Website, and how to set up WordPress to automatically place Ads in Sidebars. This will be demonstrated using a Windows 8 PC. Log into your AdSense account. : Cli…
The purpose of this video is to demonstrate how to integrate Mailchimp with WordPress, by placing a Mailchimp signup form on a WordPress Page or Post. This will be demonstrated using a Windows 8 PC. Mailchimp will be used. Log into your Mailchi…

792 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