Multiple Search cirteria - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Multiple Search cirteria

Author

Topic

Abernus
Starting Member

4 Posts

Posted - 2007-02-21 : 09:47:35

Hi all,

I am new(ish) to SQL and am having problems to what seems like an easy SQL query. Here is a sample of my database:

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

The SQL query I have (That doesn't work) is:

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;

Now the database has been created in Access 2000 and the above is done in Dreamweaver. I have created 2 pages one has the part where the user makes the search criteria selection and the other one is where the results form the database will be displayed.

The first page where the user makes the selection should work as follows:
The user has the option of 3 dropdowns. and they can select an entry in eithe rall 3 of them or any combination of 1 or 2 of them. This is to be passed to the second page and the users results displayed. The variable MMColParam is set to default at 1 unless changed via a selection from the user.

Here is an example of serach criteria and results
Users selects to see all entries where Primary_Profession=Monk

Displayed are only the entries where the Primary_Profession field= Monk, in this case ID 1

Another example:

Users selects to see all entries where Type_of_Build=Spike AND Where_Used=HA

Displayed are only the results where Type_of_Build=Spike AND Where_Used=HA, in this case ID 2,4

Please can anyone help me?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-21 : 09:53:51

Pass the parameter as NULL of no selection is made, or all options are selected.

SELECT	ID,
	Primary_Profession,
	Type_Of_Build,
	Where_Used
FROM	Builds
WHERE	ISNULL(@Type_Of_Build, Type_Of_Build) = Type_Of_Build
	AND ISNULL(@Where_Used, Where_Used) = Where_Used

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-02-21 : 10:57:56

Peso : That isn't going to find rows where the parameter, and column, are NULL - is it?

My preference for this type of query is:


SELECT	ID,
	Primary_Profession,
	Type_Of_Build,
	Where_Used
FROM	Builds
WHERE	    (@Type_Of_Build IS NULL OR Type_Of_Build = @Type_Of_Build)
	AND (@Where_Used IS NULL OR Where_Used = @Where_Used)

"Now the database has been created in Access 2000"

If the Data is in Access, rather than SQL Server, then I have no idea if this will work!

Kristen

Abernus
Starting Member

4 Posts

Posted - 2007-02-21 : 13:06:45

Kristen and Peso thanks for your very quick responces. I have been trying to figure this one out by myself for days now with no luck.

Peso I used your code and it worked a treat

I can't thank you enough. What a truly generous community and forum this is!

Kristen
Test

22859 Posts

Posted - 2007-02-21 : 13:52:59

"it worked a treat"

Is the NULL issue I raised not relevant then?

your definition:

"they can select an entry in eithe rall 3 of them or any combination of 1 or 2 of them"

sounded to be as thought it might!

Kristen

Abernus
Starting Member

4 Posts

Posted - 2007-02-22 : 05:54:47

O.K I might of been a bit quick with that reply, I think when I put the new SQL in I forgot to save it and ran my old SQL. So bottom line is I am still having problems.

When I get back from work tonight I will put up a full detailed post of errors and the full code, I hope this will be of some help to you :D

Thanks for your input so far

Abernus
Starting Member

4 Posts

Posted - 2007-02-22 : 15:55:41

Hi again

Her it is. I have taken screen grabs of everything as pics giove a better picture

of what's going on that words do. So these first 2 pics show my sample database, I had to split it as it was too long to fit on 1 pic.
[url]http://i170.photobucket.com/albums/u271/Abernus/Database1.jpg[/url]
[url]http://i170.photobucket.com/albums/u271/Abernus/Database2.jpg[/url]

Now this next pic is of the file Default.asp and shows the layout and Form settings from where the user can make the choice of what to search the database by:
[url]http://i170.photobucket.com/albums/u271/Abernus/Default.jpg[/url]

This is the full code behind it:

  <%@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 value="NULL">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 value="NULL">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 value="NULL">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>  </p>
<p> </p>
<p>  </p>

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

This is a screen shot of the file Test.asp. This is where the database is shown depending on what the user has selected. Also in the screen shot is the SQL setup etc.
[url]http://i170.photobucket.com/albums/u271/Abernus/Test.jpg[/url]

Here is the full code for this page:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/Builds.asp" -->
<%
Dim Recordset1__Where_Used
Recordset1__Where_Used = "1"
If (Request.Form("Where_Used")  <> "") Then 
  Recordset1__Where_Used = Request.Form("Where_Used") 
End If
%>
<%
Dim Recordset1__Primary_Profession
Recordset1__Primary_Profession = "1"
If (Request.Form("Primary_Profession")   <> "") Then 
  Recordset1__Primary_Profession = Request.Form("Primary_Profession")  
End If
%>
<%
Dim Recordset1__Type_of_Build
Recordset1__Type_of_Build = "1"
If (Request.Form("Type_of_Build")   <> "") Then 
  Recordset1__Type_of_Build = Request.Form("Type_of_Build")  
End If
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_Builds_STRING
Recordset1.Source = "SELECT ID,  " + Replace(Recordset1__Primary_Profession, "'", "''") + ",  " + Replace(Recordset1__Type_of_Build, "'", "''") + ",  " + Replace(Recordset1__Where_Used, "'", "''") + "  FROM Builds  WHERE ISNULL(@" + Replace(Recordset1__Type_of_Build, "'", "''") + ", " + Replace(Recordset1__Type_of_Build, "'", "''") + ") = " + Replace(Recordset1__Type_of_Build, "'", "''") + "  AND ISNULL(@" + Replace(Recordset1__Where_Used, "'", "''") + ", " + Replace(Recordset1__Where_Used, "'", "''") + ") = " + Replace(Recordset1__Where_Used, "'", "''") + ""
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
<style type="text/css">
<!--
.style2 {font-family: Verdana, Arial, Helvetica, sans-serif; font-weight: bold; font-size: 10px; }
-->
</style>
</head>

<body>
<table border="1">
  <tr>
    <td>ID</td>
    <td>Build_Name</td>
    <td>Primary_Profession</td>
    <td>Secondary_Profession</td>
    <td>Attribute1</td>
    <td>Value1</td>
    <td>Attribute2</td>
    <td>Value2</td>
    <td>Attribute3</td>
    <td>Value3</td>
    <td>Attribute4</td>
    <td>Value4</td>
    <td>Attribute5</td>
    <td>Value5</td>
    <td>Attribute6</td>
    <td>Value6</td>
    <td>Skill1</td>
    <td>Skill2</td>
    <td>Skill3</td>
    <td>Skill4</td>
    <td>Skill5</td>
    <td>Skill6</td>
    <td>Skill7</td>
    <td>Skill8</td>
    <td>Equipemen</td>
    <td>Runes</td>
    <td>Type_of_Build</td>
    <td>Where_Used</td>
  </tr>
  <% While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF)) %>
  <tr>
    <td><%=(Recordset1.Fields.Item("ID").Value)%></td>
    <td><%=(Recordset1.Fields.Item("Build_Name").Value)%></td>
    <td><%=(Recordset1.Fields.Item("Primary_Profession").Value)%></td>
    <td><%=(Recordset1.Fields.Item("Secondary_Profession").Value)%></td>
    <td><%=(Recordset1.Fields.Item("Attribute1").Value)%></td>
    <td><%=(Recordset1.Fields.Item("Value1").Value)%></td>
    <td><%=(Recordset1.Fields.Item("Attribute2").Value)%></td>
    <td><%=(Recordset1.Fields.Item("Value2").Value)%></td>
    <td><%=(Recordset1.Fields.Item("Attribute3").Value)%></td>
    <td><%=(Recordset1.Fields.Item("Value3").Value)%></td>
    <td><%=(Recordset1.Fields.Item("Attribute4").Value)%></td>
    <td><%=(Recordset1.Fields.Item("Value4").Value)%></td>
    <td><%=(Recordset1.Fields.Item("Attribute5").Value)%></td>
    <td><%=(Recordset1.Fields.Item("Value5").Value)%></td>
    <td><%=(Recordset1.Fields.Item("Attribute6").Value)%></td>
    <td><%=(Recordset1.Fields.Item("Value6").Value)%></td>
    <td><%=(Recordset1.Fields.Item("Skill1").Value)%></td>
    <td><%=(Recordset1.Fields.Item("Skill2").Value)%></td>
    <td><%=(Recordset1.Fields.Item("Skill3").Value)%></td>
    <td><%=(Recordset1.Fields.Item("Skill4").Value)%></td>
    <td><%=(Recordset1.Fields.Item("Skill5").Value)%></td>
    <td><%=(Recordset1.Fields.Item("Skill6").Value)%></td>
    <td><%=(Recordset1.Fields.Item("Skill7").Value)%></td>
    <td><%=(Recordset1.Fields.Item("Skill8").Value)%></td>
    <td><%=(Recordset1.Fields.Item("Equipement").Value)%></td>
    <td><%=(Recordset1.Fields.Item("Runes").Value)%></td>
    <td><%=(Recordset1.Fields.Item("Type_of_Build").Value)%></td>
    <td><%=(Recordset1.Fields.Item("Where_Used").Value)%></td>
  </tr>
  <% 
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  Recordset1.MoveNext()
Wend
%>
</table>
<p> </p>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>

Now when I run the above and select Spike asd the Type_of_Build and select HA form the Where_Used and submit this here is the output error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Wrong number of arguments used with function in query expression 'ISNULL(@Spike, Spike) = Spike AND ISNULL(@HA, HA) = HA'.
/latest/test.asp, line 34


Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1) 

Page:
POST 71 bytes to /latest/test.asp

POST Data:
Primary_Profession=NULL&Type_of_build=Spike&Where_Used=HA&Submit=Submit

I am not sure if all the above will be any help to anyone but there it is.

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2007-02-22 : 16:00:53

I think I just went blind

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-23 : 03:34:12

Now I finally understand why Bruce picked his alias...

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-02-23 : 08:02:45

You are not declaring any working variables - e.g. @Spike - so that approach won't work.

Previous advice was on the basis that you were using a Stored procedure, or a parameterized query, rather than dynamic SQL

If you are going to use dynamic SQL why don't you just construct a WHERE clause specific to which parameters the user has actually provided?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources