Multiple select search results

MikeFantana

Hi,

I have a multiple select drop down menu that Im running a search from. If I select 1 item from the dropdown and hit submit, it brings back all the items associated with my selection, however if i select more than one item, it brings back no results. Anyone an idea what i should to fix this issue? here is my code, Im using asp vbscript:( and reading from a sql server db


page 1
[HTML]<form name="form1" method="get" action="searchres.asp">
<table width="50%" border="0">
<tr>
<td width="23%"> </td>
<td width="77%"> </td>
</tr>
<tr>
<td>Category</td>
<td><select name="CatID" size="10" multiple id="CatID">
<%
While (NOT rsCategory.EOF)
%>
<option value="<%=(rsCategory.Fields.Item("CatID").Value)%>"><%=(rsCategory.Fields.Item("Category").Value)%></option>
<%
rsCategory.MoveNext()
Wend
If (rsCategory.CursorType > 0) Then
rsCategory.MoveFirst
Else
rsCategory.Requery
End If
%>[/HTML]


===============
and results page

[HTML]<%

Dim rsSearchResults__MMColParam1
rsSearchResults__MMColParam1 = "%"
If (Request.QueryString("CatID") <> "") Then
rsSearchResults__MMColParam1 = Request.QueryString("Catid")
End If
%>


<%
Dim rsSearchResults
Dim rsSearchResults_cmd
Dim rsSearchResults_numRows

Set rsSearchResults_cmd = Server.CreateObject ("ADODB.Command")
rsSearchResults_cmd.ActiveConnection = MM_DBCCONNECTION_STRING
rsSearchResults_cmd.CommandText = "SELECT * FROM dbo.qryALLClients where CatID LIKE ? order by contact_name ASC"
rsSearchResults_cmd.Prepared = true
rsSearchResults_cmd.Parameters.Append rsSearchResults_cmd.CreateParameter("param1", 201, 1, 50, rsSearchResults__MMColParam1) ' adLongVarChar


Set rsSearchResults = rsSearchResults_cmd.Execute
rsSearchResults_numRows = 0
%>


<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = 2000
Repeat1__index = 0
rsSearchResults_numRows = rsSearchResults_numRows + Repeat1__numRows
%>[/HTML]

BrokenArrows

I think its your select query. I dont do asp vbscript so you may need to fill in the blanks.

SELECT * FROM dbo.qryALLClients where CatID LIKE ? order by contact_name ASC

I assume the ? mark gets replaced with the selections you choose on the previous page.

So when you choose one option it becomes something like:

SELECT * FROM dbo.qryALLClients where CatID LIKE "choiceone" order by contact_name ASC

When you choose more than one option it becomes:

SELECT * FROM dbo.qryALLClients where CatID LIKE "choiceonechoice2" order by contact_name ASC

You need it to be:

SELECT * FROM dbo.qryALLClients where CatID LIKE "choice one" or CatID like "choice two" order by contact_name ASC

Or a cleaner way might be:

SELECT * FROM dbo.qryALLClients where CatID in ("choice one", "choice two") order by contact_name ASC

amen

The above but a few pointers.

In general using Select * is not a good idea. Better to just select the columns you need.

Using OR can be performance intensive on your db. Depending on what you are doing you may better off writing single SQL statements and using UNION.

MikeFantana

Hi All,

OK I have tried all your suggestions but its still not working for me

Here is my sad code again. Any other suggestions?????

<%

Dim rsSearchResults__MMColParam1
rsSearchResults__MMColParam1 = "%"
If (Request.QueryString("CatID") <> "") Then
rsSearchResults__MMColParam1 = Request.QueryString("Catid")
End If
%>


<%
Dim rsSearchResults
Dim rsSearchResults_cmd
Dim rsSearchResults_numRows

Set rsSearchResults_cmd = Server.CreateObject ("ADODB.Command")
rsSearchResults_cmd.ActiveConnection = MM_DB_STRING
rsSearchResults_cmd.CommandText = "SELECT * FROM dbo.qryALLClients where CatID LIKE ? order by contact_name ASC"
rsSearchResults_cmd.Prepared = true
rsSearchResults_cmd.Parameters.Append rsSearchResults_cmd.CreateParameter("param1", 201, 1, 50, rsSearchResults__MMColParam1) ' adLongVarChar


Set rsSearchResults = rsSearchResults_cmd.Execute
rsSearchResults_numRows = 0
%>

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



declare @P1 int
set @P1=1
exec sp_prepare @P1 output, N'@P1 text', N'SELECT * FROM dbo.qryALLClients where CatID LIKE @P1 order by contact_name ASC', 1
select @P1

exec sp_execute 1, '%'

==========