Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Mar 2006
    Location
    Sumter, SC
    Posts
    178
    Thanks
    10
    Thanked 4 Times in 4 Posts

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

    Code:
    <%@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"
    %>

  • #2
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    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.

  • #3
    Regular Coder
    Join Date
    Mar 2006
    Location
    Sumter, SC
    Posts
    178
    Thanks
    10
    Thanked 4 Times in 4 Posts
    The line that its hanging on is:
    Code:
    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

  • #4
    Regular Coder
    Join Date
    Mar 2006
    Location
    Sumter, SC
    Posts
    178
    Thanks
    10
    Thanked 4 Times in 4 Posts
    Problem solved. I rewrote the code in a different manner and now everything works just like it should.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •