473,321 Members | 1,622 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

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

Jun 19 '07 #1
14 3068
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.
Jun 19 '07 #2
On 19 Jun, 15:02, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
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") & "<BR>"
-------------------------------------------------------------------------------------------

It returns the correct record IDs 2533, 2567, 2568, 2569
i.e. I just selected these 4 records.
Jun 19 '07 #3
On 19 Jun, 15:17, David <davidgor...@scene-double.co.ukwrote:
On 19 Jun, 15:02, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
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") & "<BR>"

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

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

Jun 19 '07 #4
On 19 Jun, 15:35, David <davidgor...@scene-double.co.ukwrote:
On 19 Jun, 15:17, David <davidgor...@scene-double.co.ukwrote:
On 19 Jun, 15:02, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
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") & "<BR>"
-------------------------------------------------------------------------------------------
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)

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

Jun 19 '07 #5
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") & "<BR>"

----------------------------------------------------------------------
---------------------
>
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.
Jun 19 '07 #6
On 19 Jun, 16:47, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
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") & "<BR>"
----------------------------------------------------------------------

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

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.

Jun 19 '07 #7
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.
Jun 19 '07 #8

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.

Jun 19 '07 #9
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")) & "<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.
Jun 19 '07 #10
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")) & "<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
Jun 20 '07 #11
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
Jun 20 '07 #12
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
Jun 20 '07 #13
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

Jun 20 '07 #14
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
Jun 20 '07 #15

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Charles Alexander | last post by:
Hello I am new to php & MySQL - I am trying to retrieve some records from a MySQL table and redisplay them. The data in list form looks like this: Sample_ID Marker_ID Variation ...
19
by: James Fortune | last post by:
I have a lot of respect for David Fenton and Allen Browne, but I don't understand why people who know how to write code to completely replace a front end do not write something that will automate...
11
by: Grasshopper | last post by:
Hi, I am automating Access reports to PDF using PDF Writer 6.0. I've created a DTS package to run the reports and schedule a job to run this DTS package. If I PC Anywhere into the server on...
10
by: shubha.sunkada | last post by:
Hi, I have a recordset connection in asp that I am using to search records.If I use the client side cursorlocation (rs.cursorlocation=3) then it takes really long to return back the records due...
0
by: sarakumarsg | last post by:
Hai, i would like to use dynamic sql for update records in the DB@ db. Means, the value to be updated will be send as concatenated string. Then based on the concatnation value i need to...
10
by: chimambo | last post by:
Hi All, I have a little problem. I am retrieving records from a table and I want to update the records using checkboxes. I am able to display the database record quite alright and I have created...
9
ADezii
by: ADezii | last post by:
One question which pops up frequently here at TheScripts is: 'How do I retrieve data from a Recordset once I've created it?' One very efficient, and not that often used approach, is the GetRows()...
3
ADezii
by: ADezii | last post by:
Last Tip, we demonstrated the technique for retrieving data from a DAO Recordset, and placing it into a 2-dimensional Array using the GetRows() Method. This week, we will cover the same exact Method...
2
by: Carlton Kirby | last post by:
I need to execute a job on a SQL Express 2005 instance (no SQLAgent). The job will be executed manually by a user, so it doesn't need to be scheduled to run automatically. I thought I could...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.