Solved

Using a variable in a SQL statement

Posted on 2001-09-06
19
273 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
New Website 15 27
create a website 10 72
Netbeans Open project initial dir 5 32
Problem to echo 6 55
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 …
Turn A Profile Picture Into A Cartoon Using Photoshop And Illustrator This tutorial will teach you how to make a cartoon style image out of a regular picture. I have tried to keep the tutorial as simple as possible. I used Adobe CS4 for this tuto…
The purpose of this video is to demonstrate how to automatically show related posts at the bottom of a blog post in WordPress. This will be demonstrated using a Windows 8 PC. Plugin “Yet Another Related Posts Plugin” will be used. Go to your…
The purpose of this video is to demonstrate how to make a WordPress Site faster and smaller in size by cleaning up the database. This will be demonstrated using a Windows 8 PC. Plugin WP Optimize will be used. Go to your WordPress login page. T…

759 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now