ASP Access Advanced Search

[RFH Reseller: s80wkr]

I am having trouble with Access and an ASP Search Page

I have 4 fields to search

Specification
Type
Thickness
Diameter

Specification and Type are dropdown menus

Thickness and Diameter are text fields which will be checked to see if they are between two numbers

I want to be able to leave Thickness and Diameter blank if need be and show all the results that match the other fields.

<%
Dim rsSpec__MMColParam
rsSpec__MMColParam = "0"
If (Request.Form("mnu_spec")   <> "") Then 
  rsSpec__MMColParam = Request.Form("mnu_spec")  
End If
%>
<%
Dim rsSpec__MMColParam1
rsSpec__MMColParam1 = "0"
If (Request.Form("txt_type")   <> "") Then 
  rsSpec__MMColParam1 = Request.Form("txt_type")  
End If
%>
<%
Dim rsSpec__MMColParam2
rsSpec__MMColParam2 = "0"
If (Request.Form("txt_thickness")    <> "") Then 
  rsSpec__MMColParam2 = Request.Form("txt_thickness")   
End If
%>
<%
Dim rsSpec__MMColParam3
rsSpec__MMColParam3 = "0"
If (Request.Form("txt_thickness")    <> "") Then 
  rsSpec__MMColParam3 = Request.Form("txt_thickness")   
End If
%>
<%
Dim rsSpec__MMColParam4
rsSpec__MMColParam4 = "0"
If (Request.Form("txt_diameter")    <> "") Then 
  rsSpec__MMColParam4 = Request.Form("txt_diameter")   
End If
%>
<%
Dim rsSpec__MMColParam5
rsSpec__MMColParam5 = "0"
If (Request.Form("txt_diameter")    <> "") Then 
  rsSpec__MMColParam5 = Request.Form("txt_diameter")   
End If
%>
<%
Dim rsSpec
Dim rsSpec_cmd
Dim rsSpec_numRows
Set rsSpec_cmd = Server.CreateObject ("ADODB.Command")
rsSpec_cmd.ActiveConnection = MM_connWP_STRING
rsSpec_cmd.CommandText = "SELECT * FROM qry_weld_pro WHERE spec_name = ? AND wp_grade = ? AND wp_wt_from <= ? AND wp_wt_to >= ? AND wp_ps_from <= ? AND wp_ps_to >= ?" 
rsSpec_cmd.Prepared = true
rsSpec_cmd.Parameters.Append rsSpec_cmd.CreateParameter("param1", 200, 1, 255, rsSpec__MMColParam) ' adVarChar
rsSpec_cmd.Parameters.Append rsSpec_cmd.CreateParameter("param2", 200, 1, 255, rsSpec__MMColParam1) ' adVarChar
rsSpec_cmd.Parameters.Append rsSpec_cmd.CreateParameter("param3", 5, 1, -1, rsSpec__MMColParam2) ' adDouble
rsSpec_cmd.Parameters.Append rsSpec_cmd.CreateParameter("param4", 5, 1, -1, rsSpec__MMColParam3) ' adDouble
rsSpec_cmd.Parameters.Append rsSpec_cmd.CreateParameter("param5", 5, 1, -1, rsSpec__MMColParam4) ' adDouble
rsSpec_cmd.Parameters.Append rsSpec_cmd.CreateParameter("param6", 5, 1, -1, rsSpec__MMColParam5) ' adDouble
Set rsSpec = rsSpec_cmd.Execute
rsSpec_numRows = 0
%>

Thanks in advance

ste

[RFH Reseller: SKILLIT]

Would it not be simpler to build up the SQL command bit by bit so that optional fields are only included where necessary?

E.G.
StrSql="SELECT * FROM qry_weld_pro WHERE spec_name='" & Request.Form("mnu_spec") & "' AND "
StrSql=StrSql & "wp_grade=" & Request.Form("txt_thickness")
If Request.Form("txt_thickness") <> "" Then
StrSql=StrSql & " AND wp_wt_from <=" & Request.Form("txt_thickness") & " AND wp_wt_to >=" & Request.Form("txt_thickness")
End If
If Request.Form("txt_diameter") <> "" Then
StrSql=StrSql & " AND wp_ps_from <=" & Request.Form("txt_diameter") & " AND wp_ps_to >=" & Request.Form("txt_diameter")
End If

In this way you just omit including blank optional fields in the command.
I have assumed that spec is a string (note the single quote) and thickness is numeric.

Also with the optional fields I assume you are going to add or subtract something from the input field to test against as the Ands could (would as coded) exclude everthing.

David.

[RFH Reseller: Sol]

Firstly, I wound not build up the command as David has shown since that would open the code to a possible SQL Injection Attack.

Using parameters as you have done is definately the right way, but you haven't posted an error message or an indication of what issue you are facing hence we don't know how to correct you.

However, looking at your query I think you are struggling in getting the result set you want since the query is comparing all the parameters with an '=' (equals).

What I would do is build the query dynamically but use a parametrized query:

<%
Dim strQuery = "SELECT * FROM qry_weld_pro WHERE spec_name = ? AND wp_grade = ?"
Dim rsSpec__MMColParam1
Dim rsSpec__MMColParam2
Dim rsSpec__MMColParam3
Dim rsSpec__MMColParam4
Dim rsSpec__MMColParam5
Dim rsSpec__MMColParam6
Dim rsSpec
Dim rsSpec_cmd
Dim rsSpec_numRows
Set rsSpec_cmd = Server.CreateObject ("ADODB.Command")
rsSpec_cmd.ActiveConnection = MM_connWP_STRING
rsSpec__MMColParam1 = "0"
If (Request.Form("mnu_spec")   <> "") Then 
  rsSpec__MMColParam1 = Request.Form("mnu_spec")  
End If
rsSpec_cmd.Parameters.Append rsSpec_cmd.CreateParameter("param1", 200, 1, 255, rsSpec__MMColParam1) ' adVarChar
Dim rsSpec__MMColParam2
rsSpec__MMColParam2 = "0"
If (Request.Form("txt_type")   <> "") Then 
  rsSpec__MMColParam2 = Request.Form("txt_type")  
End If
rsSpec_cmd.Parameters.Append rsSpec_cmd.CreateParameter("param2", 200, 1, 255, rsSpec__MMColParam2) ' adVarChar

If (Request.Form("txt_thickness")    <> "") Then 
  rsSpec__MMColParam3 = Request.Form("txt_thickness")   
  strQuery = strQuery + " AND wp_wt_from <= ?"
  rsSpec_cmd.Parameters.Append rsSpec_cmd.CreateParameter("param3", 5, 1, -1, rsSpec__MMColParam3) ' adDouble
End If
If (Request.Form("txt_thickness")    <> "") Then 
  rsSpec__MMColParam4 = Request.Form("txt_thickness")  
  strQuery = strQuery + " AND wp_wt_to >= ?"
  rsSpec_cmd.Parameters.Append rsSpec_cmd.CreateParameter("param4", 5, 1, -1, rsSpec__MMColParam4) ' adDouble
End If
If (Request.Form("txt_diameter")    <> "") Then 
  rsSpec__MMColParam5 = Request.Form("txt_diameter")  
  strQuery = strQuery + " AND wp_ps_from <= ?" 
  rsSpec_cmd.Parameters.Append rsSpec_cmd.CreateParameter("param5", 5, 1, -1, rsSpec__MMColParam5) ' adDouble
End If
If (Request.Form("txt_diameter")    <> "") Then 
  rsSpec__MMColParam6 = Request.Form("txt_diameter")   
  strQuery = strQuery + " AND wp_ps_to >= ?"
  rsSpec_cmd.Parameters.Append rsSpec_cmd.CreateParameter("param6", 5, 1, -1, rsSpec__MMColParam6) ' adDouble
End If

rsSpec_cmd.CommandText = strQuery
rsSpec_cmd.Prepared = true
Set rsSpec = rsSpec_cmd.Execute
rsSpec_numRows = 0

%>

I've knocked that code up quickly, so some of the syntax may be a bit off.

In addition I would do a quick validation on the inputs to ensure they are off the expected type.

As a final note, I notice you use the same value in your query for Thickness and Diameter twice, and I'm guessing this was done just to show the issue you are having.

[RFH Reseller: s80wkr]

Thanks for both your responses

I am going to use BETWEEN rather than fiddle about with the <= and >=

Not had chance to try it but it is pointing me in the right direction

Will keep you posted

Much Appreciated

Ste

[RFH Reseller: Sol]

You should find it easy to use BETWEEN with the way I dynamically built the query. However, bear in mind that this would require both values and using '<=' and '>=' only requires one value.

By one value, I do mean doing a query like:

