Simple SQL statement and request.querystring

[gjoneshtfc]

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]]

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.

 

[Bob Barrows [MVP]]

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/en-us/ado270/htm/mdmscadoapireference.asp

 

[gjoneshtfc]

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

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.

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

 

[gjoneshtfc]

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

 

[Aaron Bertrand [SQL Server MVP]]

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

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

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.

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

 

[gjoneshtfc]

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

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

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

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.






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]]

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/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

 

[Aaron Bertrand [SQL Server MVP]]

[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]

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

[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]

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"%>
<!--#include file="Connections/connection_hmitchell.asp" -->
<%
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]]

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.

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

[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]

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

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.

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

[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]

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]]

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]

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

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

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

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