For Each Next Loop Problem

Hey guys, I'm using a for each next loop to cycle through some form field requests, everything works fine for the first 3 fields that meet the requirements but the 4 field throws an EOF/BOF Error even though I know the requested information exists in the database and does not have duplicate entries under the same name. The code is below, and I'm also open to suggestions on how to combine some of the work thats going on in this file. I know its probably not the most efficent way of working...


<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!-- #Include file = "admin/conn.inc" -->
<%
'Insert Quote Information
strSQL = "SELECT Quotes.* FROM Quotes;"
rs.Open strSQL, adoCon, 3, 3

Today = date
IP = Request.ServerVariables("REMOTE_HOST")

rs.AddNew

rs("IP") = IP
rs("QuoteDate") = Today

rs("Total") = Request.Form("Total")

rs.Update

rs.Close

'Retrieve Quote ID From Record Just Inserted
strSQL = "SELECT Quotes.ID FROM Quotes WHERE IP='" & IP & "' AND QuoteDate='" & Today & "';"
rs.Open strSQL, adoCon

QuoteID = rs("ID")

rs.Close

Dim rs2, strSQL2
Set rs2 = Server.CreateObject("ADODB.RecordSet")

'Retrieve Form Fields
For Each fld In Request.Form
'Filter out the unnecessary fields
If Instr(fld, "Price") = 0 AND Instr(fld, "Qty") = 0 Then
Short = Request.Form(fld)

'Retrieve Product ID and Price for Selected Product
strSQL = "SELECT ProdSvc.ID, ProdSvc.Price FROM ProdSvc WHERE Name='" & Short & "';"
rs.Open strSQL, adoCon

ProdID = rs("ID")
Price = rs("Price")

rs.Close

'Insert Quote Item Information
strSQL2 = "SELECT QuoteItems.* FROM QuoteItems;"
rs2.Open strSQL2, adoCon, 3, 3

rs2.AddNew

rs2("QuoteID") = QuoteID
rs2("ProdID") = ProdID
rs2("Price") = Price

rs2.Update

rs2.Close
End if
Next

'Retrieve Last Needed Fields
For Each fld In Request.Form
If Instr(fld, "Qty") Then
Qty = Request.Form(fld)

'Retrieve Proper Quote Item and Insert Qty
strSQL = "SELECT QuoteItems.* FROM QuoteItems WHERE ProdID='" & ProdID & "';"
rs.Open strSQL, adoCon, 3, 3

rs("Qty") = Qty

rs.Update

rs.Close
End if
Next

'Destroy Objects
adoCon.Close

Set rs = Nothing
Set rs2 = Nothing
set adoCon = Nothing

Response.Redirect "customer.asp"
%>

Personally, I find it much easier to debug SQL if you don't use Recordset.Update and instead get the database to execute a SQL UPDATE statement. That way you can see exactly what's being run.

Exactly which line is this falling over on? I'm not entirely clear on what it's supposed to be doing, but notice that you only set ProdID in the first loop but you use it in the second - where it's only ever going to have whatever value it got set to last.

The line that its hanging on is:
For Each fld In Request.Form
'Filter out the unnecessary fields
If Instr(fld, "Price") = 0 AND Instr(fld, "Qty") = 0 Then
Short = Request.Form(fld)

'Retrieve Product ID and Price for Selected Product
strSQL = "SELECT ProdSvc.ID, ProdSvc.Price FROM ProdSvc WHERE Name='" & Short & "';"
rs.Open strSQL, adoCon

ProdID = rs("ID")
Price = rs("Price")

rs.Close

'Insert Quote Item Information
strSQL2 = "SELECT QuoteItems.* FROM QuoteItems;"
rs2.Open strSQL2, adoCon, 3, 3

rs2.AddNew

rs2("QuoteID") = QuoteID
rs2("ProdID") = ProdID
rs2("Price") = Price

rs2.Update

rs2.Close
End if
Next

But it only hangs on this line on the 4th pass through for the last available item from the quote page.




Ok, heres the general idea of what I'm trying to do:

1) the user selects the quantity of the items they want (on the actual form page)

2) user submits the form which then goes to the processing page (shown above)

3) the processing page adds a new record to the Quotes table with the following information: Quote Date, User IP, Quote Total

4) retrieve the ID of the quote just entered

5) loop through the form field request and enter the following information about the items selected into the quoteItems table: QuoteID, ProductID, Qty

Problem solved. I rewrote the code in a different manner and now everything works just like it should.