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

well, i'm not famililar with ASP, but there were some lines of code that had me a bit suspicious. the first thing being :
rsResults_cmd.CommandText = "SELECT * FROM YotStock WHERE ? AND construction=? ORDER BY price ASC"

I don't know if the ? are mistakes or if they represent parameters. If they do represent parameters, then the "where ? and " parts got me confused, cuz you don't have a field name (like construction) that you're comparing the parameter to. if all thats ok, what i'd try is to print out the SQL thats generated and then seeing if that brings up the correct recordset.