Home > Archive > ASP > June 2007 > Using For ... Loop to retrieve records....
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Using For ... Loop to retrieve records....
|
|
|
| Hi,
I have a form on which a user can select a checkbox against a record.
Each checkbox carries the RecordID of a product.
----------------------------------------------------------------
I am trying to print the following report:-
Details for product 1
--------- Report / page Break ---------------
Details for Product 6
--------- Report / page Break ---------------
or for whatever products were selected.
Before the report is printed, the form submits to the next page with
some code as follows:
For i = 1 to Request.Form("printme").Count (where printme is the
name of the checkbox)
fieldName = Request.Form.Key(i)
fieldValue = Request.Form.Item(i)
if Request.Form.key(i) <> "Submit" then (Ignore Submit button)
strquery = ""
strquery = "SELECT * FROM reports"
strquery = strquery & " WHERE ((reports.ReportID='" & fieldValue &
"'));" (equals first selected record)
Set RS = adoDataConn.Execute(strquery)
Write out report for first ID retrieved in fieldValue
else
end if
Next
-------------------------------------------------
The problem is I cannot get my SQL code to pickup the next ID from the
For ... Loop
Can you help
Thanks
David
| |
| Bob Barrows [MVP] 2007-06-19, 6:56 pm |
| David wrote:
> Hi,
>
> I have a form on which a user can select a checkbox against a record.
> Each checkbox carries the RecordID of a product.
>
> ----------------------------------------------------------------
>
> I am trying to print the following report:-
>
> Details for product 1
>
> --------- Report / page Break ---------------
>
> Details for Product 6
>
> --------- Report / page Break ---------------
>
> or for whatever products were selected.
>
> Before the report is printed, the form submits to the next page with
> some code as follows:
>
> For i = 1 to Request.Form("printme").Count (where printme is the
> name of the checkbox)
>
>
> fieldName = Request.Form.Key(i)
> fieldValue = Request.Form.Item(i)
>
> if Request.Form.key(i) <> "Submit" then (Ignore Submit button)
>
> strquery = ""
> strquery = "SELECT * FROM reports"
> strquery = strquery & " WHERE ((reports.ReportID='" & fieldValue &
> "'));" (equals first selected record)
>
>
> Set RS = adoDataConn.Execute(strquery)
>
>
> Write out report for first ID retrieved in fieldValue
>
> else
> end if
> Next
>
> -------------------------------------------------
>
> The problem is I cannot get my SQL code to pickup the next ID from the
> For ... Loop
>
First step: verify that Request.Form("printme") contains what you think
it contains:
Response.Write Request.Form("printme") & "<BR>"
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
| |
|
| On 19 Jun, 15:02, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
wrote:
> David wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> First step: verify that Request.Form("printme") contains what you think
> it contains:
>
> Response.Write Request.Form("printme") & "<BR>"
-------------------------------------------------------------------------------------------
It returns the correct record IDs > 2533, 2567, 2568, 2569
i.e. I just selected these 4 records.
| |
|
| On 19 Jun, 15:17, David <davidgor...@scene-double.co.uk> wrote:
> On 19 Jun, 15:02, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> -------------------------------------------------------------------------------------------
>
> It returns the correct record IDs > 2533, 2567, 2568, 2569
> i.e. I just selected these 4 records.
--------------------------------------------------------------------------------------------------------
ok, now I've got a little further, If I select 1 checkbox, it prints 1
report, if I select 2 records, it prints 2 reports, but if I select 3
records it throws this error at the bottom of the page:
Request object error 'ASP 0105 : 80004005'
Index out of range
/Repair_Reports/index_Report_Multi.asp, line 16
An array index is out of range.
My current code is:
-------------------------------------------------------------------
For i = 1 to Request.Form("printme").Count
fieldName = Request.Form.Key(i)
fieldValue = Request.Form.Item(i)
if Request.Form.Item(i) <> "Submit" then
strquery = "SELECT * FROM reports"
strquery = strquery & " WHERE ((reports.ReportID='" &
Request.Form("printme").item(i) & "'));"
Set RS = adoDataConn.Execute(strquery)
Do while NOT RS.EOF
print records etc < xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx >
RS.Movenext
Loop
else
end if
Next
--------------------------------------------------------------------
As mentioned above, this works perfectly for 1 or 2 selected records,
bu crashes on 3 or more ........... can you explain this please ???
Thanks
David
| |
|
| On 19 Jun, 15:35, David <davidgor...@scene-double.co.uk> wrote:
> On 19 Jun, 15:17, David <davidgor...@scene-double.co.uk> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> --------------------------------------------------------------------------------------------------------
>
> ok, now I've got a little further, If I select 1 checkbox, it prints 1
> report, if I select 2 records, it prints 2 reports, but if I select 3
> records it throws this error at the bottom of the page:
>
> Request object error 'ASP 0105 : 80004005'
> Index out of range
> /Repair_Reports/index_Report_Multi.asp, line 16
> An array index is out of range.
>
> My current code is:
>
> -------------------------------------------------------------------
>
> For i = 1 to Request.Form("printme").Count
>
> fieldName = Request.Form.Key(i)
> fieldValue = Request.Form.Item(i)
>
> if Request.Form.Item(i) <> "Submit" then
>
> strquery = "SELECT * FROM reports"
> strquery = strquery & " WHERE ((reports.ReportID='" &
> Request.Form("printme").item(i) & "'));"
>
> Set RS = adoDataConn.Execute(strquery)
>
> Do while NOT RS.EOF
>
> print records etc < xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx >
>
> RS.Movenext
>
> Loop
>
> else
> end if
>
> Next
>
> --------------------------------------------------------------------
>
> As mentioned above, this works perfectly for 1 or 2 selected records,
> bu crashes on 3 or more ........... can you explain this please ???
>
> Thanks
>
> David
------------------------------------------------------------------------------------------------------------------
Oh, before you ask, Line 16 in the error message is for:-
fieldName = Request.Form.Key(i)
------------------------------------------------------------------------------------------------------------------
| |
| Bob Barrows [MVP] 2007-06-19, 6:56 pm |
| David wrote:
>
> ----------------------------------------------------------------------
---------------------
>
> It returns the correct record IDs > 2533, 2567, 2568, 2569
> i.e. I just selected these 4 records.
OK, time to look closer at your code ... I missed this:
> For i = 1 to Request.Form("printme").Count (where printme is the
> name of the checkbox)
>
The index is 0-based: you should be looping from 0 to
Request.Form("printme").Count - 1
OK, next step is to verify the Count property is returning what you
think it should:
Response.Write Request.Form("printme").Count & "<BR>"
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
| |
|
| On 19 Jun, 16:47, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
wrote:
> David wrote:
>
>
>
>
>
> ---------------------
>
>
>
>
> OK, time to look closer at your code ... I missed this:
>
>
> The index is 0-based: you should be looping from 0 to
> Request.Form("printme").Count - 1
>
> OK, next step is to verify the Count property is returning what you
> think it should:
> Response.Write Request.Form("printme").Count & "<BR>"
>
-----------------------------------------------------------------------------------------------------
It prints the correct number of records, i.e. 1, 2 or 3 etc, but if I
select just 1 record, I get a blank screen, if I select 2 records, I
get 1 report printed.
This is what happens if I add '-1' to the count property, otherwise,
if I leave off the '-1' it works ok, apart from when 3 records are
selected.
| |
| Bob Barrows [MVP] 2007-06-19, 6:56 pm |
| David wrote:
>
> It prints the correct number of records, i.e. 1, 2 or 3 etc, but if I
> select just 1 record, I get a blank screen, if I select 2 records, I
> get 1 report printed.
> This is what happens if I add '-1' to the count property, otherwise,
> if I leave off the '-1' it works ok, apart from when 3 records are
> selected.
It's not just adding "-1", you have to start from 0
For i = 0 to Request.Form("printme").Count - 1
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
| |
|
|
Bob Barrows [MVP] wrote:
> David wrote:
>
>
>
> It's not just adding "-1", you have to start from 0
>
> For i = 0 to Request.Form("printme").Count - 1
------------------------------------------------------------------------------
If I try what you suggest, ALL I get returned is the same error code:
Request object error 'ASP 0105 : 80004005'
Index out of range
/Repair_Reports/index_Report_Multi.asp, line 17
An array index is out of range.
| |
| Bob Barrows [MVP] 2007-06-19, 6:56 pm |
| David wrote:
> Bob Barrows [MVP] wrote:
> ----------------------------------------------------------------------
--------
>
> If I try what you suggest, ALL I get returned is the same error code:
>
> Request object error 'ASP 0105 : 80004005'
>
> Index out of range
>
> /Repair_Reports/index_Report_Multi.asp, line 17
>
> An array index is out of range.
Sorry. I haven't given this enough attention. Request.Form("printme")
does not contain an array. It contains something called an IStringList.
You need to use Split to convert it to an array. Like this:
<%
dim i, ar
if Request.Form.Count>0 then
Response.Write typename(Request.Form("printme")) & "<br>"
ar=split(Request.Form("printme"),",")
for i = 0 to ubound(ar)
Response.Write ar(i) & "<br>"
next
end if
%>
<HTML>
<BODY>
<FORM action="" method=POST id=form1 name=form1>
<INPUT type="checkbox" name="printme" value="2533"><br>
<INPUT type="checkbox" name="printme" value="2567"><br>
<INPUT type="checkbox" name="printme" value="2568"><br>
<INPUT type="checkbox" name="printme" value="2569"><br>
<INPUT type="submit" value="Submit" id=submit1 name=submit1>
</FORM>
</BODY>
</HTML>
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
| |
| Daniel Crichton 2007-06-20, 3:56 am |
| Bob wrote on Tue, 19 Jun 2007 13:40:37 -0400:
> David wrote:
> --------
>
> Sorry. I haven't given this enough attention. Request.Form("printme")
> does not contain an array. It contains something called an IStringList.
> You need to use Split to convert it to an array. Like this:
>
> <%
> dim i, ar
> if Request.Form.Count>0 then
> Response.Write typename(Request.Form("printme")) & "<br>"
> ar=split(Request.Form("printme"),",")
> for i = 0 to ubound(ar)
> Response.Write ar(i) & "<br>"
> next
> end if
> %>
Or you use the collection:
For i = 0 to Request.Form("printme").Count - 1
Response.Write Request.Form("printme")(i) & "<br>"
Next
Which is simpler, and also means a comma in a value doesn't end up giving
you the wrong values ;)
Dan
| |
| Daniel Crichton 2007-06-20, 3:56 am |
| David wrote on Tue, 19 Jun 2007 05:20:24 -0700:
> Hi,
>
> I have a form on which a user can select a checkbox against a record.
> Each checkbox carries the RecordID of a product.
>
> ----------------------------------------------------------------
>
> I am trying to print the following report:-
>
> Details for product 1
>
> --------- Report / page Break ---------------
>
> Details for Product 6
>
> --------- Report / page Break ---------------
>
> or for whatever products were selected.
>
> Before the report is printed, the form submits to the next page with
> some code as follows:
>
> For i = 1 to Request.Form("printme").Count (where printme is the
> name of the checkbox)
>
> fieldName = Request.Form.Key(i)
> fieldValue = Request.Form.Item(i)
>
> if Request.Form.key(i) <> "Submit" then (Ignore Submit button)
Why are you reading these values, when your loop is only looking at the
"printme" count?
Try this:
For i = 0 to Request.Form("printme").Count -1
fieldValue = Request.Form("printme")(i)
strquery = ""
strquery = "SELECT * FROM reports"
strquery = strquery & " WHERE ((reports.ReportID='" & fieldValue & "'));"
Set RS = adoDataConn.Execute(strquery)
'Write out report for first ID retrieved in fieldValue
else
end if
Next
That should work. However, I'd suggest you use a parameterised proc instead
to prevent potential damage to your database.
I think the reason for your error is that on the 3rd iteration, you are
doing this:
fieldName = Request.Form(i).Key
If you have only 2 different form field names (ie. "submit" and "printme"),
then there is no 3rd Key value (ASP bundles up all of the "printme" fields
into a single Key and Value pair), hence the error. You need to use the loop
iteration to look at the collection of values in Request.Form("printme").
Dan
| |
| Daniel Crichton 2007-06-20, 3:56 am |
| Daniel wrote to David on Wed, 20 Jun 2007 08:38:38 +0100:
> Try this:
>
> For i = 0 to Request.Form("printme").Count -1
>
> fieldValue = Request.Form("printme")(i)
>
> strquery = ""
> strquery = "SELECT * FROM reports"
> strquery = strquery & " WHERE ((reports.ReportID='" & fieldValue & "'));"
>
> Set RS = adoDataConn.Execute(strquery)
>
> 'Write out report for first ID retrieved in fieldValue
>
> else 'remove this line!
> end if 'remove this line!
> Next
Oops. The
else
end if
needs to be removed from the above.
Dan
| |
|
| Thanks so much Dan & Bob,
I tried your adjusted code Dan, and now understand why it was crashing
out.
Your code still threw up the same error, so after a couple of minutes
of tinkering, I adjusted your code and got it to work 100%:
<%
For i = 1 to Request.Form("printme").Count
fieldValue = Request.Form("printme")(i)
strquery = ""
strquery = "SELECT * FROM reports"
strquery = strquery & " WHERE ((reports.ReportID='" & fieldValue &
"'));"
Set RS = adoDataConn.Execute(strquery)
<< write out records >>
Next
%>
Thank you so much to all who helped me on this post .......... well
done !! :-)
David
| |
| Daniel Crichton 2007-06-20, 7:56 am |
| David wrote on Wed, 20 Jun 2007 01:17:19 -0700:
> Thanks so much Dan & Bob,
>
> I tried your adjusted code Dan, and now understand why it was crashing
> out.
> Your code still threw up the same error, so after a couple of minutes
> of tinkering, I adjusted your code and got it to work 100%:
>
> <%
>
> For i = 1 to Request.Form("printme").Count
Ah, so it's not zero based. I couldn't remember (been a long time since I
looped through a request value in ASP), and I wasn't near my server to test
the code I'd written.
Dan
|
|
|
|
|