IIS ASP - Simple SQL statement and request.querystring

Author

Simple SQL statement and request.querystring

gjoneshtfc@volcanomail.com

2006-06-20, 1:23 pm

Hello, I have a simple problem that I just cannot get my head around!

I currently have the following line in my ASP recordset:

Recordset1.Source = "SELECT * FROM MainTable ORDER BY Price ASC"

I have the following code at the start of the recordset:

dim chosencar
chosencar=Request.QueryString("make")

What i want to have is a WHERE command in the SQL statement which will
filter the passed value from the previous page. For example:

Recordset1.Source = "SELECT * FROM MainTable WHERE
Make='<--chosencar-->' ORDER BY Price ASC"

Is there any way of doing what i want it to do please? I am a newbie at
SQL so all help is greatly appreciated. Keeping it as simple as
possible will also help me!

Thanks for your time and help!
Gareth

Aaron Bertrand [SQL Server MVP]

2006-06-20, 1:23 pm

Well, aside from various bad things I might point out (like NEVER USE SELECT
* IN PRODUCTION CODE), have you tried:

chosencar = Replace(Request.QueryString("make"), "'", "''")
Recordset1.Source = "SELECT * FROM MainTable WHERE " & _
" [make] = '" & chosencar & "' ORDER BY Price ASC"

Also, consider parameterized queries, stored procedures, etc. Constructing
ad hoc sql in this way is very dangerous and inefficient. I wish I wasn't
too lazy to look up the links usually provided by Bob Barrows, but he's not.
:-)

<gjoneshtfc@volcanomail.com> wrote in message
news:1150825931.393290.315630@u72g2000cwu.googlegroups.com...
> Hello, I have a simple problem that I just cannot get my head around!
>
> I currently have the following line in my ASP recordset:
>
> Recordset1.Source = "SELECT * FROM MainTable ORDER BY Price ASC"
>
> I have the following code at the start of the recordset:
>
> dim chosencar
> chosencar=Request.QueryString("make")
>
> What i want to have is a WHERE command in the SQL statement which will
> filter the passed value from the previous page. For example:
>
> Recordset1.Source = "SELECT * FROM MainTable WHERE
> Make='<--chosencar-->' ORDER BY Price ASC"
>
> Is there any way of doing what i want it to do please? I am a newbie at
> SQL so all help is greatly appreciated. Keeping it as simple as
> possible will also help me!
>
> Thanks for your time and help!
> Gareth
>

Bob Barrows [MVP]

2006-06-20, 1:23 pm

gjoneshtfc@volcanomail.com wrote:
> Hello, I have a simple problem that I just cannot get my head around!
>
> I currently have the following line in my ASP recordset:
>
> Recordset1.Source = "SELECT * FROM MainTable ORDER BY Price ASC"

Do you really need ALL the fields and ALL the rows?

>
> I have the following code at the start of the recordset:
>
> dim chosencar
> chosencar=Request.QueryString("make")
>
> What i want to have is a WHERE command in the SQL statement which will
> filter the passed value from the previous page. For example:
>
> Recordset1.Source = "SELECT * FROM MainTable WHERE
> Make='<--chosencar-->' ORDER BY Price ASC"
>
> Is there any way of doing what i want it to do please? I am a newbie
> at SQL so all help is greatly appreciated. Keeping it as simple as
> possible will also help me!
>
I would start by getting rid of the * and explicitly naming the fields
you wish the query to return. Then:

dim sql, arParms, make, cmd
make=Request.QueryString("make")
'validate make - make sure it contains what it's supposed to contain
'if it's valid, then:

sql="SELECT <list of fields> FROM MainTable " & _
"WHERE Make=? ORDER BY Price ASC"

'see the "?" That's called a parameter marker. You can
'have as many as you need. Now let's use a command object
'to pass a value to that parameter:

arParms=array(make) 'an array is required
set cmd=createobject("adodb.commmand")
with cmd
.commandtype=1 'adCmdText
.commandtext=sql
set .activeconnection=objconn
set Recordset1 = .Execute(,arParms)
End With
if not Recordset1.eof then ...

You can find the ADO documentation here:
http://msdn.microsoft.com/library/e...pireference.asp

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

