Search Search

#1 worldwide
FREE Coding Lessons
since 1996
   THE BEST WAY to learn ASP & Asp.net!
Advertise Here!
click for details 		Credits Host:
DiscountASP.net 		Server Admin:
The "Team" 		Contact Info.
Charles M. Carroll
my Blog
[prev. Lesson]  SQL: Search Forms #1
     [next Lesson]  SQL: Search Forms #3

Search Database (SQL Where Form examples)

We will now present a form that allows people to choose a city but also supports inexact searches using LIKE are supported:

   filename=/learn/test/SQLwhereform2.asp

<Test Script Below>


<HEAD><TITLE>SQLwhereform2.asp</TITLE></HEAD>
<HTML><body bgcolor="#FFFFFF">
<Form action = "SQLwhereForm2respond.asp" method=GET>
Choose A City:<p>
City: <Input NAME="cy" MaxLength="20" size="23"><P>
<input TYPE="checkbox" NAME="ExactSearch" CHECKED>Exact Search<P>
* note if Exact Search is -> NOT CHECKED <-<br>You can use % as a wildcard<p>
<Input type="submit" value="Get Data">&nbsp;<Input type="reset" value="Clear City"></form>
</BODY></HTML>

   filename=/learn/test/SQLwhereform2respond.asp

<Test Script Below>


<HTML><HEAD>
<TITLE>sqlwhereform2respond.asp</TITLE></HEAD>
<body bgcolor="#FFFFFF">
<%
myDSN="DSN=student;uid=student;pwd=magic"
mycity=request.querystring("cy")
myexactsearch=request.querystring("exactsearch")
SQLtemp="select * from publishers where city"
If myexactsearch="on" then
    SQLtemp=SQLtemp & " ='"
Else
    SQLtemp=SQLtemp & " LIKE '"
End If
SQLtemp=SQLtemp & mycity & "'"
'response.write SQLtemp
call query2table(SQLtemp,myDSN)
%>
<!--#include virtual="/learn/test/lib_dbtable.asp"-->
</BODY></HTML>

The file lib_dbtable.asp looks like this:

   filename=/learn/test/lib_dbtable.asp

<Test Script Below>


<% 
sub query2table(inputquery, inputDSN)
    dim conntemp, rstemp
    set conntemp=server.createobject("adodb.connection")
    conntemp.open inputDSN
    set rstemp=conntemp.execute(inputquery)
    howmanyfields=rstemp.fields.count -1%>
    <table border=1><tr>
    <% 'Put Headings On The Table of Field Names
    for i=0 to howmanyfields %>
            <td><b><%=rstemp(i).name%></B></TD>
    <% next %>
    </tr>
    <% ' Now lets grab all the records
    do while not rstemp.eof %>
        <tr>
        <% for i = 0 to howmanyfields
            thisvalue=rstemp(i)
            If isnull(thisvalue) then
                thisvalue="&nbsp;"
            end if%>
            <td valign=top><%=thisvalue%></td>
        <% next %>
        </tr>
        <%rstemp.movenext
    loop%>
    </table>
    <%
    rstemp.close
    set rstemp=nothing
    conntemp.close
    set conntemp=nothing
end sub%>

There are many worthy charities!!. But perhaps help starving children in Africa or South America AND help Charles too. a $5 tip buys him lunch at McDonalds, a $20 tip buys his kid Hitoshi a new computer game, a $39 tip buys his daughter Michiko a few nice outfits. See our donor list.