Multiple ASP search not working

MikeFantana · September 8th, 2014, 04:33 AM

Hi,

I have created a classic ASP search and results page (Feeding from SQL DB) but cant get the search working. Problem is its bringing back everything not a defined result set .Any ideas?

Here is my search form

HTML Code:

 <form action="searchresults.asp" method="get" name="form" class="clearfix" id="form" novalidate >
                            <div class="col-lg-6 col-md-6 col-sm-6 col-xs-6">
                                <label for="CountyID">County</label>
								<select name="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">
                                    <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="#" class="btn btn-inverse btn-block" onclick="document.getElementById('form').submit(); return false;"><i class="fa fa-search"></i> SEARCH CONTRACTOR IN YOUR AREA</a>
                            </div>
						</form>

============

And here is the results page code

============

HTML Code:

<%
Dim MMColParam1
Dim MMColParam2
Dim MMColParam3
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

%>

mat41 · September 9th, 2014, 03:28 AM

Help others help you. There is very little effort made by you to tell us about your issue. This is not a place to get people to write code for you.

You need to be more specific including errors, line numbers, point out what line in your code the number refers to.

MikeFantana · September 9th, 2014, 06:50 AM

Hi,

Apologies. Newbie Alert!!!

OK so I have pinned it down to my SQL statement is the issue. It works if I select all 3 fields but if I select any other combination so I have played around with the code (to the best of my ability) and if you could help me anyway Id appreciate it. Basically my statement needs to be versitile so depending on what combination you choose it will bring back the right results...

This what im trying to.

HTML Code:

SELECT * FROM dbo.qrySearchResults where ((CountyID = ? OR CountyID IS NOT NULL) AND (ConTypeID = ? OR ConTypeID IS NOT NULL)) OR (ConBusName Like '') ORDER BY ConBusName ASC

String query = âSELECT * FROM dbo.qrySearchResults where(â

If select 1 then

                Query = Query + â(CountyID = ? OR CountyID IS NOT NULL)â

End if
 

If select 2 then

Query = Query + âAND (ConTypeID = ? OR ConTypeID IS NOT NULL))â

End if


If select 3 then

Query = query + OR (ConBusName Like '')

End if


Query = query + ORDER BY ConBusName ASC

mat41 · September 9th, 2014, 09:29 PM

Here is how I would do this:

Code:

dim sql
sql = "SELECT * FROM dbo.qrySearchResults WHERE 1=1 "
if trim(request.form("CountyID")) <> "" then
  sql = sql & " AND CountyID = " & trim(request.form("CountyID"))
end if
if trim(request.form("ConTypeID")) <> "" then
  sql = sql & " AND ConTypeID = " & trim(request.form("ConTypeID"))
end if
if trim(request.form("ConTypeID")) <> "" then
  'Only choose one of these.  the second will gve you more resutls using wildcard
  'sql = sql & " AND ConBusName LIKE '" & trim(request.form("ConTypeID")) & "' "
  sql = sql & " AND ConBusName LIKE '%" & trim(request.form("ConTypeID")) & "%' "
end if
sql = sql & " ORDER BY ConBusName;"

Note:
- I have placed WHERE 1=1 at the beginning of the SQL so all of the SQL inside each conditional statement can safely use AND without worrying about which get fired in what order etc...
- The conditional SQL assumes CountyID and ConTypeID are int datatypes in your DB. It assumes ConBusName is some kind of string
- Your code used this:
String query =
This would have never run, you can have spaces in variable names
- This:
Query = Query +
will not work either. you must use & to concatenate
- No need to use ASC, this is default sql ordering

MikeFantana · September 14th, 2014, 03:38 PM

Hi Mat41,

Thanks for the reply. Sorry only getting to action your code now. For starters the error messages have disappeared. Always a good start ;). I have printed your SQL and it comes back with the following

SELECT * FROM dbo.qrySearchResults WHERE 1=1 ORDER BY ConBusName

Now the only issue with this is its bringing back everything in the DB not the results from my form criteria. Should I be replacing that 1=1 with something else?

I would appreciate any help you can give.

Thanks

Quote:

Originally Posted by mat41

Here is how I would do this:

Code:

dim sql
sql = "SELECT * FROM dbo.qrySearchResults WHERE 1=1 "
if trim(request.form("CountyID")) <> "" then
  sql = sql & " AND CountyID = " & trim(request.form("CountyID"))
end if
if trim(request.form("ConTypeID")) <> "" then
  sql = sql & " AND ConTypeID = " & trim(request.form("ConTypeID"))
end if
if trim(request.form("ConTypeID")) <> "" then
  'Only choose one of these.  the second will gve you more resutls using wildcard
  'sql = sql & " AND ConBusName LIKE '" & trim(request.form("ConTypeID")) & "' "
  sql = sql & " AND ConBusName LIKE '%" & trim(request.form("ConTypeID")) & "%' "
end if
sql = sql & " ORDER BY ConBusName;"

Note:
- I have placed WHERE 1=1 at the beginning of the SQL so all of the SQL inside each conditional statement can safely use AND without worrying about which get fired in what order etc...
- The conditional SQL assumes CountyID and ConTypeID are int datatypes in your DB. It assumes ConBusName is some kind of string
- Your code used this:
String query =
This would have never run, you can have spaces in variable names
- This:
Query = Query +
will not work either. you must use & to concatenate
- No need to use ASC, this is default sql ordering

mat41 · September 14th, 2014, 08:14 PM

Hi there

;;;For starters the error messages have disappeared
I wasn't aware there were any errors. You have not mentioned any, or posted any.

if the SQL comes back with just this:

SELECT * FROM dbo.qrySearchResults WHERE 1=1 ORDER BY ConBusName

then none if the IF statements are firing. You need to check, are your form controls named correctly. For a start two of myn are named the same (bacasue I could not see a name for the third in your code) Surely you picked this up? The code I gave you had these three if's:

if trim(request.form("CountyID")) <> "" then
if trim(request.form("ConTypeID")) <> "" then
if trim(request.form("ConTypeID")) <> "" then

Name these in-line with the form controls that are being posted. If they dont fire use respose.writes just before the IF's to see what the values are. Of course they need to have values to fire...

;;;Should I be replacing that 1=1 with something else
No. I thought I explained why I would use these in this situation. Its only so you can start the conditional SQL with " AND ". You dont need to chnage this. 1 will always equal 1