gjoneshtfc@volcanomail.com

2006-06-21, 1:21 pm

Thanks Aaron,

I tried what you suggested but it says the syntax is incorrect. Any
other suggestions on how to do it? I a complete novice to SQL so
parameterized queries and stored procedures are things i have not heard
of!

Thanks again for your reply,
Regards, Gareth

Aaron Bertrand [SQL Server MVP] wrote:[vbcol=seagreen]
> Well, aside from various bad things I might point out (like NEVER USE SELECT
> * IN PRODUCTION CODE), have you tried:
>
> chosencar = Replace(Request.QueryString("make"), "'", "''")
> Recordset1.Source = "SELECT * FROM MainTable WHERE " & _
> " [make] = '" & chosencar & "' ORDER BY Price ASC"
>
> Also, consider parameterized queries, stored procedures, etc. Constructing
> ad hoc sql in this way is very dangerous and inefficient. I wish I wasn't
> too lazy to look up the links usually provided by Bob Barrows, but he's not.
> :-)
>
>
>
>
>
> <gjoneshtfc@volcanomail.com> wrote in message
> news:1150825931.393290.315630@u72g2000cwu.googlegroups.com...

Aaron Bertrand [SQL Server MVP]

2006-06-21, 1:21 pm

Debugging 101:

Change

Recordset1.Source = "SELECT * FROM MainTable WHERE " & _
" [make] = '" & chosencar & "' ORDER BY Price ASC"

to

sql = "SELECT * FROM MainTable WHERE " & _
" [make] = '" & chosencar & "' ORDER BY Price"
response.write sql
response.end

Show us the result! And if you still get an error message, please copy and
paste explicitly. I know of about 30 different messages that include the
words "syntax" and "incorrect"... the exact error message (and maybe even
the line it occurs on) would be much more helpful.

A

<gjoneshtfc@volcanomail.com> wrote in message
news:1150900526.748891.190220@y41g2000cwy.googlegroups.com...
> Thanks Aaron,
>
> I tried what you suggested but it says the syntax is incorrect. Any
> other suggestions on how to do it? I a complete novice to SQL so
> parameterized queries and stored procedures are things i have not heard
> of!
>
> Thanks again for your reply,
> Regards, Gareth
>
>
> Aaron Bertrand [SQL Server MVP] wrote:
>

gjoneshtfc@volcanomail.com

2006-06-21, 1:21 pm

Xref: TK2MSFTNGP01.phx.gbl microsoft.public.inetserver.asp.general:306239

Thanks for your help Bob,

Just one query with what you have written - how do i link that in with
the VBScript Recordset1.Source? Or is this a completely different
approach to my problem?

Thanks again, Gareth

Bob Barrows [MVP] wrote:
> gjoneshtfc@volcanomail.com wrote:
>
> Do you really need ALL the fields and ALL the rows?
>
> I would start by getting rid of the * and explicitly naming the fields
> you wish the query to return. Then:
>
> dim sql, arParms, make, cmd
> make=Request.QueryString("make")
> 'validate make - make sure it contains what it's supposed to contain
> 'if it's valid, then:
>
> sql="SELECT <list of fields> FROM MainTable " & _
> "WHERE Make=? ORDER BY Price ASC"
>
> 'see the "?" That's called a parameter marker. You can
> 'have as many as you need. Now let's use a command object
> 'to pass a value to that parameter:
>
> arParms=array(make) 'an array is required
> set cmd=createobject("adodb.commmand")
> with cmd
> .commandtype=1 'adCmdText
> .commandtext=sql
> set .activeconnection=objconn
> set Recordset1 = .Execute(,arParms)
> End With
> if not Recordset1.eof then ...
>
> You can find the ADO documentation here:
> http://msdn.microsoft.com/library/e...pireference.asp
>
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.

gjoneshtfc@volcanomail.com

2006-06-21, 1:21 pm

Aaron,

If i change that i get an unspecified error. So you know exactly what i
am using it is Dreamweaver MX2004 with ASP VBscript pages. From within
dreamweaver i create a recordset to connect to the database. Part of
this connection is the SQL which when i change it your suggestion i get
the following error when i test it:

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
operator) in query expression ' [ & _ ] [make] = ' " & chosencar & " '
'

