homepage Welcome to WebmasterWorld Guest from 54.211.231.221
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld

Home / Forums Index / Microsoft / Microsoft IIS Web Server and ASP.NET
Forum Library, Charter, Moderators: ocean10000

Microsoft IIS Web Server and ASP.NET Forum

    
SQL Multiple search criteria
SQL Multiple search criteria
Abernus




msg:3252460
 3:02 pm on Feb 14, 2007 (gmt 0)

Hi all,

I have been seraching for help high and low and have hit a brick wall. I used to do a little SQL etc back at Uni but have not touched it for years and now find myself needing it again.

I have written a database in Access 2000 called Build.mdb and am writing a web frontend for it in Dreamweaver. What I am trying to do is allow users to select a search criteria by dropdowns and then pull the data based on their selections.

The database at the moment only has a few rows in, here are the rows I am wanting to serach on (I have left out other none searchable data):

ID.....Primary_Profession....Type_of_Build....Where_Used
1......Monk.....................Support.............GvG
2......Elementalist.............Spike...............HA
3......Necromancer..............Spike...............PvP
4......Elementalist.............Spike...............HA

Now the first page has the following listboxes:
Primary_Profession - populated manually with the data
Type_of_Build - populated manually with the data
Where_Used - populated manually with the data

Here is the code for this page:

<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/Mythos1.asp" -->
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_Mythos1_STRING
Recordset1.Source = "SELECT * FROM Builds"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<style type="text/css">
<!--
.style2 {font-family: Verdana, Arial, Helvetica, sans-serif; font-weight: bold; font-size: 10px; }
-->
</style>

<form name="form1" method="post" action="test.asp">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="43%"><p align="center" class="style2">Primary Profession
<select name="Primary_Profession" id="Primary_Profession">
<option>Please Select</option>
<option value="Monk">Monk</option>
<option value="Necromancer">Necromancer</option>
<option value="Ranger">Ranger</option>
<option value="Elementalist">Elementalist</option>
</select>
</p>
</td>
<td width="34%">
<div align="center" class="style2">
Type Of Build
<select name="Type_of_build" id="Type_of_build">
<option>Please Select</option>
<option value="Support">Support</option>
<option value="Spike">Spike</option>
</select>
</div></td>
<td width="23%"><div align="center" class="style2">Where Used
<select name="Where_Used" id="Where_Used">
<option>Please Select</option>
<option value="GvG">GvG</option>
<option value="HA">HA</option>
<option value="PvP">PvP</option>
</select>
</div></td>
</tr>
</table>
<p align="center">
<input type="submit" name="Submit" value="Submit">
</p>
</form>
<p>&nbsp; </p>
<p>&nbsp;</p>
<p>&nbsp; </p>

<%
Recordset1.Close()
Set Recordset1 = Nothing
%>

Now the other page contains the database with the following SQL query:

SELECT *
FROM Builds
WHERE (((Builds.Primary_Profession)='MMColParam1') AND ((Builds.Type_of_Build)='MMColParam2') AND ((Builds.Where_Used)='MMColParam'))
ORDER BY Builds.Primary_Profession, Builds.Type_of_Build, Builds.Where_Used;

My Problem is that if the user selects a value in every list box on the first page they will get a result. But what I want it to be able to do is allow users to only select 1 or 2 values and still pull data bases on the 1 or 2 selections. What is actually does in this case at the moment is return no data.

Please can anyone help me and tell me what I am doing wrong in my SQL query?

P.S I hope this makes sence.

 

TheNige




msg:3252899
 11:20 pm on Feb 14, 2007 (gmt 0)

You'll probably have to build your SQL query conditionally based on if they selected values in each of the drop down lists. If they only selected 1 value then your query will only have the WHERE clause looking for that value. If it has 2 then only search for those two in your WHERE cluase.

Abernus




msg:3253141
 9:11 am on Feb 15, 2007 (gmt 0)

Thanks for the update, but still need help. I am not sure how to set out the SQL statement. Are you saying that it (logicaly) should look like this:

IF
MMColParam1=''
THEN
WHERE
......
ELSE IF
MMColParam1='' AND MMColParam2=''
THEN WHERE
.....

TheNige




msg:3253668
 8:30 pm on Feb 15, 2007 (gmt 0)

Yes.

Test your various queries in Access using the SQL view and you can tell what type of results you will get before coding anything in your GUI.

Abernus




msg:3253794
 10:25 pm on Feb 15, 2007 (gmt 0)

Thanks for putting me on the right track now it's off to the library to get books on SQL so I know what commands are used and whats not valid :D

Abernus




msg:3254557
 3:48 pm on Feb 16, 2007 (gmt 0)

O.K Back asking for more help again. Do I need to use Stored procedures (I have no idea what these are) I have been trying for the best part of 8 hours solid and I am still no further forward

This is the best I can come up with:

