ASP Access Advanced Search

**RFH Reseller: Sol** · 12th June 2008, 11:22 AM

[see above - duplicated post by accident]

**RFH Reseller: Sol** · 12th June 2008, 11:25 AM

I don't think you can specify a column name as a parameter, which is what Alistair has done:

Code:

rsSpec__MMWall = "0"
If (Request.Form("txt_thickness")    <> "") Then 
  rsSpec__MMWall = Request.Form("txt_thickness")   
  strQuery = strQuery + " AND ? BETWEEN wp_wt_from AND wp_wt_to"
End If

I think you should look to do something like:

Code:

If (Request.Form("txt_thickness_from") <> "") then
  If (Request.Form("txt_thickness_to") <> "") then
     strQuery = strQuery + "AND [thicknessCol] BETWEEN ? AND ?"
  Else
     strQuery = strQuery + "AND [thicknessCol] >= ?"
  End If
Else
  If (Request.Form("txt_thickness_to") <> "") then
     strQuery = strQuery + "AND [thicknessCol] <= ?"
  End If
End If

note: You should replace [thicknessCol] with the column name that stores the items thickness.

**RFH Reseller: s80wkr** · 13th June 2008, 12:10 PM

I am still having trouble with this getting the same error

Any chace that someone could post a working page with test details???

To recap I want 2 dropdowns and 2 text fields.

All the search criteria must work independently of each other when left blank.

Many thanks,

Stephen

**RFH Reseller: SKILLIT** · 14th June 2008, 05:47 PM

Originally Posted by s80wkr

I am still having trouble with this getting the same error

Any chace that someone could post a working page with test details???

To recap I want .

All the search criteria must work independently of each other when left blank.

Many thanks,

Stephen

O.K. - can you explain how you get to the between values with "2 dropdowns and 2 text fields"

David.

**RFH Reseller: s80wkr** · 16th June 2008, 02:00 PM

Entered into the database there are seven fields

ID
Specification
Grade
Wall Thickness from (2 decimal places)
Wall Thickness to (2 decimal places)
Pipe Diameter from (2 decimal places)
Pipe Diamter to (2 decimal places)

Specification and Grade are your 2 dropdowns populated by a DISTINCT statements for Specification and Grade taken from the database.

The two text fields are for Wall Thickness and Pipe Diameter

The value that is given in the Wall Thickness text box has to be between these two values Wall Thickness from and Wall Thickness To

The same applies for Pipe Diameter.

Many thanks for your help

**RFH Reseller: SKILLIT** · 16th June 2008, 04:47 PM

Try the following:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta content="text/html; charset=windows-1252" http-equiv="Content-Type" />
<title>Untitled 1</title>
</head>
<body>


<%'Connection String is defined in above include
If lcase(Request.ServerVariables("REQUEST_METHOD")) = "post" Then
Set Cnxn = Server.CreateObject("ADODB.Connection")
Cnxn.Open Connect
strQuery = "SELECT * FROM Dave.qry_weld_pro WHERE specification = ? AND grade = ?"

Set rsSpec_cmd = Server.CreateObject("ADODB.Command")
rsSpec_cmd.CommandType = adCmdText
rsSpec_cmd.ActiveConnection = Cnxn

Set P1=rsSpec_cmd.CreateParameter(, adVarChar, AdParamInput, 50, Request.Form("mnu_spec"))
rsSpec_cmd.Parameters.Append P1
Set P2=rsSpec_cmd.CreateParameter(, adVarChar, AdParamInput, 50, Request.Form("txt_type"))
rsSpec_cmd.Parameters.Append P2

If Request.Form("txt_thickness") <> "" Then
rsSpec__MMColParam3 = Request.Form("txt_thickness")
strQuery = strQuery & " AND Wall_Thickness_from <= ? AND Wall_Thickness_to >=?"
Set P3=rsSpec_cmd.CreateParameter(, adDouble, AdParamInput, 20, Request.Form("txt_thickness")) ' adDouble
rsSpec_cmd.Parameters.Append P3
Set P4=rsSpec_cmd.CreateParameter(, adDouble, AdParamInput, 20, Request.Form("txt_thickness")) ' adDouble
rsSpec_cmd.Parameters.Append P4
End If
If (Request.Form("txt_diameter") <> "") Then
strQuery = strQuery + " AND Pipe_Diameter_from <= ? And Pipe_Diameter_to >=?"
Set P5=rsSpec_cmd.CreateParameter(, adDouble, AdParamInput, 20, Request.Form("txt_diameter")) ' adDouble
rsSpec_cmd.Parameters.Append P5
Set P6=rsSpec_cmd.CreateParameter(, adDouble, AdParamInput, 20, Request.Form("txt_diameter")) ' adDouble
rsSpec_cmd.Parameters.Append P6
End If
rsSpec_cmd.Prepared = true
rsSpec_cmd.CommandText = strQuery

Set RS1 = Server.CreateObject("ADODB.Recordset")
Rs1.CursorType = 1
Rs1.CursorLocation = 3
Rs1.LockType = 3
RS1.Open rsSpec_cmd
If NOT Rs1.Eof Then%><table border="1">
<% Do until Rs1.EOF%>
<tr><td><%=Rs1("Specification")%></td><td><%=Rs1("Grade")%></td><td><%=Rs1("Wall_Thickness_from")%></td><td><%=Rs1("Wall_Thickness_to")%></td><td><%=Rs1("Pipe_Diameter_from")%></td><td><%=Rs1("Pipe_Diameter_to")%></td></tr>
<%Rs1.MoveNext
Loop%>
</table>
<%End If
Else%>
<form action="/Test/Test.asp" method="post">
Specification: <select name="mnu_spec">
<option>AAAAAA</option>
<option>BBBBBB</option>
</select><br />
Grade: <select name="txt_type">
<option>A</option>
<option>B</option>
</select><br />
Wall Thickness <input name="txt_thickness" type="text" />Diameter
<input name="txt_diameter" type="text" /><input type="submit" name="S1"/></form>
 <%End If
%>
</body>
</html>

You can find my crude example at: http://bookholidayrentals.com/Test/Test.asp

Assumptions: In the Database To's are >= From's
Validation needed for numeric fields
It's raining here in what should be sunny France.

I have used a recordset as this would be a good structure (I think) for an update script (and I like them).

Good luck,
David.

Test Data:
1AAAAAAA1.002.003.004.002AAAAAAA1.202.802.005.003B BBBBBA0.608.003.004.004BBBBBBB0.4020.003.2020.005A AAAAAA0.4019.001.0030.00