What am I doing wrong?? Code inside!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

What am I doing wrong?? Code inside!

Author

Topic

goingcrazy
Starting Member

6 Posts

Posted - 2003-06-18 : 01:25:01

I want to define a variable called "var" I want this brand name. Basically I have a hardware device sending a variable to my asp code. This value will be defined by my device but...

I am using this nice ASP code tutorial that I downloaded (works great and simple)

How do I define another variable to be used in my SQL select statement..( variable inside a variable possible?)

I am a newbie, everything in this code works great with the exception of what I added up top (trying to define the "var" variable to test) It does not help my second SQL string, it just comes back as "0"

Here is my code:

<%
dim var
var ="Rolex"
%>

<%
'This is the companion ASP page for the DevGuru
'Beginners guide to data access using ADO and ASP
'tutorial which, when combined with the
'example database, gives you everything you
'need to see ADO in action.

'Using Option Explicit demands that all
'variables are explicitly defined. Although
'not necessary, this is good programming
'practice and makes fault finding easier.
'Option Explicit

'Define our variables.
Dim cnnDB, strQuery, strQuery2, rsInfo, rsInfo2

'Create an instance of the Connection object.
Set cnnDB = Server.CreateObject("ADODB.Connection")

'And open it.
cnnDB.Open "DSN=slx"

'Build our SQL query string.
strQuery = "SELECT * FROM watches"
strQuery2 = "SELECT count(ProductBrand) FROM watches WHERE ProductBrand = '& var &'"

'Execute the query and return a recordset.
Set rsInfo = cnnDB.Execute(strQuery)
Set rsInfo2 = cnnDB.Execute(strQuery2)

%>

<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>
<CENTER><H2>
DevGuru ADO Tutorial</H2>
<BR>
<TABLE>
<TR>
<TH align="left">
ProductID
</TH>
<td width="15"><br></td>
<TH align="left">
Brand
</TH>
<td width="15"><br></td>
<TH align="left">
Name
</TH>
<td width="15"><br></td>
<TH align="left">
Rolex Count
</TH>
</TR>

<%
'Iterate through the recordset, pull
'out the required data, and insert it
'into an HTML table.
Do While Not rsInfo.EOF
%>

<TR>
<TD><% =rsInfo("ProductId") %></TD>
<td width="15"><br></td>
<TD><% =rsInfo("ProductBrand") %></TD>
<td width="15"><br></td>
<TD><% =rsInfo("ProductName") %></TD>
<td width="15"><br></td>
<TD><% =rsInfo2("") %></TD>
<td width="15"><br></td>
</TR>

<%
'Move to the next record in the
'recordset
rsInfo.MoveNext

Loop

'Close the recordset.
rsInfo.Close
rsInfo2.Close

'And close the database connection.
cnnDB.Close
%>

</TABLE>
</CENTER>

</BODY>
</HTML>

Edited by - goingcrazy on 06/18/2003 01:25:44

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-06-18 : 02:50:29

The problem might be as small as this:

'Build our SQL query string.
strQuery = "SELECT * FROM watches"
strQuery2 = "SELECT count(ProductBrand) FROM watches WHERE ProductBrand = '" & var & "'"

Do you understand why this is a problem? Because the query that was going out to SQL server ealier was

SELECT COUNT(ProductBrand) FROM Watches WHERE ProductBrand = '&var&'
while you need
SELECT COUNT(ProductBrand) FROM Watches WHERE ProductBrand = 'Rolex'

Which means SQL server should look for the ProductBrand named '&var&', but what you want it to look for is 'Rolex'. Hence you need to concatenate the variable with the rest of the statement from ASP. Try running these statements from Query Analyzer to see the difference.

And if you are serious about continuing with ASP and SQL Server I suggest get a good intoductory book or two. Tutorials wont take you too far.

Owais

goingcrazy
Starting Member

6 Posts

Posted - 2003-06-18 : 08:50:55

hm...

You mention I need to set my query like this:

strQuery2 = "SELECT count(ProductBrand) FROM watches WHERE ProductBrand = 'Rolex'"

That works like a champ but...

I am passing a variable from another outside source to my ASP. So, I don't want this to be a static statement. I would like the WHERE ProductBrand ='Rolex'" of my statement to change based on what variable this outside source sends. So it could be WHERE ProductBrand ='Timex'" or WHERE ProductBrand ='Omega'"

I have about 10 books so far :) I am trying to get hands on, I just can't seem to read most of these books and understand it without getting my hands dirty. This has helped me understand more than anything so far.

Thanks for the reply...

Edited by - goingcrazy on 06/18/2003 08:52:09

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-06-18 : 09:22:48

You might missed this from my earlier post:

quote:
The problem might be as small as this:

'Build our SQL query string.
strQuery = "SELECT * FROM watches"
strQuery2 = "SELECT count(ProductBrand) FROM watches WHERE ProductBrand = '" & var & "'"

The second query is doing exactly what you want it to do...all you had to do was change your original statement to

ProductID = (single quote)(double quote) & var & (double quote)(single quote)(double quote)

Owais

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources