|
P: n/a
|
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
| |
Share this Question
|
P: n/a
|
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 | |
|
P: n/a
| 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. | |
|
P: n/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
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 | |
|
P: n/a
|
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. | |
|
P: n/a
|
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
> | |
|
P: n/a
|
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
> | |
|
P: n/a
|
> [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 | |
|
P: n/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" | |
|
P: n/a
|
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
| |
|
P: n/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
| |
|
P: n/a
|
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
| |
|
P: n/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 | |
|
P: n/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
| |
|
P: n/a
|
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 | |
|
P: n/a
|
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. | |  Question stats - viewed: 2650
- replies: 15
- date asked: Jun 20 '06
|
