Solved

Using a variable in a SQL statement

Posted on 2001-09-06
19
278 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

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 …
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…
The purpose of this video is to demonstrate how to reset a WordPress password if you are locked out and cannot reset the password. A typical use would be if you cannot access the email to which WordPress would send the password recovery email to…
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…

697 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