CodeAve.com/ASP - Multiple Form Selection (Where In)

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

Multiple Form Selection (Where In)

Allowing multiple choices of data observations enables people to customize views of your data and adds value to your applications. The following example utilizes our db on the career statistics of Hammerin’ Hank Greenberg. The years he played are available to select from in a dropdown menu. When multiple years are selected they are passed comma delimited. Utilizing the where in statement in our sql the comma delimited selections are appended to our sql statement to display only those years selected. Because we are looking for numeric values in our where in statement no modification to the comma delimited input is necessary. When character input is involved a replace statement can be used to put the appropriate quotation marks into your statement. Which is also provided in this example, but must be un-commented to be used.

•Text View

•Print View

•Mail this Link

• View the Data

<html>
<title>CodeAve.com(Multiple Form Selection (Where In) )</title>
<body bgcolor="#FFFFFF">
<%' Check to see if there is any input
' if not display the form for input
u_input=request.form("u_input")
if u_input = "" then %>

<form action="<%= request.servervariables("script_name")%>" method="post">
Select one, many, or all years to display<br>
<select size="5" name="u_input" multiple>
<option value="1930">1930</option>
<option value="1933">1933</option>
<option value="1934">1934</option>
<option value="1935">1935</option>
<option value="1936">1936</option>
<option value="1937">1937</option>
<option value="1938">1938</option>
<option value="1939">1939</option>
<option value="1940">1940</option>
<option value="1941">1941</option>
<option value="1942">1942</option>
<option value="1943">1943</option>
<option value="1944">1944</option>
<option value="1945">1945</option>
<option value="1946">1946</option>
<option value="1947">1947</option>
</select>
<input type="submit" value="Submit">
</form>

<%' When there is input display the data
else
' Name of the Access db
accessdb="greenberg"

' Connection to the db
cn="DRIVER={Microsoft Access Driver (*.mdb)};"
cn=cn & "DBQ=" & server.mappath(accessdb)

' Create a server record set object
set rs = server.createobject("ADODB.Recordset")

' Create an sql statement that will select all the table elements
' A single selection will simply query for that year
' Multiple selections will be passed
' already comma delimited. Which is fine for numerics
' However for text queries uncomment the two lines below
' beginging with u_input and uncomment the sql query for alpha

' For text queries u_input will need to be comma delimited and in single quotes
' We replace the commas with ',' and change the sql to include single quotes around u_input
' u_input="Detroit,Pittsburgh"
' u_input=replace(u_input,",","','")

' The sql statement is written to the browser
' to demonstrate this.
sql = "select * from hammerin_hank where year in("& u_input &")" 'sql for numerics

'sql = "select * from hammerin_hank where team in('"& u_input &"')" 'sql for alpha
' Execute the sql
rs.Open sql, cn
%>

<table border=3 align=center>
<caption><%= sql%></caption><tr>
<% ' Write out all the elements requested in the
' sql statement as table headers
for each element in rs.fields%>
<th><%= element.name %></th>
<% next
' End table headers %>
</tr>
<tr>
<% ' Write out all the values in the record
do while not rs.eof
for each element in rs.fields %>
<td align=right><%= rs(element.name) %></td>
<%
next
' end of record %>
</tr>
<% ' Move to the next record
rs.movenext
' Loop to the beginning
loop%>
</table>
<p>
<center>* In Military Service</center>
<% end if 'End check for user input %>
</body>
</html>