Passing multiple checkboxes from custom filter to 2nd ASP page.

[Ralph Asbury]

I'm wondering if there is a way to pass multiple checkbox items from a custom filter form to a second ASP program. It works correctly when only one checkbox is chosen but I get the following error when I check more than one checkbox:

Warning: User supplied query returns no records for display
SELECT [Building Name], [ADDRESS NUMBER] & ' ' & [ADDRESS NAME] AS Address, [Lease Signed], [Dark Fibre Ready] AS [Fibre Installed], [Bldg Service Ready] AS [POP Installed] FROM Buildings WHERE (((CITY)='Burnaby, Calgary&#39) ORDER BY [BUILDING NAME]

================================================


My Code below:

================================================
First page:

<%
Session(&#34;SesCity&#34 = &#34;&#34; &#39; This will get passed to the next program
%>

<CENTER>
<FORM method=&#34;GET&#34; action=&#34;../retail/prime_bldg_report.asp&#34;>
<TABLE border=&#34;0&#34; cellpadding=&#34;2&#34; cellspacing=&#34;1&#34;>
<TR>
<TD bgcolor=&#34;#FFCC00&#34;><FONT face=&#34;Arial&#34; size=&#34;2&#34;><B>Burnaby</B></FONT></TD>
<TD bgcolor=&#34;#FFCC00&#34;><INPUT type=&#34;checkbox&#34; name=&#34;City&#34; value=&#34;Burnaby&#34;></TD>
<TD rowspan=&#34;4&#34;>&nbsp;<INPUT type=&#34;image&#34; value=&#34;<IMG border=&#34; src=&#34;../images/prime_bldg_but.gif&#34; 0&#34; 0&#34; name=&#34;Display&#34; border=&#34;0&#34;></TD>
</TR>
<TR>
<TD bgcolor=&#34;#FFCC00&#34;><FONT face=&#34;Arial&#34; size=&#34;2&#34;><B>Calgary</B></FONT></TD>
<TD bgcolor=&#34;#FFCC00&#34;><INPUT type=&#34;checkbox&#34; name=&#34;City&#34; value=&#34;Calgary&#34;></TD>
</TR>
<TR>
<TD bgcolor=&#34;#FFCC00&#34;><FONT face=&#34;Arial&#34; size=&#34;2&#34;><B>Edmonton</B></FONT></TD>
<TD bgcolor=&#34;#FFCC00&#34;><INPUT type=&#34;checkbox&#34; name=&#34;City&#34; value=&#34;Edmonton&#34;></TD>
</TR>
<TR>
<TD bgcolor=&#34;#FFCC00&#34;><FONT face=&#34;Arial&#34; size=&#34;2&#34;><B>Vancouver</B></FONT></TD>
<TD bgcolor=&#34;#FFCC00&#34;><INPUT type=&#34;checkbox&#34; name=&#34;City&#34; value=&#34;Vancouver&#34;></TD>
</TR>
</TABLE>
</FORM>
</CENTER>

================================================== =

Second Page:

<%
W=Request(&#34;City&#34 &#39; Get what the user type in during the sql1 program

if (W <> &#34;&#34 then &#39; Is this the first time in? If so, set the session
Session(&#34;SesCity&#34 = W &#39; variable so the user&#39;s entry will be remembered.

LOCALSQL = Session(&#34;SesCity&#34&#39; + &#34;%&#34; &#39; Add the wildcard character.
%>

<P align=&#34;center&#34;><IMG border=&#34;0&#34; src=&#34;../images/prime_bldg.gif&#34;>

<BR>
<FONT face=&#34;Arial&#34; size=&#34;4&#34;>for <B><%=Session(&#34;SesCity&#34%></B></FONT>

<%
Set N = Server.CreateObject(&#34;ASP.db&#34
N.dbMDB = Server.MapPath(&#34;SERF 2000.mdb&#34
N.dbUnit = 14

if (W <> &#34;&#34 then N.dbReset(14) &#39; new parameters just came in.

sq = &#34;SELECT [Building Name], [ADDRESS NUMBER] & &#39; &#39; & [ADDRESS NAME] AS Address, [Lease Signed], [Dark Fibre Ready] AS [Fibre Installed], [Bldg Service Ready] AS [POP Installed] FROM Buildings WHERE (((CITY)=&#39;&#34; &#39; Build up the query string
sq = sq & LOCALSQL &#39; drop in the name the user typed in
sq = sq & &#34;&#39)&#34;
sq = sq & &#34; ORDER BY [BUILDING NAME]&#34;
N.dbSQL = sq &#39; Set the query definition

N.dbColor = &#34;white, 000066, #FFCC00, black, #EEEEEE&#34;

N.dbMode = &#34;Grid&#34;
N.dbNavigation = &#34;None&#34;
N.dbStatusBar = &#34;False&#34;
N.dbGridInc = &#34;600&#34;
N.dbGridColSort = False
N.dbGridTableTag = &#34;CellPadding=2 width=650&#34;

N.dbMagicCell = &#34;Address,align=right;Lease Signed,align=center;Fibre Installed,align=center;POP Installed,align=center&#34;
N.dbOptions = &#34;CellFontTag=Face=Arial Size=1&#34;
N.ASPdb

End If
%>

[Mark]

Ralph,

There is a programming example on our site that does EXACTLY that. See example 5g at http://www.aspdb.com/apps (complete source code is provided).
You need to parse out each choice individually in the 2nd program and use an &#34;OR&#34; in the WHERE clause.

Mark.

------------
Ralph Asbury at 12/20/00 6:26:16 PM

I&#39;m wondering if there is a way to pass multiple checkbox items from a custom filter form to a second ASP program. It works correctly when only one checkbox is chosen but I get the following error when I check more than one checkbox:

Warning: User supplied query returns no records for display
SELECT [Building Name], [ADDRESS NUMBER] & &#39; &#39; & [ADDRESS NAME] AS Address, [Lease Signed], [Dark Fibre Ready] AS [Fibre Installed], [Bldg Service Ready] AS [POP Installed] FROM Buildings WHERE (((CITY)=&#39;Burnaby, Calgary&#39) ORDER BY [BUILDING NAME]

================================================


My Code below:

================================================
First page:

<%
Session(&#34;SesCity&#34 = &#34;&#34; &#39; This will get passed to the next program
%>

<CENTER>
<FORM method=&#34;GET&#34; action=&#34;../retail/prime_bldg_report.asp&#34;>
<TABLE border=&#34;0&#34; cellpadding=&#34;2&#34; cellspacing=&#34;1&#34;>
<TR>
<TD bgcolor=&#34;#FFCC00&#34;><FONT face=&#34;Arial&#34; size=&#34;2&#34;><B>Burnaby</B></FONT></TD>
<TD bgcolor=&#34;#FFCC00&#34;><INPUT type=&#34;checkbox&#34; name=&#34;City&#34; value=&#34;Burnaby&#34;></TD>
<TD rowspan=&#34;4&#34;> <INPUT type=&#34;image&#34; value=&#34;<IMG border=&#34; src=&#34;../images/prime_bldg_but.gif&#34; 0&#34; 0&#34; name=&#34;Display&#34; border=&#34;0&#34;></TD>
</TR>
<TR>
<TD bgcolor=&#34;#FFCC00&#34;><FONT face=&#34;Arial&#34; size=&#34;2&#34;><B>Calgary</B></FONT></TD>
<TD bgcolor=&#34;#FFCC00&#34;><INPUT type=&#34;checkbox&#34; name=&#34;City&#34; value=&#34;Calgary&#34;></TD>
</TR>
<TR>
<TD bgcolor=&#34;#FFCC00&#34;><FONT face=&#34;Arial&#34; size=&#34;2&#34;><B>Edmonton</B></FONT></TD>
<TD bgcolor=&#34;#FFCC00&#34;><INPUT type=&#34;checkbox&#34; name=&#34;City&#34; value=&#34;Edmonton&#34;></TD>
</TR>
<TR>
<TD bgcolor=&#34;#FFCC00&#34;><FONT face=&#34;Arial&#34; size=&#34;2&#34;><B>Vancouver</B></FONT></TD>
<TD bgcolor=&#34;#FFCC00&#34;><INPUT type=&#34;checkbox&#34; name=&#34;City&#34; value=&#34;Vancouver&#34;></TD>
</TR>
</TABLE>
</FORM>
</CENTER>

================================================== =

Second Page:

<%
W=Request(&#34;City&#34 &#39; Get what the user type in during the sql1 program

if (W <> &#34;&#34 then &#39; Is this the first time in? If so, set the session
Session(&#34;SesCity&#34 = W &#39; variable so the user&#39;s entry will be remembered.

LOCALSQL = Session(&#34;SesCity&#34&#39; + &#34;%&#34; &#39; Add the wildcard character.
%>

<P align=&#34;center&#34;><IMG border=&#34;0&#34; src=&#34;../images/prime_bldg.gif&#34;>

<BR>
<FONT face=&#34;Arial&#34; size=&#34;4&#34;>for <B><%=Session(&#34;SesCity&#34%></B></FONT>

<%
Set N = Server.CreateObject(&#34;ASP.db&#34
N.dbMDB = Server.MapPath(&#34;SERF 2000.mdb&#34
N.dbUnit = 14

if (W <> &#34;&#34 then N.dbReset(14) &#39; new parameters just came in.

sq = &#34;SELECT [Building Name], [ADDRESS NUMBER] & &#39; &#39; & [ADDRESS NAME] AS Address, [Lease Signed], [Dark Fibre Ready] AS [Fibre Installed], [Bldg Service Ready] AS [POP Installed] FROM Buildings WHERE (((CITY)=&#39;&#34; &#39; Build up the query string
sq = sq & LOCALSQL &#39; drop in the name the user typed in
sq = sq & &#34;&#39)&#34;
sq = sq & &#34; ORDER BY [BUILDING NAME]&#34;
N.dbSQL = sq &#39; Set the query definition

N.dbColor = &#34;white, 000066, #FFCC00, black, #EEEEEE&#34;

N.dbMode = &#34;Grid&#34;
N.dbNavigation = &#34;None&#34;
N.dbStatusBar = &#34;False&#34;
N.dbGridInc = &#34;600&#34;
N.dbGridColSort = False
N.dbGridTableTag = &#34;CellPadding=2 width=650&#34;

N.dbMagicCell = &#34;Address,align=right;Lease Signed,align=center;Fibre Installed,align=center;POP Installed,align=center&#34;
N.dbOptions = &#34;CellFontTag=Face=Arial Size=1&#34;
N.ASPdb

End If
%>

[Mark]

TYPO!
Session(&#34;Cities&#34 = S &
should be:
Session(&#34;Cities&#34 = C &

Mark.

------------
Ralph Asbury at 12/21/00 2:18:05 PM

Thanks, Anita

Forgive my ignorance but could you explain what you mean by &#34;recreating the variable&#34;?


------------
Anita at 12/21/00 2:05:41 PM


It seems that your session variable &#34;city&#34; is not having any values. I just ran the same code and I exactly got the same error like yours. Then I found out that my variable is not getting initalized and I recreated the variable and now it works fine.
Anita.

------------
Ralph Asbury at 12/21/00 1:04:07 PM

Mark,

Thanks for the help... much appreciated. Unfortunately, I can&#39;t seem to get the program to work. Error message, output and code below:

================================================== ==========

OUTPUT:
=====================================
LOCALCities =, NumCities =1
City1 (City) =
Based on the information from the previous html form, read the database using:

N.dbSQL = SELECT [Building Name], [ADDRESS NUMBER] & &#39; &#39; & [ADDRESS NAME] AS Address, [Lease Signed], [Dark Fibre Ready] AS [Fibre Installed], [Bldg Service Ready] AS [POP Installed] FROM Buildings WHERE (CITY =&#39;&#39

ERROR MSSG:
=====================================
Warning: User supplied query returns no records for display
SELECT [Building Name], [ADDRESS NUMBER] & &#39; &#39; & [ADDRESS NAME] AS Address, [Lease Signed], [Dark Fibre Ready] AS [Fibre Installed], [Bldg Service Ready] AS [POP Installed] FROM Buildings WHERE (CITY =&#39;&#39

FIRST PAGE
=====================================

<%
Session(&#34;SesCity&#34 = &#34;&#34; &#39; This will get passed to the next program
%>

<CENTER>
<FORM method=&#34;POST&#34; action=&#34;../retail/prime_bldg_report.asp&#34;>
<TABLE border=&#34;0&#34; cellpadding=&#34;2&#34; cellspacing=&#34;1&#34;>
<TR>
<TD bgcolor=&#34;#FFCC00&#34;><FONT face=&#34;Arial&#34; size=&#34;2&#34;><B>Burnaby</B></FONT></TD>
<TD bgcolor=&#34;#FFCC00&#34;><INPUT type=&#34;checkbox&#34; name=&#34;City&#34; value=&#34;Burnaby&#34;></TD>
<TD rowspan=&#34;4&#34;> <INPUT type=&#34;image&#34; value=&#34;<IMG border=&#34; src=&#34;../images/prime_bldg_but.gif&#34; 0&#34; 0&#34; name=&#34;Display&#34; border=&#34;0&#34;></TD>
</TR>
<TR>
<TD bgcolor=&#34;#FFCC00&#34;><FONT face=&#34;Arial&#34; size=&#34;2&#34;><B>Calgary</B></FONT></TD>
<TD bgcolor=&#34;#FFCC00&#34;><INPUT type=&#34;checkbox&#34; name=&#34;City&#34; value=&#34;Calgary&#34;></TD>
</TR>
<TR>
<TD bgcolor=&#34;#FFCC00&#34;><FONT face=&#34;Arial&#34; size=&#34;2&#34;><B>Edmonton</B></FONT></TD>
<TD bgcolor=&#34;#FFCC00&#34;><INPUT type=&#34;checkbox&#34; name=&#34;City&#34; value=&#34;Edmonton&#34;></TD>
</TR>
<TR>
<TD bgcolor=&#34;#FFCC00&#34;><FONT face=&#34;Arial&#34; size=&#34;2&#34;><B>Vancouver</B></FONT></TD>
<TD bgcolor=&#34;#FFCC00&#34;><INPUT type=&#34;checkbox&#34; name=&#34;City&#34; value=&#34;Vancouver&#34;></TD>
</TR>
</TABLE>
</FORM>
</CENTER>

SECOND PAGE
=====================================

<%
C=Request(&#34;City&#34 &#39; Get what the user chose during the first program

if (C <> &#34;&#34 then &#39; Is this the first time in? If so, set the session
Session(&#34;Cities&#34 = S & &#34;,&#34; &#39; variable so the user&#39;s entry will be remembered.
end if

LOCALCities = Session(&#34;Cities&#34
Session(&#34;WantedCity1&#34 = &#34;&#34;
Session(&#34;WantedCity2&#34 = &#34;&#34;
Session(&#34;WantedCity3&#34 = &#34;&#34;
Session(&#34;WantedCity4&#34 = &#34;&#34;

ar = SPLIT(LOCALCities,&#34;,&#34

NumCities=ubound(ar)
Response.write(&#34;<BR>LOCALCities =&#34; & LOCALCities & &#34; NumCities =&#34; & NumCities & &#34;<BR>&#34

if (NumCities >= 1) then
City1 = ar(0)
end if
if (NumCities >= 2) then
City2 = ar(1)
end if
if (NumCities >= 3) then
City3 = ar(2)
end if
if (NumCities >= 4) then
City4 = ar(3)
end if

City1=Trim(City1)
City2=Trim(City2)
City3=Trim(City3)
City4=Trim(City4)
Session(&#34;WantedCity1&#34 = City1
Session(&#34;WantedCity2&#34 = City2
Session(&#34;WantedCity3&#34 = City3
Session(&#34;WantedCity4&#34 = City4

if (NumCities >= 1) then
response.write(&#34;City1 (City) = &#34; & City1 & &#34;<BR>&#34
end if
if (NumCities >= 2) then
response.write(&#34;City2 (City) = &#34; & City2 & &#34;<BR>&#34
end if
if (NumCities >= 3) then
response.write(&#34;City3 (City) = &#34; & City3 & &#34;<BR>&#34
end if
if (NumCities >= 4) then
response.write(&#34;City4 (City) = &#34; & City4 & &#34;<BR>&#34
end if

wanted1 = Session(&#34;WantedCity1&#34
wanted2 = Session(&#34;WantedCity2&#34
wanted3 = Session(&#34;WantedCity3&#34
wanted4 = Session(&#34;WantedCity4&#34

Set N = Server.CreateObject(&#34;ASP.db&#34
N.dbMDB = Server.MapPath(&#34;SERF 2000.mdb&#34
N.dbUnit = 14

if (Request(&#34;Reset&#34 = &#34;1&#34 then N.dbReset(14) &#39; new parameters just came in.

if NumCities=0 then
sq = &#34;SELECT [Building Name], [ADDRESS NUMBER] & &#39; &#39; & [ADDRESS NAME] AS Address, [Lease Signed], [Dark Fibre Ready] AS [Fibre Installed], [Bldg Service Ready] AS [POP Installed] FROM Buildings&#34;
else
sq = &#34;SELECT [Building Name], [ADDRESS NUMBER] & &#39; &#39; & [ADDRESS NAME] AS Address, [Lease Signed], [Dark Fibre Ready] AS [Fibre Installed], [Bldg Service Ready] AS [POP Installed] FROM Buildings WHERE (&#34;
for ii=1 to NumCities
tmp = TRIM(ar(ii-1))
sq = sq & &#34;CITY =&#39;&#34; & tmp & &#34;&#39;&#34;
if ii<NumCities then
sq = sq & &#34; OR &#34;
end if
next
sq = sq & &#34&#34;
end if

N.dbSQL = sq &#39; Set the query definition

response.write(&#34;Based on the information from the previous html form, &#34
response.write(&#34;read the database using: <P>&#34
response.write(&#34;<FONT SIZE=2><B> N.dbSQL = &#34; & sq & &#34;</B></FONT><P>&#34

&#39;sq = &#34;SELECT [Building Name], [ADDRESS NUMBER] & &#39; &#39; & [ADDRESS NAME] AS Address, [Lease Signed], [Dark Fibre Ready] AS [Fibre Installed], [Bldg Service Ready] AS [POP Installed] FROM Buildings WHERE (((CITY)=&#39;&#34; &#39; Build up the query string
&#39;sq = sq & LOCALSQL &#39; drop in the name the user typed in
&#39;sq = sq & &#34;&#39)&#34;
&#39;sq = sq & &#34; ORDER BY [BUILDING NAME]&#34;

N.dbColor = &#34;white, 000066, #FFCC00, black, #EEEEEE&#34;

N.dbMode = &#34;Grid&#34;
N.dbNavigation = &#34;None&#34;
N.dbStatusBar = &#34;False&#34;
N.dbGridInc = &#34;600&#34;
N.dbGridColSort = False
N.dbGridTableTag = &#34;CellPadding=2 width=650&#34;

N.dbMagicCell = &#34;Address,align=right;Lease Signed,align=center;Fibre Installed,align=center;POP Installed,align=center&#34;
N.dbOptions = &#34;CellFontTag=Face=Arial Size=1&#34;
N.ASPdb
%>




------------
Mark at 12/21/00 8:28:37 AM

Ralph,

There is a programming example on our site that does EXACTLY that. See example 5g at http://www.aspdb.com/apps (complete source code is provided).
You need to parse out each choice individually in the 2nd program and use an &#34;OR&#34; in the WHERE clause.

Mark.

[Ralph]

A big &#34;Homer&#34; DOH! on my part.

Thank you, Mark.