Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Simple SQL statement and request.querystring

Reply
Thread Tools

Simple SQL statement and request.querystring

 
 
gjoneshtfc@volcanomail.com
Guest
Posts: n/a
 
      06-20-2006
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

 
Reply With Quote
 
 
 
 
Aaron Bertrand [SQL Server MVP]
Guest
Posts: n/a
 
      06-20-2006
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.






<(E-Mail Removed)> wrote in message
news:(E-Mail Removed) oups.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
>



 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      06-20-2006
http://www.velocityreviews.com/forums/(E-Mail Removed) 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/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.


 
Reply With Quote
 
gjoneshtfc@volcanomail.com
Guest
Posts: n/a
 
      06-21-2006
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("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.
>
>
>
>
>
>
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed) oups.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
> >


 
Reply With Quote
 
gjoneshtfc@volcanomail.com
Guest
Posts: n/a
 
      06-21-2006
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:
> (E-Mail Removed) 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/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.


 
Reply With Quote
 
Aaron Bertrand [SQL Server MVP]
Guest
Posts: n/a
 
      06-21-2006
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



<(E-Mail Removed)> wrote in message
news:(E-Mail Removed) oups.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("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.
>>
>>
>>
>>
>>
>>
>> <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed) oups.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
>> >

>



 
Reply With Quote
 
gjoneshtfc@volcanomail.com
Guest
Posts: n/a
 
      06-21-2006
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.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
>
>
>
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed) oups.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("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.
> >>
> >>
> >>
> >>
> >>
> >>
> >> <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed) oups.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
> >> >

> >


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      06-21-2006
(E-Mail Removed) 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/...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"


 
Reply With Quote
 
Aaron Bertrand [SQL Server MVP]
Guest
Posts: n/a
 
      06-21-2006
> [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


 
Reply With Quote
 
gjoneshtfc@volcanomail.com
Guest
Posts: n/a
 
      06-21-2006
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


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Simple SQL Statement....broken brain... D. Shane Fowlkes ASP .Net 4 11-22-2004 06:49 PM
asp and sql statement in sql server db weiwei ASP General 3 09-22-2004 04:12 PM
DBI SQL column datatype not jiving with SQL statement requirement dna Perl 1 01-18-2004 04:15 PM
Re: SQL statement working in SQL Server but not in .aspx.cs page David Browne ASP .Net 0 08-21-2003 10:43 PM
Re: SQL statement working in SQL Server but not in .aspx.cs page William \(Bill\) Vaughn ASP .Net 0 08-21-2003 10:41 PM



Advertisments