-
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 %>
-
Problem searching DB on web (reply)
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","*","%"
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
|
|