Checkbox Queries...

All Forums >> [Web Development] >> ASP and Database

Message

Bnugent -> Checkbox Queries... (6/4/2008 12:22:49)
First Page: Has query showing all open orders (column name SOPNUMBE)...Results are in a form that has checkbox for each record...Checkbox name is checkbox, value is SOPNUMBE Value...Which I post to second page... Second Page: Has query showing Sum of QTY of each item where SOPNUMBE = '::checkbox::'. Everything works great when I only submit one checkedbox from the first page...Problem arrives when I select two or more checkboxes from the first page, no results are shown...I dont get errors, it just doesnt find any results)... Thoughts???

Bnugent -> RE: Checkbox Queries... (6/4/2008 15:10:26)
Okay, I changed the coding afters browsing for hours through these forums... Here is what I changed my query to on the second page: SELECT SOP10200.ITEMNMBR, SOP10200.ITEMDESC, Sum(SOP10200.QUANTITY) AS SumOfQUANTITY FROM SOP10200 WHERE SOPNUMBE IN '::checkbox::' GROUP BY SOP10200.ITEMNMBR, SOP10200.ITEMDESC ORDER BY SOP10200.ITEMNMBR; Notice I changed from = and replaced to IN... Now I actually get an error when I check one or multiple boxes...here is the error: Database Results Error Description: Incorrect syntax near '00385 , 00386 '. Number: -2147217900 (0x80040E14) Source: Microsoft OLE DB Provider for SQL Server Any thoughts?

DesiMcK -> RE: Checkbox Queries... (6/4/2008 17:46:31)

This may help although I'm sure it's not the most efficient way to do it. I have a similiar problem and this code workd for me.

SQL = "SELECT Surname, Firstname, tblPupil.SENStage, tblPupil.SENBriefDescription"

If request.querystring("ks3mg") = "ON" then SQL = SQL & ", ks3mg" End if 
If request.querystring("Msat2") = "ON" then SQL = SQL & ", Msat2" End if 
If request.querystring("Esat2") = "ON" then SQL = SQL & ", Esat2" End if 
If request.querystring("Ssat2") = "ON" then SQL = SQL & ", Ssat2" End if 
If request.querystring("SAS7") = "ON" then SQL = SQL & ", SAS7" End if 
If request.querystring("NSAS7") = "ON" then SQL = SQL & ", NSAS7" End if 

SQL = SQL & " FROM tblPupil INNER JOIN tblBaseline ON tblPupil.PupilID = tblBaseline.PupilID "
SQL = SQL & "WHERE TutorGroup =  '" & Request("TutorGroup") & "' ORDER BY Surname ASC"

You could do something similar and build up the SQL.

Desi

pd_it_guy -> RE: Checkbox Queries... (6/4/2008 18:21:20)
Was just reading this.... checkboxes are individual form elements, and are either ON, or not ON, and each sends their current status as an individual variable on to the query processing page. Thus... if each record has a check box accompanying it, how are you uniquely naming them such that you know which one(s) are ON, and which are not. Was not clear. The query you need to build would have to take into account the status of every checkbox in order to return the records (or build sums) of you want. Can you also explain the function of the IN statement/ command. Was unable to locate any reference to that. There might be a simple way to accomplish what you want, but it's not clear from what appears so far.

Bnugent -> RE: Checkbox Queries... (6/4/2008 20:09:49)
again, i am passing the unique field of SOPNUMBE in the checkbox field... Everything is working GREAT, except if you submit more than one checkbox to the second page, it errors out... Spooky? rDouglass? BeTheBall? Bueller?

Spooky -> RE: Checkbox Queries... (6/4/2008 20:17:41)
Can you try a replace(input," ","") on the input? The database design is text?

Bnugent -> RE: Checkbox Queries... (6/5/2008 16:05:52)

Sorry Spooky, not sure what you mean or where to try that? I will keep researching, but in the meantime, can you look at my code and give me input on the INPUT suggestion?

Yes, SOPNUMBE is a text field.

FIRST PAGE CODE:

<!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM SOP10100 WHERE (SOPTYPE =  2) ORDER BY SOPNUMBE ASC"
fp_sDefault=""
fp_sNoRecords="<tr><td colspan=9 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="SBF02"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=9
fp_fCustomQuery=False
BOTID=2
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<tr>
            <td align="center">
            <p align="center">
            <input type="checkbox" name="checkbox" value="<%=FP_FieldVal(fp_rs,"SOPNUMBE")%>"></td>
            <td align="center">
            <font size="2">
<%=FP_FieldVal(fp_rs,"SOPNUMBE")%></font> </td>
            <td align="center">
            <font size="2">
<%=FP_FieldVal(fp_rs,"DOCDATE")%></font> </td>
            <td align="center">
            <font size="2">
<%=FP_FieldVal(fp_rs,"CSTPONBR")%></font> </td>
            <td align="center">
            <font size="2">
            <a href="cc_customer_summary.asp?CUSTNMBR=<%=FP_FieldURL(fp_rs,"CUSTNMBR")%>">
<%=FP_FieldVal(fp_rs,"CUSTNMBR")%></a></font> </td>
            <td align="center">
            <font size="2">
<%=FP_FieldVal(fp_rs,"CUSTNAME")%></font> </td>
            <td align="center">
            <font size="2">
