I’m a complete newbie when it comes to sql. I’m working on a Webthang tutorial and I can’t get the records to be displayed in the results page correctly.
I’m using Dreamweaver 8.0.2 and access database.
Query is displaying all records and not filtering them by price (other filter works fine).
This is the form code:
<form id="search1" name="search1" method="post" action="results.asp">
<table width="100%" border="0" cellspacing="2" cellpadding="2">
<tr>
<td align="center" valign="middle">Search</td>
</tr>
<tr>
<td align="left" valign="middle"><select name="ysCon" id="ysCon">
<option value="GRP" selected="selected">GRP</option>
<option value="Wood">Wood</option>
<option value="Steel">Steel</option>
<option value="Aluminium">Aluminium</option>
<option value="Ferro Cement">Ferro Cement</option>
<option value="Composite">Composite</option>
</select></td>
</tr>
<tr>
<td align="left" valign="middle"><select name="ysPrice" id="ysPrice">
<option value="BETWEEN 1 AND 2000000" selected="selected">Any Price</option>
<option value="BETWEEN 1 AND 19999">Up to £ 20000</option>
<option value="BETWEEN 20000 AND 50000">£20000 to £50000</option>
<option value="BETWEEN 50000 AND 2000000">Over £50000</option>
</select> </td>
</tr>
<tr>
<td> </td>
</tr>
<tr>
<td align="center" valign="middle"><input type="submit" name="Submit" value="SEARCH" /></td>
</tr>
</table>
</form>


I think the problem is in the advanced recordset query which is:


<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/YotShop.asp" -->
<%
Dim rsResults__MMColParam
rsResults__MMColParam = "1"
If (Request.Form("price" <> "" Then
rsResults__MMColParam = Request.Form("price"
End If
%>
<%
Dim rsResults__ysCon
rsResults__ysCon = "GRP"
If (Request.Form("ysCon" <> "" Then
rsResults__ysCon = Request.Form("ysCon"
End If
%>
<%
Dim rsResults
Dim rsResults_cmd
Dim rsResults_numRows

Set rsResults_cmd = Server.CreateObject ("ADODB.Command"
rsResults_cmd.ActiveConnection = MM_YotShop_STRING
rsResults_cmd.CommandText = "SELECT * FROM YotStock WHERE ? AND construction=? ORDER BY price ASC"
rsResults_cmd.Prepared = true
rsResults_cmd.Parameters.Append rsResults_cmd.CreateParameter("param1", 5, 1, -1, rsResults__MMColParam) ' adDouble
rsResults_cmd.Parameters.Append rsResults_cmd.CreateParameter("param2", 200, 1, 255, rsResults__ysCon) ' adVarChar

Set rsResults = rsResults_cmd.Execute
rsResults_numRows = 0
%>


Can anybody help me in discovering why don’t I get all filters to work correctly?

Many thanks.


Mickey

unfortunately I know no asp or vb but looking at this line . . .

SELECT * FROM YotStock WHERE ? AND construction = ? ORDER BY price ASC

that looks like the problem to me.

those two ? marks will give you an error, I would expect to see something like . .

WHERE fieldname = valueToSearchFor AND construction = valueExpected ORDER BY price ASC

there should be some code that inserts the values that you expect to find in construction and whatever the other field is that you are searching

Alan,
Thanks very much for your reply.

In Dreamweaver 8.0.2 Advanced Recordset the query is written as following:
SELECT *
FROM YotStock
WHERE MMColParam AND construction=ysCon
ORDER BY price ASC

it also sets the following: Name=MMColParam , Type=Numeric, Value=Request.Form("price" , Default value=1.
Name=ysCon , Type=Text , Value=Request.Form("ysCon" , Default value=GRP.

I have no problem with ysCon and I'm not getting any errors but MMColParam isn't filtering.
I don't know why Dreamweaver writes the code with wildcards wile the recordset doesn’t.
But when I replace the code with the recordset sql I get the following error:
• Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.
/YotShop/results.asp, line 29
• POST Data:
ysCon=GRP&ysPrice=Between+0+And+20000&Submit=SEARCH
Line 29 in my page is as follows: Set rsResults = rsResults_cmd.Execute


Any suggestions?

sorry to say I am not into asp at all, I decided to go php, mysql when I started database-driven sites, this is probably telling you stuff you already know . . .

I use print statements fairly extensively in debugging to look at what intermediate results are being produced by my code, really handy sometimes it finds those spelling mistakes in variable names etc that are staring you in the face.

Alan,
I still didn't resolve the problem, must be the lack of experience <img src=../images/dmxzone/forum/icon_smile_blush.gif border=0 align=middle>.

But in any case thanks very much for your time and if anybody else has any suggestions please let me know.