Hope this gives you some clue! Thanks again for your help

Gareth

Aaron Bertrand [SQL Server MVP] wrote:[vbcol=seagreen]
> Debugging 101:
>
> Change
>
> Recordset1.Source = "SELECT * FROM MainTable WHERE " & _
> " [make] = '" & chosencar & "' ORDER BY Price ASC"
>
> to
>
> sql = "SELECT * FROM MainTable WHERE " & _
> " [make] = '" & chosencar & "' ORDER BY Price"
> response.write sql
> response.end
>
> Show us the result! And if you still get an error message, please copy and
> paste explicitly. I know of about 30 different messages that include the
> words "syntax" and "incorrect"... the exact error message (and maybe even
> the line it occurs on) would be much more helpful.
>
> A
>
>
>
> <gjoneshtfc@volcanomail.com> wrote in message
> news:1150900526.748891.190220@y41g2000cwy.googlegroups.com...

Bob Barrows [MVP]

2006-06-21, 1:21 pm

gjoneshtfc@volcanomail.com wrote:
> Thanks for your help Bob,
>
> Just one query with what you have written - how do i link that in with
> the VBScript Recordset1.Source?

You don't need to. Setting the Source property to a sql statement and
opening the recordset achieves the same result as executing the sql
statement using the Command object.

Why am I recommending the Command object? or better yet stored procedures?
SQL Injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

Since you did not tell us what type of database you are using, I will
refrain from posting the links that explain how to use stored procedures.
Here's my canned post about using Command objects:
http://groups-beta.google.com/group...2e36562fee7804e

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Aaron Bertrand [SQL Server MVP]

2006-06-21, 1:21 pm

> [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
> operator) in query expression ' [ & _ ] [make] = ' " & chosencar & " '

I think you copied my code wrong, the & _ does not belong inside the string,
but apparently you placed it there.

A

gjoneshtfc@volcanomail.com

2006-06-21, 1:21 pm

