-
Problem searching DB on web
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","*","%"
Else
vContact = Request.Form("Contact"
End If
If Instr(Request.Form("Program","%" then
vProgram = Replace(Request.Form("Program","*","%"
Else
vProgram = Request.Form("Program"
End If
If Instr(Request.Form("City","*" then
vCity = Replace(Request.Form("City","*","%"
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"><p align="left"><font face="Arial" color="#004080"><strong>National
Institute of Governmental Purchasing<small><small><br>
</small></small>Best Practices Library Search Results</strong></font></td>
</tr>
</table>
</div><% If rs1.RecordCount > 0 then %>
<p> </p>
<table border="1" width="100%">
<tr>
<td valign="bottom" width="40%"><font face="Arial" size="2"><strong>Program</strong></font></td>
<td valign="bottom" width="10%"><font face="Arial" size="2"><strong>Contact</strong></font></td>
<td valign="bottom" width="50%"><font face="Arial" size="2"><strong>City</strong></font></td>
<td valign="bottom" width="10%"><font face="Arial" size="2"><strong>State</strong></font></td>
</tr>
<% rs1.MoveFirst
Do While NOT rs1.EOF %>
<tr>
<td width="40%"><a href="BPDetailResult.asp?PID=<%= rs1("ID" %>"><font size="3"><%= rs1("Program" %></font></a></td>
<td width="50%"><font size="3"><%= rs1("Contact" %></font></td>
<td width="50%"><font size="3"><%= rs1("City" %></font></td>
<td width="10%"><font size="3"><%= rs1("State" %></font></td>
</tr>
<% rs1.MoveNext
Loop %>
</table>
<% Else %>
<h4><small><font face="Arial">No Records Found that Match this criteria.</font></small></h4>
<% End If %>
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|