Get all records where dimension is more than 10

or

Get all records where dimension is less than 100

[RFH Reseller: SKILLIT]

Sols parameter method is much safer, but you will still need to validate the input if you want to avoid an exception.

Between is the way to go.

Good luck,

David

[RFH Reseller: s80wkr]

Hi Guys,

I have tried the code in a few differnt ways but end up getting the following error:


ADODB.Commanderror '800a0bb9'
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. /wp/results2.asp, line 40

Line 40 is the execute line

Any ideas??

I output the sql to the screen and it looked ok so must be a parameter thing

Thanks,


Stephen

<%
Dim strQuery
strQuery = "SELECT * FROM qry_weld_pro WHERE spec_name = ? AND wp_grade = ?"
Dim strParam
strParam = ""
Dim rsSpec__MMSpec
Dim rsSpec__MMGrade
Dim rsSpec__MMWall
Dim rsSpec__MMDiameter
rsSpec__MMSpec = "0"
If (Request.Form("mnu_spec")   <> "") Then 
  rsSpec__MMSpec = Request.Form("mnu_spec")  
End If
rsSpec__MMGrade = "0"
If (Request.Form("txt_type")   <> "") Then 
  rsSpec__MMGrade = Request.Form("txt_type")  
End If
rsSpec__MMWall = "0"
If (Request.Form("txt_thickness")    <> "") Then 
  rsSpec__MMWall = Request.Form("txt_thickness")   
  strQuery = strQuery + " AND rsSpec__Wall BETWEEN wp_wt_from AND wp_wt_to"
End If
rsSpec__MMDiameter = "0"
If (Request.Form("txt_diameter")    <> "") Then 
  rsSpec__MMDiameter = Request.Form("txt_diameter")  
  strQuery = strQuery + " AND rsSpec__MMDiameter BETWEEN wp_ps_from AND wp_ps_to" 
End If
%>
<%
Dim rsSpec
Dim rsSpec_cmd
Dim rsSpec_numRows
Set rsSpec_cmd = Server.CreateObject ("ADODB.Command")
rsSpec_cmd.ActiveConnection = MM_connWP_STRING
rsSpec_cmd.CommandText = strQuery
rsSpec_cmd.Prepared = true
rsSpec_cmd.Parameters.Append rsSpec_cmd.CreateParameter("param1", 200, 1, 255, rsSpec__MMSpec) ' adVarChar
rsSpec_cmd.Parameters.Append rsSpec_cmd.CreateParameter("param2", 200, 1, 255, rsSpec__MMGrade) ' adVarChar
If (Request.Form("txt_thickness")    <> "") Then
rsSpec_cmd.Parameters.Append rsSpec_cmd.CreateParameter("param3", 200, 1, 255, rsSpec__MMWall) ' adVarChar
End If
If (Request.Form("txt_diameter")    <> "") Then 
rsSpec_cmd.Parameters.Append rsSpec_cmd.CreateParameter("param4", 200, 1, 255, rsSpec__MMDiameter) ' adVarChar
End If
rsSpec_numRows = 0
%>

[RFH Reseller: Wise Webs]

I think you need ? in the SQL not references to the fields as they come later in the parameter definitions... Items in red are what changes I think you need.

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
rsSpec__MMDiameter = "0"
If (Request.Form("txt_diameter")    <> "") Then 
  rsSpec__MMDiameter = Request.Form("txt_diameter")  
  strQuery = strQuery + " AND ? BETWEEN wp_ps_from AND wp_ps_to" 
End If
%>
<%
Dim rsSpec

[RFH Reseller: s80wkr]

Still get the same error i'm afraid

Thanks

[RFH Reseller: Wise Webs]

OK

Try this too. Set the last 2 parameters to be same datatype as the fields used in the BETWEEN statement. I have changed the code to show datatype SINGLE.

Other dataypes could be
adEmpty 0
adSmallInt 2
adInteger 3
adSingle 4
adDouble 5
adDecimal 14

If (Request.Form("txt_thickness")    <> "") Then
rsSpec_cmd.Parameters.Append rsSpec_cmd.CreateParameter("param3", 4, 1, , rsSpec__MMWall) ' adVarChar
End If
If (Request.Form("txt_diameter")    <> "") Then 
rsSpec_cmd.Parameters.Append rsSpec_cmd.CreateParameter("param4", 4, 1, , rsSpec__MMDiameter) ' adVarChar
End If