Multiple ASP search not working

MikeFantana

Hi,

I have what i thought was a simple search on my website. But i cant get it to retrieve the correct details from my DB. Currently its pulling back everything, not the selection made. Any ideas?

Here is my search form Code

========
<form action="searchresults.asp" method="get" name="advanced_search" class="clearfix" id="advanced_search" novalidate>
<div class="col-lg-6 col-md-6 col-sm-6 col-xs-6">
<label for="CountyID">County</label>
<select id="CountyID" class="show-menu-arrow selectpicker">
<option value="%">--Please Select--</option>
<%
While (NOT rsCounty.EOF)
%>
<option value="<%=(rsCounty.Fields.Item("CountyID").Value)%>"><%=(rsCounty.Fields.Item("County").Value)%></option>
<%
rsCounty.MoveNext()
Wend
If (rsCounty.CursorType > 0) Then
rsCounty.MoveFirst
Else
rsCounty.Requery
End If
%>


</select>
</div>
<div class="col-lg-6 col-md-6 col-sm-6 col-xs-6">
<label for="ConTypeID">Service</label>
<select name="ConTypeID" class="show-menu-arrow selectpicker" id="location">
<option value="%">Please Select--</option>
<%
While (NOT rsConType.EOF)
%>
<option value="<%=(rsConType.Fields.Item("ConTypeID").Value)%>"><%=(rsConType.Fields.Item("ConTypeDesc").Value)%></option>
<%
rsConType.MoveNext()
Wend
If (rsConType.CursorType > 0) Then
rsConType.MoveFirst
Else
rsConType.Requery
End If
%>
</select>

</div>

<div class="col-lg-6 col-md-6 col-sm-6 col-xs-6">
<label for="type">Supplier Name</label>
<input type="text" size="15" name="ConBusName">
</div>


<div class="clearfix"></div>
<div class="col-lg-12 col-md-12 col-sm-12 col-xs-12">
<a href="Searchresults.asp" class="btn btn-inverse btn-block"><i class="fa fa-search"></i> </a>
</div>
</form>

==========
and here is my results page code
==========
<%
Dim MMColParam1
Dim MMColParam2
Dim MMColParam3
MMColParam1 = 0
If (Request.QueryString("Countyid") <> "" and CInt(Request.QueryString("countyid") > 0)) Then
MMColParam1 = Request.QueryString("countyid")
End If
MMColParam2 = 0
If (Request.QueryString("ConTypeID") <> "" and CInt(Request.QueryString("ConTypeID") > 0)) Then
MMColParam2 = Request.QueryString("ConTypeId")
End If
MMColParam3 = "%"
If (Request.QueryString("ConBusName") <> "") Then
MMColParam3 = "%" & Request.QueryString("ConBusName") & "%"
End If

%>
<%
Dim rsSearchRes
Dim rsSearchRes_cmd
Dim rsSearchRes_numRows

Set rsSearchRes_cmd = Server.CreateObject ("ADODB.Command")
rsSearchRes_cmd.ActiveConnection = MM_DBCONN_STRING
rsSearchRes_cmd.CommandText = "SELECT * FROM dbo.qrySearchResults where (CountyID LIKE ? AND ConTypeID LIKE ?) OR (ConBusName Like ?) ORDER BY ConBusName ASC"

rsSearchRes_cmd.Prepared = true

rsSearchRes_cmd.Parameters.Append rsSearchRes_cmd.CreateParameter("param1", 4, 1, -1, MMColParam1) ' adLongVarChar
rsSearchRes_cmd.Parameters.Append rsSearchRes_cmd.CreateParameter("param2", 4, 1, -1, MMColParam2) ' adLongVarChar
rsSearchRes_cmd.Parameters.Append rsSearchRes_cmd.CreateParameter("param3", 200, 1, 255, MMColParam3) ' adLongVarChar

Set rsSearchRes = rsSearchRes_cmd.Execute
rsSearchRes_numRows = 0
%>

amen

a few things

1: move your While(rs. ! EOF) code to a common function that just returns the values to populate the list box
2: Turn on SQL Server Profiler. Watch the call to the DB and take the SQL.
3: Run the SQL in SQL Server. If the SQL doesn't work there then you know the SQL construct in ASP is incorrect. Fix it
4: If it does work in SQL Server then you have something wrong with how the page process the results.

What exactly are you doing with rsSearchRes once you get results ?

MikeFantana

ran that trace but its not calling all the form selections from what i can make out

declare P1 int
set P1=1
exec sp_prepare P1 output, N'P1 real, P2 real,P3 varchar(255)', N'SELECT * FROM dbo.qrySearchResults where ((CountyID = P1) AND (ConTypeID = P2)) OR (ConBusName Like P3) ORDER BY ConBusName ASC', 1
select P1

It should have called it as follows
P1=10
P2=32
P3=GARDENING

I had to remove the at symbols, as this was been misread as links and Im only a newbie with under 50 posts so baords wouldnt let me add them

MikeFantana

Hi, Im wondering can anybody help me out here on this search issue.!!!!

colm_c

Looks like you're just not passing the parameters correctly.

<select id="CountyID" class="show-menu-arrow selectpicker">

You need to have a 'name' attribute which will be the parameter name, id and name are not the same thing.

Then here:

Request.QueryString("Countyid")

This should match the 'name' attribute (CountyID vs Countyid).

That was just a 30 second glance.

You should also learn how to debug something like this, a simple request/response debug might look like this:

#1 check the entire request object, does it contain all the parameters/required values?
#2 check the logic on the results page? are the if statements working?
#3 etc.

Process of elimination.

MikeFantana

Hi Colm_c
Are you saying I should change it e.g.

[HTML]<select id="ConTypeID" name="ConTypeID" class="show-menu-arrow selectpicker">.[/html]

Have checked and its calling through the correct parameters

[html]Param1 is 2 Param2 is 8 Param3 is %abc%[/html]

MikeFantana

Hi Colm_c
Are you saying I should change it e.g.

[HTML]<select id="CountyID" name="CountyID" class="show-menu-arrow selectpicker">.[/html]

Have checked and its calling through the correct parameters

[html]Param1 is 2 Param2 is 8 Param3 is %abc%[/html]