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

Re: Using For ... Loop to retrieve records....

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") & "
"



--
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.

Re: Using For ... Loop to retrieve records....

On 19 Jun, 15:02, "Bob Barrows [MVP]"
wrote:
> 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") & "
"

------------------------------------------------------------ -------------------------------

It returns the correct record IDs > 2533, 2567, 2568, 2569
i.e. I just selected these 4 records.

Re: Using For ... Loop to retrieve records....

On 19 Jun, 15:17, David wrote:
> On 19 Jun, 15:02, "Bob Barrows [MVP]"
> wrote:
>
>
>
> > 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") & "
"
>
> ------------------------------------------------------------ -------------------------------
>
> 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

Re: Using For ... Loop to retrieve records....

On 19 Jun, 15:35, David wrote:
> On 19 Jun, 15:17, David wrote:
>
>
>
> > On 19 Jun, 15:02, "Bob Barrows [MVP]"
> > wrote:
>
> > > 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") & "
"
>
> > ------------------------------------------------------------ -------------------------------
>
> > 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

------------------------------------------------------------ ------------------------------------------------------

Oh, before you ask, Line 16 in the error message is for:-
fieldName = Request.Form.Key(i)

------------------------------------------------------------ ------------------------------------------------------

Re: Using For ... Loop to retrieve records....

David wrote:
>>
>>> 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") & "
"
>
> ------------------------------------------------------------ ----------
---------------------
>
> 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 & "
"


--
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.

Re: Using For ... Loop to retrieve records....

On 19 Jun, 16:47, "Bob Barrows [MVP]"
wrote:
> David wrote:
>
> >>> 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") & "
"
>
> > ------------------------------------------------------------ ----------
>
> ---------------------
>
>
>
> > 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 & "
"
>
------------------------------------------------------------ -----------------------------------------

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.

Re: Using For ... Loop to retrieve records....

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.

Re: Using For ... Loop to retrieve records....

Bob Barrows [MVP] wrote:
> 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
------------------------------------------------------------ ------------------

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.

Re: Using For ... Loop to retrieve records....

David wrote:
> Bob Barrows [MVP] wrote:
>> 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
> ------------------------------------------------------------ ----------
--------
>
> 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")) & "
"
ar=split(Request.Form("printme"),",")
for i = 0 to ubound(ar)
Response.Write ar(i) & "
"
next
end if
%>

Re: Using For ... Loop to retrieve records....

Bob wrote on Tue, 19 Jun 2007 13:40:37 -0400:

> David wrote:
>> Bob Barrows [MVP] wrote:
>>> 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
>> ------------------------------------------------------------ ----------
> --------
>>
>> 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")) & "
"
> ar=split(Request.Form("printme"),",")
> for i = 0 to ubound(ar)
> Response.Write ar(i) & "
"
> next
> end if
> %>


Or you use the collection:

For i = 0 to Request.Form("printme").Count - 1
Response.Write Request.Form("printme")(i) & "
"
Next

Which is simpler, and also means a comma in a value doesn't end up giving
you the wrong values ;)

Dan

Re: Using For ... Loop to retrieve records....

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

Re: Using For ... Loop to retrieve records....

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

Re: Using For ... Loop to retrieve records....

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

Re: Using For ... Loop to retrieve records....

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