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

[ Post a follow-up to this message ]

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
>

[ Post a follow-up to this message ]

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.

[ Post a follow-up to this message ]

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 SELE
CT
> * 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.  Constructin
g
> 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 no
t.
> :-)
>
>
>
>
>
> <gjoneshtfc@volcanomail.com> wrote in message
> news:1150825931.393290.315630@u72g2000cwu.googlegroups.com... 

[ Post a follow-up to this message ]

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

[ Post a follow-up to this message ]

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:
> [url]http://msdn.microsoft.com/library/en-us/ado270/htm/mdmscadoapireference.asp[/url
]
>
>
> --
> 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.

[ Post a follow-up to this message ]

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 an
d
> 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... 

[ Post a follow-up to this message ]

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...r />
2fee7804e

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

[ Post a follow-up to this message ]

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

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

A

[ Post a follow-up to this message ]

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

[ Post a follow-up to this message ]