Simple SQL statement and request.queryst ring

[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.Sour ce = "SELECT * FROM MainTable ORDER BY Price ASC"

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

dim chosencar
chosencar=Reque st.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.Sour ce = "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("m ake"), "'", "''")
Recordset1.Sour ce = "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.
:-)

<gj********@vol canomail.com> wrote in message
news:11******** **************@ u72g2000cwu.goo glegroups.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.Sour ce = "SELECT * FROM MainTable ORDER BY Price ASC"

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

dim chosencar
chosencar=Reque st.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.Sour ce = "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]]

gj********@volc anomail.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.Sour ce = "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=Reque st.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.Sour ce = "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.Qu eryString("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(m ake) 'an array is required
set cmd=createobjec t("adodb.commma nd")
with cmd
.commandtype=1 'adCmdText
.commandtext=sq l
set .activeconnecti on=objconn
set Recordset1 = .Execute(,arPar ms)
End With
if not Recordset1.eof then ...

You can find the ADO documentation here:
http://msdn.microsoft.com/library/en...ireference.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]

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:

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

chosencar = Replace(Request .QueryString("m ake"), "'", "''")
Recordset1.Sour ce = "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.
:-)

<gj********@vol canomail.com> wrote in message
news:11******** **************@ u72g2000cwu.goo glegroups.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.Sour ce = "SELECT * FROM MainTable ORDER BY Price ASC"

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

dim chosencar
chosencar=Reque st.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.Sour ce = "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.Sour ce? Or is this a completely different
approach to my problem?

Thanks again, Gareth
Bob Barrows [MVP] wrote:

gj********@volc anomail.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.Sour ce = "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=Reque st.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.Sour ce = "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.Qu eryString("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(m ake) 'an array is required
set cmd=createobjec t("adodb.commma nd")
with cmd
.commandtype=1 'adCmdText
.commandtext=sq l
set .activeconnecti on=objconn
set Recordset1 = .Execute(,arPar ms)
End With
if not Recordset1.eof then ...

You can find the ADO documentation here:
http://msdn.microsoft.com/library/en...ireference.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.

[Aaron Bertrand [SQL Server MVP]]

Debugging 101:

Change

Recordset1.Sour ce = "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

<gj********@vol canomail.com> wrote in message
news:11******** **************@ y41g2000cwy.goo glegroups.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:

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

chosencar = Replace(Request .QueryString("m ake"), "'", "''")
Recordset1.Sour ce = "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.
:-)

<gj********@vol canomail.com> wrote in message
news:11******** **************@ u72g2000cwu.goo glegroups.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.Sour ce = "SELECT * FROM MainTable ORDER BY Price ASC"
>
> I have the following code at the start of the recordset:
>
> dim chosencar
> chosencar=Reque st.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.Sour ce = "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

Aaron Bertrand [SQL Server MVP] wrote:

Debugging 101:

Change

Recordset1.Sour ce = "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

<gj********@vol canomail.com> wrote in message
news:11******** **************@ y41g2000cwy.goo glegroups.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:

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

chosencar = Replace(Request .QueryString("m ake"), "'", "''")
Recordset1.Sour ce = "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.
:-)

<gj********@vol canomail.com> wrote in message
news:11******** **************@ u72g2000cwu.goo glegroups.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.Sour ce = "SELECT * FROM MainTable ORDER BY Price ASC"
>
> I have the following code at the start of the recordset:
>
> dim chosencar
> chosencar=Reque st.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.Sour ce = "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]]

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

[Bob Barrows [MVP]]

gj********@volc anomail.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.Sour ce?

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/...e36562fee7804e

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

[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

Aaron Bertrand [SQL Server MVP] wrote:

[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