|
Search Database (SQL Where Form
examples)
In the previous page we introduced the WHERE clause, but now
we will see several examples of the WHERE clause in typical forms. This example allows
users to choose a city:
The form that they can enter a city in:
filename=/learn/test/SQLwhereform1.asp
<HEAD><TITLE>sqlwhereform1.asp</TITLE></HEAD>
<HTML><body bgcolor="#FFFFFF">
<Form action = "sqlwhereForm1respond.asp" method=GET>
Choose A State:<p>
State: <Input NAME="st" MaxLength="2" size="3"><P>
<Input type="submit" value="Get Data"> <Input type="reset" value="Clear State"></form>
</BODY></HTML>
The responder that deals with that form:
filename=/learn/test/SQLwhereform1respond.asp
<HEAD><TITLE>sqlwhereform1respond.asp</TITLE></HEAD>
<HTML><body bgcolor="#FFFFFF">
<%
myDSN="DSN=student;uid=student;pwd=magic"
mystate=request.querystring("st")
SQLtemp="select * from publishers where state='"
SQLtemp=SQLtemp & mystate & "'"
call query2table(SQLtemp,myDSN)
%>
<!--#include virtual="/learn/test/lib_dbtable.asp"-->
</BODY></HTML>
The library file lib_dbtable.asp looks like this:
filename=/learn/test/lib_dbtable.asp
<%
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=" "
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%>
|