Problem searching DB on web

Hi Alanna,

You can probably replace the entire program below with three lines of code using ASP-db. It has a built-in search function that does everything you need including the ability to create custom drop-downs, sort by columns and even graph your data.

Check it out at www.aspdb.com.

If you're still determined to write everything by hand, head over to one of the ASP support forums on this site.

Thanks,
John

------------
Alanna at 8/22/00 2:13:07 PM

I have set up a search on our web site to search an Access DB. The DB connection is fine, I can retrieve all records, however, when I want to search using an asterisk, I get "no records found". Code Below:

'Get the search criteria from the search form (searchBP.asp)
If Instr(Request.Form("Contact"),"*") then
vContact = Replace(Request.Form("Contact"),"*&#34 ;,"%")
Else
vContact = Request.Form("Contact")
End If

If Instr(Request.Form("Program"),"%") then
vProgram = Replace(Request.Form("Program"),"*&#34 ;,"%")
Else
vProgram = Request.Form("Program")
End If

If Instr(Request.Form("City"),"*") then
vCity = Replace(Request.Form("City"),"*",& #34;%")
Else
vCity = Request.Form("City")
End If

If Instr(Request.Form("State"),"*") then
vState = Replace(Request.Form("State"),"*", "%")
Else
vState = Request.Form("State")
End If

'Build the sql search statement
sql1 = "SELECT * FROM Submissions WHERE "

If Request.Form("Program") <> "" then
If Right(vProgram, 1) = "*" then
sql1 = sql1 & " Program LIKE '" & vProgram & "' AND "
Else
sql1 = sql1 & " Program = '" & vProgram & "' AND "
End if
End If

If Request.Form("Contact") <> "" then
If Right(vContact, 1) = "*" then
sql1 = sql1 & " Contact LIKE '" & vContact & "' AND "
Else
sql1 = sql1 & " Contact = '" & vContact & "' AND "
End if
End If

If Request.Form("City") <> "" then
If Right(vCity, 1) = "*" then
sql1 = sql1 & " City LIKE '" & vCity & "' AND "
Else
sql1 = sql1 & " City = '" & vCity & "' AND "
End if
End If

If Request.Form("State") <> "" then
If Right(vState, 1) = "*" then
sql1 = sql1 & " State LIKE '" & vState & "' AND "
Else
sql1 = sql1 & " State = '" & vState & "' AND "
End if
End If

sql1 = sql1 & " 0 = 0 "

'Create a recordset
Set rs1 = Server.CreateObject("ADODB.RecordSet")

'Open the recordset with the sql statement that was constructed above
rs1.open sql1, conn, adOpenStatic
%>
<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML//EN">
<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta name="GENERATOR" content="Microsoft FrontPage 3.0">
<title>Best Practices Library Search Results</title>
</head>

<body bgcolor="#FFFFFF">
<div align="left">

<table border="0" width="592" height="37">
<tr>
<td width="112" height="33"><img src="../../images/12innigp.gif"
alt="12innigp.gif (1958 bytes)" WIDTH="108" HEIGHT="33"></td>
<td width="472" height="33">National
Institute of Governmental Purchasing 
Best Practices Library Search Results</td>
</tr>
</table>
</div><% If rs1.RecordCount > 0 then %>

 

<table border="1" width="100%">
<tr>
<td valign="bottom" width="40%">Program</td>
<td valign="bottom" width="10%">Contact</td>
<td valign="bottom" width="50%">City</td>
<td valign="bottom" width="10%">State</td>
</tr>
<% rs1.MoveFirst
Do While NOT rs1.EOF %>
<tr>
<td width="40%"><a href="BPDetailResult.asp?PID=<%= rs1("ID") %>"><%= rs1("Program") %></a></td>
<td width="50%"><%= rs1("Contact") %></td>
<td width="50%"><%= rs1("City") %></td>
<td width="10%"><%= rs1("State") %></td>
</tr>
<% rs1.MoveNext
Loop %>
</table>
<% Else %>

<h4>No Records Found that Match this criteria.</h4>
<% End If %>