IF MMColParam=1 AND MMColParam2=1
THEN
SELECT *
FROM Builds
WHERE ((Builds.Primary_Profession)='MMColParam1')
ELSE
IF IF MMColParam1=1 AND MMColParam2=1
THEN
SELECT *
FROM Builds
WHERE ((Builds.Where_Used)='MMColParam')
ORDER BY Builds.Primary_Profession, Builds.Type_of_Build, Builds.Where_Used;

However I get the usuall very non helpfull message: "Please enter either a SELECT statement or a call to a stored procedurethat returns a recordset"

The default value of MMColParam is 1 so I figure if that value it returned it means that the use didn't make a selection on this variable on the previous page.

Please can someone tell me where I am going wrong and give an example please :D

Abernus




msg:3258637
 8:48 am on Feb 21, 2007 (gmt 0)

Hi peep's, I am still having problems with this. Can anyone give me any pointers as to what I am doing wrong, please.

Thanks

wrkalot




msg:3270144
 6:12 pm on Mar 3, 2007 (gmt 0)

here is a little snippet that "may" help. Should be easy enough to expand on.

If request.querystring("SearchType") = "" Then
response.write "what ever..."
end if
If request.querystring("SearchType") = "Product" Then
strSearch = Split(trim(request.querystring("search")), " ")
For i = 0 to (UBound(strSearch))
strQL = "SELECT * FROM Products WHERE Product LIKE '%" & strSearch(i) & "%'"
Next
strQL = strQL & "ORDER BY CDYear AND Artist DESC"
end if

If request.querystring("SearchType") = "Artist" Then
strSearch = Split(trim(request.querystring("search")), " ")
For i = 0 to (UBound(strSearch))
strQL = "SELECT * FROM Products WHERE Artist LIKE '%" & strSearch(i) & "%'"
Next
strQL = strQL & "ORDER BY CDYear AND Artist DESC"
end if

If request.querystring("SearchType") = "Tracks" Then
strSearch = Split(trim(request.querystring("search")), " ")
For i = 0 to (UBound(strSearch))
strQL = "SELECT * FROM Products WHERE Tracks LIKE '%" & strSearch(i) & "%'"
Next
strQL = strQL & "ORDER BY CDYear AND Artist DESC"
end if

aspdaddy




msg:3272963
 7:15 pm on Mar 6, 2007 (gmt 0)

Hers one way to build the search dynamically:

if request("txt1")<>"" then
strSearch=strSearch & " AND( col1='" & request("txt1") & ') "
end if

if request("txt2")<>"" then
strSearch=strSearch & " AND( col2='" & request("txt2") & ') "
end if

strSQL = "SELECT * FROM tbl WHERE 1=1 " & strSearch

wingnut




msg:3319058
 5:10 pm on Apr 23, 2007 (gmt 0)

This may help you though its been awhile since you posted!

<% Dim SQL

SQL = "WHERE "

'1st dropdown
If Request.QueryString("Primary_Profession") <> "" Then
SQL = SQL & "Primary_Profession = '" & Request.QueryString("Primary_Profession") & "' "
Else
SQL = SQL & "Primary_Profession = '%' "
End IF
' 2nd dropdown
If Request.QueryString("Type_of_build") <> "" Then
SQL = SQL & "AND Type_of_build = '" & Request.QueryString("Type_of_build") & "' "
End IF
' 3rd dropdown
If Request.QueryString("Where_Used") <> "" Then
SQL = SQL & "AND Where_Used = '" & Request.QueryString("Where_Used") & "' "
End IF

%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_Mythos1_STRING
Recordset1.Source = "SELECT * FROM Builds " & SQL & " ORDER BY Primary_Profession"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<style type="text/css">
<!--
.style2 {font-family: Verdana, Arial, Helvetica, sans-serif; font-weight: bold; font-size: 10px; }
-->
</style>

<form name="form1" method="get" action="test.asp">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="43%"><p align="center" class="style2">Primary Profession
<select name="Primary_Profession" id="Primary_Profession">
<option>Please Select</option>
<option value="Monk">Monk</option>
<option value="Necromancer">Necromancer</option>
<option value="Ranger">Ranger</option>
<option value="Elementalist">Elementalist</option>
</select>
</p>
</td>
<td width="34%">
<div align="center" class="style2">
Type Of Build
<select name="Type_of_build" id="Type_of_build">
<option>Please Select</option>
<option value="Support">Support</option>
<option value="Spike">Spike</option>
</select>
</div></td>
<td width="23%"><div align="center" class="style2">Where Used
<select name="Where_Used" id="Where_Used">
<option>Please Select</option>
<option value="GvG">GvG</option>
<option value="HA">HA</option>
<option value="PvP">PvP</option>
</select>
</div></td>
</tr>
</table>
<p align="center">
<input type="submit" name="Submit" value="Submit">
</p>
</form>
<p>&nbsp; </p>
<p>&nbsp;</p>
<p>&nbsp; </p>

<%
Recordset1.Close()
Set Recordset1 = Nothing
%>

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Microsoft / Microsoft IIS Web Server and ASP.NET
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved