Results 1 to 2 of 2

Thread: Problem searching DB on web

  1. 08-22-2000, 01:13 PM #1
    Alanna Guest

    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&#34,"*&#34 then
    vContact = Replace(Request.Form("Contact&#34,"*","%&#34
    Else
    vContact = Request.Form("Contact&#34
    End If

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

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

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

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



    If Request.Form(&#34;Program&#34 <> &#34;&#34; then
    If Right(vProgram, 1) = &#34;*&#34; then
    sql1 = sql1 & &#34; Program LIKE &#39;&#34; & vProgram & &#34;&#39; AND &#34;
    Else
    sql1 = sql1 & &#34; Program = &#39;&#34; & vProgram & &#34;&#39; AND &#34;
    End if
    End If


    If Request.Form(&#34;Contact&#34 <> &#34;&#34; then
    If Right(vContact, 1) = &#34;*&#34; then
    sql1 = sql1 & &#34; Contact LIKE &#39;&#34; & vContact & &#34;&#39; AND &#34;
    Else
    sql1 = sql1 & &#34; Contact = &#39;&#34; & vContact & &#34;&#39; AND &#34;
    End if
    End If

    If Request.Form(&#34;City&#34 <> &#34;&#34; then
    If Right(vCity, 1) = &#34;*&#34; then
    sql1 = sql1 & &#34; City LIKE &#39;&#34; & vCity & &#34;&#39; AND &#34;
    Else
    sql1 = sql1 & &#34; City = &#39;&#34; & vCity & &#34;&#39; AND &#34;
    End if
    End If



    If Request.Form(&#34;State&#34 <> &#34;&#34; then
    If Right(vState, 1) = &#34;*&#34; then
    sql1 = sql1 & &#34; State LIKE &#39;&#34; & vState & &#34;&#39; AND &#34;
    Else
    sql1 = sql1 & &#34; State = &#39;&#34; & vState & &#34;&#39; AND &#34;
    End if
    End If

    sql1 = sql1 & &#34; 0 = 0 &#34;




    &#39;Create a recordset
    Set rs1 = Server.CreateObject(&#34;ADODB.RecordSet&#34

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

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

    <body bgcolor=&#34;#FFFFFF&#34;>
    <div align=&#34;left&#34;>

    <table border=&#34;0&#34; width=&#34;592&#34; height=&#34;37&#34;>
    <tr>
    <td width=&#34;112&#34; height=&#34;33&#34;><img src=&#34;../../images/12innigp.gif&#34;
    alt=&#34;12innigp.gif (1958 bytes)&#34; WIDTH=&#34;108&#34; HEIGHT=&#34;33&#34;></td>
    <td width=&#34;472&#34; height=&#34;33&#34;><p align=&#34;left&#34;><font face=&#34;Arial&#34; color=&#34;#004080&#34;><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>&nbsp;</p>

    <table border=&#34;1&#34; width=&#34;100%&#34;>
    <tr>
    <td valign=&#34;bottom&#34; width=&#34;40%&#34;><font face=&#34;Arial&#34; size=&#34;2&#34;><strong>Program</strong></font></td>
    <td valign=&#34;bottom&#34; width=&#34;10%&#34;><font face=&#34;Arial&#34; size=&#34;2&#34;><strong>Contact</strong></font></td>
    <td valign=&#34;bottom&#34; width=&#34;50%&#34;><font face=&#34;Arial&#34; size=&#34;2&#34;><strong>City</strong></font></td>
    <td valign=&#34;bottom&#34; width=&#34;10%&#34;><font face=&#34;Arial&#34; size=&#34;2&#34;><strong>State</strong></font></td>
    </tr>
    <% rs1.MoveFirst
    Do While NOT rs1.EOF %>
    <tr>
    <td width=&#34;40%&#34;><a href=&#34;BPDetailResult.asp?PID=<%= rs1(&#34;ID&#34 %>&#34;><font size=&#34;3&#34;><%= rs1(&#34;Program&#34 %></font></a></td>
    <td width=&#34;50%&#34;><font size=&#34;3&#34;><%= rs1(&#34;Contact&#34 %></font></td>
    <td width=&#34;50%&#34;><font size=&#34;3&#34;><%= rs1(&#34;City&#34 %></font></td>
    <td width=&#34;10%&#34;><font size=&#34;3&#34;><%= rs1(&#34;State&#34 %></font></td>
    </tr>
    <% rs1.MoveNext
    Loop %>
    </table>
    <% Else %>

    <h4><small><font face=&#34;Arial&#34;>No Records Found that Match this criteria.</font></small></h4>
    <% End If %>
    Reply With Quote Reply With Quote
  2. 08-26-2000, 06:35 PM #2
    John Guest

    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&#39;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 &#34;no records found&#34;. Code Below:

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

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

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

    If Instr(Request.Form(&#34;State&#34,&#34;*&#34 then
    vState = Replace(Request.Form(&#34;State&#34,&#34;*&#34;,&#34;%&#34
    Else
    vState = Request.Form(&#34;State&#34
    End If

    &#39;Build the sql search statement
    sql1 = &#34;SELECT * FROM Submissions WHERE &#34;



    If Request.Form(&#34;Program&#34 <> &#34;&#34; then
    If Right(vProgram, 1) = &#34;*&#34; then
    sql1 = sql1 & &#34; Program LIKE &#39;&#34; & vProgram & &#34;&#39; AND &#34;
    Else
    sql1 = sql1 & &#34; Program = &#39;&#34; & vProgram & &#34;&#39; AND &#34;
    End if
    End If


    If Request.Form(&#34;Contact&#34 <> &#34;&#34; then
    If Right(vContact, 1) = &#34;*&#34; then
    sql1 = sql1 & &#34; Contact LIKE &#39;&#34; & vContact & &#34;&#39; AND &#34;
    Else
    sql1 = sql1 & &#34; Contact = &#39;&#34; & vContact & &#34;&#39; AND &#34;
    End if
    End If

    If Request.Form(&#34;City&#34 <> &#34;&#34; then
    If Right(vCity, 1) = &#34;*&#34; then
    sql1 = sql1 & &#34; City LIKE &#39;&#34; & vCity & &#34;&#39; AND &#34;
    Else
    sql1 = sql1 & &#34; City = &#39;&#34; & vCity & &#34;&#39; AND &#34;
    End if
    End If



    If Request.Form(&#34;State&#34 <> &#34;&#34; then
    If Right(vState, 1) = &#34;*&#34; then
    sql1 = sql1 & &#34; State LIKE &#39;&#34; & vState & &#34;&#39; AND &#34;
    Else
    sql1 = sql1 & &#34; State = &#39;&#34; & vState & &#34;&#39; AND &#34;
    End if
    End If

    sql1 = sql1 & &#34; 0 = 0 &#34;




    &#39;Create a recordset
    Set rs1 = Server.CreateObject(&#34;ADODB.RecordSet&#34

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

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

    <body bgcolor=&#34;#FFFFFF&#34;>
    <div align=&#34;left&#34;>

    <table border=&#34;0&#34; width=&#34;592&#34; height=&#34;37&#34;>
    <tr>
    <td width=&#34;112&#34; height=&#34;33&#34;><img src=&#34;../../images/12innigp.gif&#34;
    alt=&#34;12innigp.gif (1958 bytes)&#34; WIDTH=&#34;108&#34; HEIGHT=&#34;33&#34;></td>
    <td width=&#34;472&#34; height=&#34;33&#34;><p align=&#34;left&#34;><font face=&#34;Arial&#34; color=&#34;#004080&#34;><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=&#34;1&#34; width=&#34;100%&#34;>
    <tr>
    <td valign=&#34;bottom&#34; width=&#34;40%&#34;><font face=&#34;Arial&#34; size=&#34;2&#34;><strong>Program</strong></font></td>
    <td valign=&#34;bottom&#34; width=&#34;10%&#34;><font face=&#34;Arial&#34; size=&#34;2&#34;><strong>Contact</strong></font></td>
    <td valign=&#34;bottom&#34; width=&#34;50%&#34;><font face=&#34;Arial&#34; size=&#34;2&#34;><strong>City</strong></font></td>
    <td valign=&#34;bottom&#34; width=&#34;10%&#34;><font face=&#34;Arial&#34; size=&#34;2&#34;><strong>State</strong></font></td>
    </tr>
    <% rs1.MoveFirst
    Do While NOT rs1.EOF %>
    <tr>
    <td width=&#34;40%&#34;><a href=&#34;BPDetailResult.asp?PID=<%= rs1(&#34;ID&#34 %>&#34;><font size=&#34;3&#34;><%= rs1(&#34;Program&#34 %></font></a></td>
    <td width=&#34;50%&#34;><font size=&#34;3&#34;><%= rs1(&#34;Contact&#34 %></font></td>
    <td width=&#34;50%&#34;><font size=&#34;3&#34;><%= rs1(&#34;City&#34 %></font></td>
    <td width=&#34;10%&#34;><font size=&#34;3&#34;><%= rs1(&#34;State&#34 %></font></td>
    </tr>
    <% rs1.MoveNext
    Loop %>
    </table>
    <% Else %>

    <h4><small><font face=&#34;Arial&#34;>No Records Found that Match this criteria.</font></small></h4>
    <% End If %>
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

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