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 15 2866
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.
:-)
<gj********@volcanomail.com> wrote in message
news:11**********************@u72g2000cwu.googlegr 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 gj********@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/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.
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. :-)
<gj********@volcanomail.com> wrote in message news:11**********************@u72g2000cwu.googlegr 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
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: gj********@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/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.
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
<gj********@volcanomail.com> wrote in message
news:11**********************@y41g2000cwy.googlegr 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. :-)
<gj********@volcanomail.com> wrote in message news:11**********************@u72g2000cwu.googlegr 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 >
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 <gj********@volcanomail.com> wrote in message news:11**********************@y41g2000cwy.googlegr 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. :-)
<gj********@volcanomail.com> wrote in message news:11**********************@u72g2000cwu.googlegr 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 >
> [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 gj********@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/...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"
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
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
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.
<gj********@volcanomail.com> wrote in message
news:11**********************@g10g2000cwb.googlegr oups.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: > [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
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: 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.
<gj********@volcanomail.com> wrote in message news:11**********************@g10g2000cwb.googlegr oups.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: > [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
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
A number is not a string. You do not need ' around a price.
And when you get to the dates, they are delimited by #.
<gj********@volcanomail.com> wrote in message
news:11**********************@m73g2000cwd.googlegr oups.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
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: A number is not a string. You do not need ' around a price.
And when you get to the dates, they are delimited by #.
<gj********@volcanomail.com> wrote in message news:11**********************@m73g2000cwd.googlegr oups.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 This discussion thread is closed Replies have been disabled for this discussion. Similar topics
13 posts
views
Thread by Samantha Smit |
last post: by
|
17 posts
views
Thread by Paul |
last post: by
|
9 posts
views
Thread by Peter |
last post: by
|
1 post
views
Thread by Tyro |
last post: by
|
2 posts
views
Thread by mahsa |
last post: by
|
1 post
views
Thread by Miguel Dias Moura |
last post: by
|
4 posts
views
Thread by Tim::.. |
last post: by
| |
7 posts
views
Thread by =?Utf-8?B?QVRT?= |
last post: by
| | | | | | | | | | |