Wrox Programmer Forums
|
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 8th, 2014, 04:33 AM
Registered User
 
Join Date: Sep 2014
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
Default Multiple ASP search not working

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

%>

Last edited by MikeFantana; September 8th, 2014 at 04:37 AM..
 
Old September 9th, 2014, 03:28 AM
Friend of Wrox
 
Join Date: Jan 2004
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

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.
__________________
Wind is your friend
Matt
 
Old September 9th, 2014, 06:50 AM
Registered User
 
Join Date: Sep 2014
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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
 
Old September 9th, 2014, 09:29 PM
Friend of Wrox
 
Join Date: Jan 2004
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

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
__________________
Wind is your friend
Matt

Last edited by mat41; September 9th, 2014 at 09:32 PM..
The Following User Says Thank You to mat41 For This Useful Post:
MikeFantana (September 14th, 2014)
 
Old September 14th, 2014, 03:38 PM
Registered User
 
Join Date: Sep 2014
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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 View Post
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
 
Old September 14th, 2014, 08:14 PM
Friend of Wrox
 
Join Date: Jan 2004
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

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
__________________
Wind is your friend
Matt





Similar Threads
Thread Thread Starter Forum Replies Last Post
Search with multiple menus forry SQL Language 0 November 5th, 2005 07:23 AM
search function not working locally Henry 8 BOOK: Beginning PHP, Apache, MySQL Web Development ISBN: 978-0-7645-5744-6 0 December 19th, 2004 12:31 PM
Multiple Joins in Multiple Table Search query pookster Access 4 September 23rd, 2004 03:04 PM
Multiple Keyword Search tuffour Classic ASP Databases 3 September 10th, 2004 06:12 AM
search for multiple words keph Beginning PHP 5 April 6th, 2004 01:23 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.