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
>

> 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.
> :-)
>
>
>
>
>
> <gjonesh***@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
> >

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:
>> 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.
>> :-)
>>
>>
>>
>>
>>
>> <gjonesh***@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
>> >
>

> 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
>
>
>
> <gjonesh***@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:
> >> 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.
> >> :-)
> >>
> >>
> >>
> >>
> >>
> >> <gjonesh***@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
> >> >
> >

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

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

> 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.
>
>
>
>
> <gjonesh***@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:
> >> > [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
> >

news:1150910899.845322.288040@m73g2000cwd.googlegroups.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
>

> A number is not a string.  You do not need ' around a price.
>
> And when you get to the dates, they are delimited by #.
>
>
> <gjonesh***@volcanomail.com> wrote in message
> news:1150910899.845322.288040@m73g2000cwd.googlegroups.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
> >

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

> gjonesh***@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-us/ado270/htm/mdmscadoapireference.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.