record sorting through checkbox values - need help
jaywalk101 | Posted 3:04pm 22. May 2008 Server Time |
Hey guys its been a while since I last posted. I'm glad this sites still around. I need some help with an asp project i'm working on. I'm trying to let a user be able to sort records
by different fields using checkboxes. I'm kind of on the right track, but I'm missing something. Possibly an array, not sure.. here's my code though.. thanks for any help in advance!!! I will continue to research.. .
**********************************************************
HTML
<form action="results1.asp" method="post" name="form" align="center">
<p><i><font face="Times New Roman" size="7">Provider
Database</font></i></p>
<p align="center"><b>County:</b></p>
<div align="center">
<center>
<p>Pasquotank<input type="checkbox" name="pasquotank" value="pasquotank"></p>
<p>Camden<input type="checkbox" name="camden" value="camden"></p>
<p><b>Services:</b></p>
<p> CSA <input type="checkbox" name="csa" value="csa"></p>
<p>CSC <input type="checkbox" name="csc" value="csc"></p>
<p><input type="submit" value="Submit" name="B1"></p>
<p> </p>
</center>
</div>
</form>
******************************************************
ASP and VBscript
<%
Dim SqlForm
Set DbSearch = Server.CreateObject("ADODB.Connection")
DbSearch.Open = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("providers.mdb")
SqlForm = "SELECT * FROM providers WHERE"
If Request.Form("pasquotank") = "pasquotank" Then
SqlForm = SqlForm & " county='pasquotank' or "
Else If Request.Form("pasquotank") = "" Then
End If
End IF
If Request.Form("camden") = "camden" Then
SqlForm = SqlForm & " county='camden' "
Else If Request.Form("camden") = "" Then
End If
End IF
<%
|
WizzKidd | Posted 1:02pm 12. June 2008 Server Time |
Try this:
<%
Dim SqlForm, bSqlWhere
Set DbSearch = Server.CreateObject("ADODB.Connection")
DbSearch.Open = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("providers.mdb")
SqlForm = "SELECT * FROM providers"
bSqlWhere = False
If Request.Form("pasquotank") = "pasquotank" Then
If bSqlWhere Then
SqlForm = SqlForm & " OR "
Else
SqlForm = SqlForm & " WHERE "
bSqlWhere = True
End If
SqlForm = SqlForm & "county='pasquotank'"
End If
If Request.Form("camden") = "camden" Then
If bSqlWhere Then
SqlForm = SqlForm & " OR "
Else
SqlForm = SqlForm & " WHERE "
bSqlWhere = True
End If
SqlForm = SqlForm & "county='camden'"
End If
'execute the query (SqlForm) down here
%>
I've written it in a way so that its expandable too, have a look:
Set DbSearch = Server.CreateObject("ADODB.Connection")
DbSearch.Open = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("providers.mdb")
SqlForm = "SELECT * FROM providers"
bSqlWhere = False
If Request.Form("pasquotank") = "pasquotank" Then
If bSqlWhere Then
SqlForm = SqlForm & " OR "
Else
SqlForm = SqlForm & " WHERE "
bSqlWhere = True
End If
SqlForm = SqlForm & "county='pasquotank'"
End If
If Request.Form("camden") = "camden" Then
If bSqlWhere Then
SqlForm = SqlForm & " OR "
Else
SqlForm = SqlForm & " WHERE "
bSqlWhere = True
End If
SqlForm = SqlForm & "county='camden'"
End If
'add another checkbox to see if the apples checkbox was selected
If Request.Form("apples") = "Checked" Then
If bSqlWhere Then
SqlForm = SqlForm & " OR "
Else
SqlForm = SqlForm & " WHERE "
bSqlWhere = True
End If
SqlForm = SqlForm & "fruit='apples'"
End If
'add another checkbox to see if the red checkbox was selected
If Request.Form("red") = "Checked" Then
If bSqlWhere Then
SqlForm = SqlForm & " OR "
Else
SqlForm = SqlForm & " WHERE "
bSqlWhere = True
End If
SqlForm = SqlForm & "colour='red'"
End If
'and so on
'...
'...
'...
'execute the query (SqlForm) down here
%>
- Neil-One (aka WizzKidd)
- http://www.promotioncity.co.uk
WizzKidd | Posted 1:04pm 12. June 2008 Server Time |
In fact, if you could throw in some AND clauses as well, and all that'd involve is creating an bSqlAnd boolean used in the same way as the bSqlWhere boolean variable. :)
Reply to Post record sorting through checkbox values - need help
|
|
|
