asked on

Expand current search page to pass many parameters to a stored procedure

Currently, I have a search page that has ONE input box where a user enters either a PIN, name or address to search for. The page then passes the one parameter from the input box to a stored procedure and returns a list of matches. My data is farily messy, so this is not working for me. Instead, I'd like to modify my page to have several input boxes (one for PIN, one for first name, one for last name, one for street number and one for street name). I have developed a stored procedure to accept these paramters and done extensive testing and it is working exactly how I'd like. My problem is that I am now struggling with modifying my search page to have these 5 input boxes (instead of one), one submit button and then pass the 5 parameters (instead of one) to the new stored procedure on click on the submit button.

My new stored procedure: (the previous SP was identical, but only accepted one parameter...@strSearch)

CREATE PROCEDURE SearchData_proc
@strPIN varchar(10),
@strFirstName varchar(30),
@strLastName varchar(30),
@strHouseNum varchar(10),
@strStreet varchar(30)
as
SELECT first_name, last_name, parcel_number, housenum, street
from SearchView
WHERE
parcel_number = @strPIN or
first_name LIKE + '%' + @strFirstName +'%' and
last_name LIKE + '%' + @strLastName +'%' and
HouseNum LIKE + '%' + @strHouseNum +'%' and
street LIKE + '%' + @strStreet +'%'
GO

This is my current Search.asp page that needs to be modified:

<%
'check to make sure the user is logged in
If Session("blnValidUser") <> True Then Response.Redirect("Login.asp")

Dim strURL ' The URL of this page
Dim strConnect ' ADO connection
Dim rstSearch ' ADO recordset
Dim strDBPath
Dim SQL
Dim strSearch ' The text being searched for

strURL = Request.ServerVariables("URL")
strSearch = Request.QueryString("search")

%>
<p><font size=3><font color=#383798>Please enter a parcel number, name or address to search by:</p>
<form action="<%= strURL %>" method="get">
<input name="search" value="<%= strSearch %>" size="20" />
<input type="submit" value='Search' />
</form>
<p><font size=2><font color=#383798><u>Examples:</u><br>0101100001<br>Smith<br>
<p><font size=2><font color=#383798> Hint: if no results are returned, try minimizing your entry. <br>For example...if John Smith returns no results, try searching for Smith.

<%
If strSearch <> "" Then
strDBPath = Server.MapPath("database.mdb")

Set strConnect = Server.CreateObject("ADODB.Connection")
strConnect.Open "Provider=SQLOLEDB; DRIVER=SQLServer; SERVER=; UID=; PWD=; DATABASE=;"
'will change this to connection file when development is done

SQL = "exec searchdata_proc '" & Replace(strSearch, "'", "''") & "%' "
Set rstSearch = strConnect.Execute(SQL)
%>

<%
response.write("<form name='displayresult' method='POST' action='results.asp'>" & vbcrlf)
response.write("<select name='id'>" & vbcrlf)
if rstSearch.eof then
response.write("<option value=''>No Results</option>" & vbcrlf)
else
response.write("<option value=''><u>Parcel Number, Owner, Address</option></u>" & vbcrlf)
do while not rstSearch.eof
response.write(vbtab & "<option value='" & rstSearch("parcel_number") & "'>" & rstSearch("parcel_number") & " , " & rstSearch("last_name") & ", " &rstSearch("first_name") & " , " & rstSearch("house_number") & " " & rstSearch("house_number_suffix") & " " & rstSearch("prefix_directional") & " " & rstSearch("Street_name") & " " & rstSearch("street_suffix") & " " & rstSearch("post_directional") & ", " & rstSearch("community_name") & "</option>" & vbcrlf)
rstSearch.movenext
loop
end if
response.write("</select>" & vbcrlf)
response.write("<input type='submit' value='Display Results' />" & vbcrlf)
response.write("</form>" & vbcrlf)
%>

<%
      rstSearch.Close
      Set rstSearch = Nothing
      strConnect.Close
      Set strConnect = Nothing
End If

%>
</BODY>
</HTML>