<%=FP_FieldVal(fp_rs,"ADDRESS1")%></font> </td>
            <td align="center">
            <font size="2">
<%=FP_FieldVal(fp_rs,"CITY")%></font> </td>
            <td align="center">
            <font size="2">
<%=FP_FieldVal(fp_rs,"STATE")%></font> </td>
            <td align="center">
            <font size="2">
<%=FP_FieldVal(fp_rs,"ZIPCODE")%></font> </td>
          </tr>
<!--#include file="_fpclass/fpdbrgn2.inc"-->
</tbody>
      </table>
      <p><input type="submit" value="Submit" name="B1"><input type="reset" value="Reset" name="B2"></p>
    </form>

SECOND PAGE CODE:

<!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT SOP10200.ITEMNMBR, SOP10200.ITEMDESC, Sum(SOP10200.QUANTITY) AS SumOfQUANTITY FROM SOP10200 WHERE SOPNUMBE IN '::checkbox::' GROUP BY SOP10200.ITEMNMBR, SOP10200.ITEMDESC ORDER BY SOP10200.ITEMNMBR;"
fp_sDefault="checkbox="
fp_sNoRecords="<tr><td colspan=3 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="SBF02"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="ITEMNMBR"
fp_sMenuValue="ITEMNMBR"
fp_iDisplayCols=3
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<tr>
          <td>
<%=FP_FieldVal(fp_rs,"ITEMNMBR")%> </td>
          <td>
<%=FP_FieldVal(fp_rs,"ITEMDESC")%> </td>
          <td>
<%=FP_FieldVal(fp_rs,"SumOfQUANTITY")%> </td>
        </tr>
<!--#include file="_fpclass/fpdbrgn2.inc"-->
</tbody>
    </table>

pd_it_guy -> RE: Checkbox Queries... (6/5/2008 19:37:31)
There might be something here I am still missing, but what the problem seems to be is exactly how the contents of the string you call checkbox on the second page's SQL dynamically grows or shrinks, depending on what gets checked, or not, on the first page, so that the SQL IN clause can either find the value for a particular record, and include it in the results, or not. Seems like a step is missing that builds the content of that IN target variable "checkbox". If all the checkboxes are named identically, with just one checked the value would correctly be passed on and IN would work; if more than one tries to pass a variable with different values that might well be where the conflict is. If you could name the checkboxes differently, and on page 2 as others have suggested, use the individual results to construct the string that IN will work against it should work. Simple enough to check out.

BeTheBall -> RE: Checkbox Queries... (6/5/2008 19:58:29)

Try the Instr function:

fp_sQry="SELECT SOP10200.ITEMNMBR, SOP10200.ITEMDESC, Sum(SOP10200.QUANTITY) AS SumOfQUANTITY FROM SOP10200 WHERE Instr('::checkbox::',SOPNUMBE) > 0 GROUP BY SOP10200.ITEMNMBR, SOP10200.ITEMDESC ORDER BY SOP10200.ITEMNMBR;"

Bnugent -> RE: Checkbox Queries... (6/5/2008 20:26:55)
I feel brain dead and humbled on this one... Duane, I Tried your suggestion and got: Database Results Error Description: 'Instr' is not a recognized built-in function name. Number: -2147217900 (0x80040E14) Source: Microsoft OLE DB Provider for SQL Server Banging my head...

BeTheBall -> RE: Checkbox Queries... (6/5/2008 23:07:09)
Ahh. SQL Server. I assumed Access. Try this: fp_sQry="SELECT SOP10200.ITEMNMBR, SOP10200.ITEMDESC, Sum(SOP10200.QUANTITY) AS SumOfQUANTITY FROM SOP10200 WHERE CHARINDEX(SOPNUMBE,'::checkbox::') > 0 GROUP BY SOP10200.ITEMNMBR, SOP10200.ITEMDESC ORDER BY SOP10200.ITEMNMBR;"

Bnugent -> RE: Checkbox Queries... (6/6/2008 11:32:33)
WORKED LIKE A CHARM! But, can you explain the logic going on in the SQL that allowed it to work? Want to make sure I understand so when I encounter next time, all will work... While I wait for answer, I am going to search how to to a select all feature... thanks big time!

BeTheBall -> RE: Checkbox Queries... (6/6/2008 13:48:25)
Explanations cost extra. [;)] I actually have never used SQL Server, but CHARINDEX is the same as VB's Instr function. The way it works is this. The items in the parentheses in CHARINDEX(SOPNUMBE,'::checkbox::') are the string to look FOR and the string to look IN. CHARINDEX and Instr tell you a what character the string you are looking FOR begins in the string you are look IN. For example, if I had CHARINDEX('birth','Happy birthday'), the result would be 7. So, if the value of CHARINDEX is greater than zero, then you know the string you are looking FOR exists somewhere whithin the string you are looking IN. In your case, the string you are looking for is the value of the database field named SOPNUMBE. You are looking for that value within the string that is sent from your checkboxes. When you mark multiple checkboxes that all have the same name a comma-delimited string is created. For example, if you have 2 checkboxes named "color" and the values of the checkboxes are "red" and "blue". Marking both checkboxes and submitting sends a value of "red, blue". Does that help?

Page: [1]

9.472656E-02