Hi

I'm very new to dynamic pages and I've been trying to set up a search function that will search a range of prices. I was working from a Webthang.co.uk tutorial but just cannot get the thing to work. I have narrowed the problem down to my default.asp page passing the string (BETWEEN 1 AND 20000) to the results.asp page - well, at least I think I'm right!

All I want to happen is that when a selection is made from a price range drop-down box, the results will then be displayed on a new page. In aan attempt to work out the problem I'v ereduced the pages to bare bones. The code I have for the search page is:


<pre id=code><font face=courier size=2 id=code>&lt;form id="search1" name="search1" method="post" action="results.asp"&gt;
&lt;table width="700" border="0" cellspacing="0" cellpadding="1"&gt;
&lt;tr&gt;
&lt;td&gt;&lt;select name="ysprice" id="ysprice"&gt;
&lt;option value="1 AND 200000"&gt;Any&lt;/option&gt;
&lt;option value="1 AND 20000"&gt;&pound;1 to &pound;20000&lt;/option&gt;
&lt;option value="20000 AND 50000"&gt;&pound;20000 to &pound;50000&lt;/option&gt;
&lt;option value="50000 AND 200000"&gt;&pound;50000 to &pound;200000&lt;/option&gt;
&lt;/select&gt;
&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&nbsp;&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;input type="submit" name="Submit" value="Submit" /&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;/table&gt;
&lt;/form&gt;

The code from the results page:

&lt;%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%&gt;
&lt;!--#include file="Connections/yotshopODBC.asp" --&gt;
&lt;%
Dim Recordset1__MMColParam
Recordset1__MMColParam = "1"
If (Request.Form("ysprice" &lt;&gt; "" Then
Recordset1__MMColParam = Request.Form("ysprice"
End If
%&gt;
&lt;%
Dim Recordset1
Dim Recordset1_cmd
Dim Recordset1_numRows

Set Recordset1_cmd = Server.CreateObject ("ADODB.Command"
Recordset1_cmd.ActiveConnection = MM_yotshopODBC_STRING
Recordset1_cmd.CommandText = "SELECT * FROM YotStock WHERE price BETWEEN ? ORDER BY price ASC"
Recordset1_cmd.Prepared = true
Recordset1_cmd.Parameters.Append Recordset1_cmd.CreateParameter("param1", 200, 1, 255, Recordset1__MMColParam) ' adVarChar

Set Recordset1 = Recordset1_cmd.Execute - Line 21
Recordset1_numRows = 0
%&gt;
&lt;!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"&gt;
&lt;html xmlns="www.w3.org/1999/xhtml"&gt;
&lt;head&gt;
&lt;meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /&gt;
&lt;title&gt;Untitled Document&lt;/title&gt;
&lt;/head&gt;

&lt;body&gt;
&lt;p&gt;Price: &lt;%=(Recordset1.Fields.Item("price".Value)%&gt;&lt;/p&gt;
&lt;p&gt;Construction: &lt;%=(Recordset1.Fields.Item("construction".Value)%&gt;&lt;/p&gt;
&lt;p&gt;Design: &lt;%=(Recordset1.Fields.Item("design".Value)%&gt;&lt;/p&gt;
&lt;/body&gt;
&lt;/html&gt;
&lt;%
Recordset1.Close()
Set Recordset1 = Nothing
%&gt;</font id=code></pre id=code>

Everytime I preview it I get the following error:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Between operator without And in query expression 'price BETWEEN Pa_RaM000'.
/yot/results.asp, line 21



If someone can shed any light I would be most appreciative. I am tearing my hair out!!

Many thanks

Steve<font color=red></font id=red><b></b>

------------------------------
DWMX | ASP | ACCESS
------------------------------
We will do what is right, of course - Tarzan, the animated series.

Hi Stephen

A quick fix for this, amend this line:

Recordset1_cmd.CommandText = "SELECT * FROM YotStock WHERE price BETWEEN ? ORDER BY price ASC"

to this:

Recordset1_cmd.CommandText = "SELECT * FROM YotStock WHERE price BETWEEN " & Recordset1__MMColParam & " ORDER BY price ASC"

Comment out this line:

' Recordset1_cmd.Parameters.Append Recordset1_cmd.CreateParameter("param1", 200, 1, 255, Recordset1__MMColParam) ' adVarChar

Let me know how you get on.

Sharing Knowledge Saves Valuable Time!!!
~ ~ ~ ~ ~ ~
<b>Lee Diggins</b> - <i>DMXzone Manager</i>
<font size="1">[ Studio MX/MX2004 | ASP -> VBScript/PerlScript/JavaScript | SQL | CSS ]</font>

Lee

This obviously proves what a newbie I am at this!!!

It worked like a dream - and while I still have some hair!

I do have one q that follows this though and that is; what happens if I want to add another list to be able to narrow the search down? I don't seem to be able to do it in Dreamweaver as it complains that the MMColParam is an invalid variable name.

Does that makes sense??

Thanks for your help so far, it really is much appreciated.

Steve

------------------------------
DWMX | ASP | ACCESS
------------------------------
We will do what is right, of course - Tarzan, the animated series.

Hmm, I may have jumped the gun a little!

Although the query works, it's not actually doing what I'm asking of it. The code you provided made sure it didn't get an error anymore but it's displying all of the records every time. If I try and put BETWEEN into the SQL or into the list box I'm preneted with an error!

It's something like this:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/yot/results.asp, line 17, column 77
Recordset1_cmd.CommandText = "SELECT * FROM YotStock WHERE price " & BETWEEN Recordset1_MMColParam & " ORDER BY price ASC"
----------------------------------------------------------------------------^

Anything? Anyone?

Ta

Steve

------------------------------
DWMX | ASP | ACCESS
------------------------------
We will do what is right, of course - Tarzan, the animated series.

Okay, Okay, my bad!

I had inadvertantly opened DW8 instead of MX and it just doesn't work in that version - I guess something to do with SQL injection, I don't know I'm just guessing??

So it is actually working just fine!!

If anyone does know how I'd get it working in version 8 that would be good as I don't really want to use the student version of MX that I have.

THNX

Steve

------------------------------
DWMX | ASP | ACCESS
------------------------------
We will do what is right, of course - Tarzan, the animated series.

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
If anyone does know how I'd get it working in version 8 that would be good as I don't really want to use the student version of MX that I have.


Steve
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Steve,
I noticed your post today.
I could do with some help on the same problem with Dreamweaver 8 (I'm working on the same tutorial). So if you found the solution for dreamweaver 8 I would appreciate it.

Thanks,
Mickey