This is the code i have (spaced the single/double " out for clarity):

SELECT *
FROM MainTable
WHERE " & _ " [make] = ' " & chosencar & " '
ORDER BY Price ASC

Is this not correct?

Thanks, Gareth

Aaron Bertrand [SQL Server MVP] wrote:
>
> I think you copied my code wrong, the & _ does not belong inside the string,
> but apparently you placed it there.
>
> A

gjoneshtfc@volcanomail.com

2006-06-21, 1:21 pm

Thanks Bob,

Just to be completely clear on what needs to be done my current code at
the top of my webpage is:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%
Dim Recordset1
Dim Recordset1_numRows

chosencar = Replace(Request.QueryString("make"), "'", "''")

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_connection_hmitchell_STRING
Recordset1.Source = "SELECT * FROM MainTable WHERE " & _ " [make] =
'" & chosencar & "' ORDER BY Price ASC"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>

So would i just delete the following line from my webpage code:

Recordset1.Source = "SELECT * FROM MainTable WHERE " & _ " [make] =
'" & chosencar & "' ORDER BY Price ASC"

And then paste in this in its place?:

dim sql, arParms, make, cmd
make=Request.QueryString("make")
'validate make - make sure it contains what it's supposed to contain
'if it's valid, then:

sql="SELECT <list of fields> FROM MainTable " & _
"WHERE Make=? ORDER BY Price ASC"

'see the "?" That's called a parameter marker. You can
'have as many as you need. Now let's use a command object
'to pass a value to that parameter:

arParms=array(make) 'an array is required
set cmd=createobject("adodb.commmand")
with cmd
.commandtype=1 'adCmdText
.commandtext=sql
set .activeconnection=objconn
set Recordset1 = .Execute(,arParms)
End With
if not Recordset1.eof then ...

Regards, Gareth

Aaron Bertrand [SQL Server MVP]

2006-06-21, 1:21 pm

NO! Did you see mine? The & _ is NOT PART OF THE STRING! It's used to
wrap commands across multiple lines.

sql = "SELECT * FROM MainTable WHERE " & _
" [make] = '" & chosencar & "' ORDER BY Price ASC"

Try copying and pasting instead of transcribing or typing from memory.

<gjoneshtfc@volcanomail.com> wrote in message
news:1150902323.523916.233180@g10g2000cwb.googlegroups.com...
> This is the code i have (spaced the single/double " out for clarity):
>
> SELECT *
> FROM MainTable
> WHERE " & _ " [make] = ' " & chosencar & " '
> ORDER BY Price ASC
>
> Is this not correct?
>
> Thanks, Gareth
>
> Aaron Bertrand [SQL Server MVP] wrote:
>

gjoneshtfc@volcanomail.com

2006-06-21, 1:21 pm

My apologies! I did copy and paste your code but it wasnt working so i
moved it onto one line. I am still learning a lot of this and did not
know what the & _ was for. However, i know now for the future and will
not be making the same mistake again!

I took the " & _ " out because my code was on one line and it now works
perfectly! Thanks for your time and patience with me. I am forever
grateful.

Thanks again,
Gareth

Aaron Bertrand [SQL Server MVP] wrote:
[vbcol=seagreen]
> NO! Did you see mine? The & _ is NOT PART OF THE STRING! It's used to
> wrap commands across multiple lines.
>
> sql = "SELECT * FROM MainTable WHERE " & _
> " [make] = '" & chosencar & "' ORDER BY Price ASC"
>
> Try copying and pasting instead of transcribing or typing from memory.
>
>
>
>
> <gjoneshtfc@volcanomail.com> wrote in message
> news:1150902323.523916.233180@g10g2000cwb.googlegroups.com...

gjoneshtfc@volcanomail.com

2006-06-21, 1:21 pm

Aaron,

Hopefully you are still keeping an eye on this thread. I now have a
similar problem but i know the reason why! I need to do the same thing
but for "price" not "make". If i specify price in my database to be
text and do the same thing as for make my webpage works. However, it
does not bring up the correct results. When i change price in the
database to number i then get the following error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in
criteria expression.
/searchprice.asp, line 15

I believe this is to do with the following line:

varprice = Replace(Request.QueryString("price"), "'", "''" )

For completeness my Recordset line is now:

Recordset1.Source = "SELECT * FROM MainTable WHERE [price] < '" &
varprice & "' ORDER BY Price DESC"

Do you have any ideas? Thanks again for all your help up to this point,
Regards, Gareth

Aaron Bertrand [SQL Server MVP]

2006-06-21, 1:21 pm

A number is not a string. You do not need ' around a price.

And when you get to the dates, they are delimited by #.

<gjoneshtfc@volcanomail.com> wrote in message
news:1150910899.845322.288040@m73g2000cwd.googlegroups.com...
> Aaron,
>
> Hopefully you are still keeping an eye on this thread. I now have a
> similar problem but i know the reason why! I need to do the same thing
> but for "price" not "make". If i specify price in my database to be
> text and do the same thing as for make my webpage works. However, it
> does not bring up the correct results. When i change price in the
> database to number i then get the following error:
>
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
> [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in
> criteria expression.
> /searchprice.asp, line 15
>
> I believe this is to do with the following line:
>
> varprice = Replace(Request.QueryString("price"), "'", "''" )
>
> For completeness my Recordset line is now:
>
> Recordset1.Source = "SELECT * FROM MainTable WHERE [price] < '" &
> varprice & "' ORDER BY Price DESC"
>
> Do you have any ideas? Thanks again for all your help up to this point,
> Regards, Gareth
>

gjoneshtfc@volcanomail.com

2006-06-21, 1:21 pm

Aaron,

Your help is fantastic. Problem solved straight away. I am now starting
to understand exactly what i am doing and its down to making lots of
mistakes and you fixing them and explaing why!

Thanks for all your help,
Regards, Gareth

Aaron Bertrand [SQL Server MVP] wrote:
[vbcol=seagreen]
> A number is not a string. You do not need ' around a price.
>
> And when you get to the dates, they are delimited by #.
>
>
> <gjoneshtfc@volcanomail.com> wrote in message
> news:1150910899.845322.288040@m73g2000cwd.googlegroups.com...