CodeAve.com/ASP - Search db by Field

CodeAve.com - ASP - Database Table Display

ASP
·Read/Write
·Script Writers
·Database Display
·Response Objects
·Server Variables
·Random Events
·Articles
·Miscellaneous
·Contents
·What's New?
JavaScript
CSS
HTML
Maps
Links
Mail List
Search
Sitemap

Search db by Field

Providing search capabilities to a db enables users to query on whichever element you target them to. However allowing users to target which portion of the db they would like to query gives extra value. In this example, utilizing our db of US States (state, statename, and capital) the user can search in any of three fields (by dropdown menu input) rather than the application targeting one specific table element. Note: The db only has 50 observations in it, so it is better to search on single or double letter combinations.

•Text View

•Print View

•Mail this Link

• View the Data

<html>
<title>CodeAve.com(Search db by Field)</title>
<body bgcolor="#FFFFFF">
<%
u_input = trim(request.form("u_input")) 'u_input is the varible for user input
' the trim function removes any blanks around the the use input
u_field=request.form("u_field") 'the dropdown varible for user inputted search field

if u_input <> "" then ' If the user entered a value query the db
accessdb="state_info" ' name of the access db
table_name="states" ' name of the table within the access db
cn="driver={microsoft access driver (*.mdb)};"
cn=cn & "dbq=" & server.mappath(accessdb)
set rs = server.createobject("ADODB.Recordset")
sql = "select * from "& table_name &" where " & u_field & " like '%%" & u_input & "%%' "
response.write sql
rs.Open sql, cn

if rs.eof or rs.bof then
response.write "No results found..."
observations=0
else
observations=1
end if 'end check for observations
end if 'end check for user input
%>
<% 'If there are observations then display them
if observations > 0 then %>
<table>
<tr>
<% 'Write the field names
for each table_element in rs.fields %>
<td><b><%= table_element.name%></B></TD><%
next %>
</tr>
<tr>
<% 'Write the values
rs.movefirst
do while not rs.eof
for each cell in rs.fields %>
<td><%= cell.value %></td><%
next %>
</tr>
<% rs.movenext
loop %>
</table>
<% end if 'end of check of obs for display %>

<form action ="<%= request.servervariables("script_name") %>" method="post">
<input type="text" name="u_input" value="<%= u_input %>">
<select name="u_field" size="1">
<option <% ' write out all the search fields and select
if u_field = "state" or u_field = "" then
response.write "selected "
end if
%>value="state">State</option>
<option <%
if u_field = "statename" then
response.write "selected "
end if
%>value="statename">State Name</option>
<option <%
if u_field = "capital" then
response.write "selected "
end if
%>value="capital">Capital</option>
</select>
<input type="submit" value="Submit">
</form>