2 Column Split Search Results from Access #2:

Code:

search.asp

<%option explicit%>

<%
dim conn, rs, rs2, strconn, strsql, strsql2

set conn = server.createobject("adodb.connection")
set rs = server.createobject("adodb.recordset")
set rs2 = server.createobject("adodb.recordset")
conn.open "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & server.mappath("test.mdb")

strsql = "select Name from test"
strsql2 = "select distinct Division from test"

rs.open strsql, conn, 2, 2
rs2.open strsql2, conn, 2, 2
%>

</head>
<body>

<%if request.querystring("Results") = "False" then%>
	<font color=#ff0000><b>Sorry, there are no matching records.</b></font>
<%end if%>

<table>
<tr>
<td class=font valign=middle><b>Name:</b> </td>
<td valign=middle>
<select name="Name">
<option value="">--Select a Name--</option>
<%if not rs.eof then
	do while not rs.eof%> 
		<option value="<%=rs("Name")%>"><%=rs("Name")%></option>
	<%rs.movenext
	loop
end if%>
</select>
</td>
</tr>

<td class=font valign=middle><b>Division:</b> </td>
<td valign=middle>
<select name="Division">
<option value="">--Select a Division--</option>
<%if not rs2.eof then
	do while not rs2.eof%> 
		<option value="<%=rs2("Division")%>"><%=rs2("Division")%></option>
	<%rs2.movenext
	loop
end if%>
</td>
</tr>

</form>

</font>

<%    
rs.close
rs2.close
conn.close 
set rs = nothing
set rs2 = nothing
set conn = nothing
%>

</body>
</html>

list.asp

Highlight

<%option explicit%>

<%
dim conn, rs, strconn, strsql, Count

set conn = server.createobject("adodb.connection")
set rs = server.createobject("adodb.recordset")
conn.cursorlocation = 3
conn.open "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & server.mappath("test.mdb")
strsql = "select * from test where"

If request.querystring("Name") <> "" Then
strsql = strsql & " Name = '" 		&_ 
request.querystring("Name") & "' and "
End If

If request.querystring("Division") <> "" Then
strsql = strsql & " Division = '" 	& _
request.querystring("Division") & "' and "
End If

strsql = left(strsql,(len(strsql) - 5))
rs.open strsql,conn, adopenforwardonly, adlockreadonly
%>

<%
if rs.eof then
	rs.close
	conn.close 
	set rs = nothing
	set conn = nothing
	response.redirect "search.asp?Results=False"
else

response.write "<table cellspacing=2 cellpadding=2 border=1>"

do until rs.eof
	%>
			<tr>
				<td><%=rs("Name")%> - <%=rs("Division")%></td>
		
			<%
			rs.movenext
			if rs.recordcount = 1 then
				' -- do nothing
			elseif rs.eof then
				response.write "<td> </td>"
			else%>
				<td><%=rs("Name")%> - <%=rs("Division")%></td>
			<%rs.movenext
			end if
			%>
			</tr>
	<%loop%>

</table>
<%
end if

rs.close
conn.close 
set rs = nothing
set conn = nothing
%>

</body>
</html>