NBDesign
Posts: 15
|
| Posted: 09/07/2007, 6:20 PM |
|
Why is this not working?
All the code works up until I get to the part where it loops through the data. Once it hits the "While not Recordset.EOF" I get....
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
/nbd_cart_events.asp, line 154
I have searched the internet and tried many different things to get it to work. It once worked, not sure what I did to make it stop working, but it did and nothing I do will get it working again.
Here is the entire piece of code....
Dim order_date
Dim last_order_id
Dim RecordSet
Dim SQL
Dim OrderTotal
Dim cid
Dim cip
cip = Request.ServerVariables("REMOTE_ADDR")
cid = Session("CartUserID")
' Count Order Total
SQL = "SELECT SUM(product_price*quantity) as total " _
& "FROM nbdCart_Items " _
& "WHERE " _
& "nbdCart_id =" & DBnbdCart.ToSQL(Request.Cookies("nbdCart_id"),ccsInteger)
Set RecordSet = DBnbdCart.Execute(SQL)
OrderTotal = CCGetValue(RecordSet, "total")
' Create a new order
SQL="INSERT INTO nbdSorders (cust_id, order_date, order_stat_id, total, cartip) " _
& "VALUES (" & DBnbdCart.ToSQL(cid, ccsInteger) _
& "," & DBnbdCart.ToSQL(Now(),ccsDate) _
& ",1" _
& "," & DBnbdCart.ToSQL(OrderTotal,ccsFloat) _
& "," & DBnbdCart.ToSQL(cip, ccsText) _
& ")"
DBnbdCart.Execute(SQL)
'Get Order ID
'This method compatible with all databases (unsafe when multiple users insert records at the same time)
'Use your own method for your database.
SQL = "SELECT MAX(nbdSorders_id) FROM nbdSorders"
Set RecordSet = DBnbdCart.Execute(SQL)
last_order_id = CCGetValue(RecordSet, 0)
'Move items from user's shopping cart into the order
SQL = "SELECT product_id, quantity, product_price " _
& "FROM nbdCart_Items " _
& "WHERE a.product_id=b.product_id AND shopping_cart_id = " & DBnbdCart.ToSQL(Request.Cookies("nbdCart_id"),ccsInteger)
Set RecordSet = DBnbdCart.Execute(SQL)
While not Recordset.EOF
SQL = "INSERT INTO nbdOrder_Items (nbdSorders_id, product_id, quantity, product_price)" _
& "VALUES (" & DBnbdCart.ToSQL(last_order_id,ccsInteger) _
& "," & DBnbdCart.ToSQL(CCGetValue(RecordSet, "product_id"),ccsInteger) _
& "," & DBnbdCart.ToSQL(CCGetValue(RecordSet, "quantity"),ccsInteger) _
& "," & DBnbdCart.ToSQL(CCGetValue(RecordSet, "product_price"),ccsInteger) & ")"
DBnbdCart.Execute(SQL)
Recordset.MoveNext
Wend
'Delete items from user's shopping
'DBnbdCart.Execute("DELETE FROM nbdCart_Items WHERE nbdCart_id = " & DBnbdCart.ToSQL(Request.Cookies("nbdCart_id"),ccsInteger) )
'DBnbdCart.Execute("DELETE FROM nbdCarts WHERE nbdCart_id = " & DBnbdCart.ToSQL(Request.Cookies("nbdCart_id"),ccsInteger) )
All based off the CCS Shopping Cart Example with a couple extra fields added, I need to add more but cannot until I get this back to a working state.
I am not a complete moron when it comes to this stuff, but this has me stumpped.
I have made the place where it stops in bold. If I remove the lines (While not), (recordset Move.Next) and (Wend), I can get the 1st line of the SQL in.... meaning this is the only thing that gets written to the database, DBnbdCart.ToSQL(last_order_id,ccsInteger) _
Any suggestions?
Thanks 
|
 |
 |
TheunisP
Posts: 342
|
| Posted: 09/07/2007, 9:03 PM |
|
I didn't check your logic but try this
While not Recordset.BOF and not Recordset.EOF
|
|
|
|
NBDesign
Posts: 15
|
| Posted: 09/07/2007, 9:40 PM |
|
Quote TheunisP:
I didn't check your logic but try this
While not Recordset.BOF and not Recordset.EOF
Thanks, but no, still getting the same error.
Not my logic, this is almost straight from the CCS Shopping cart example. I am trying to get this worked out before I change it too much.
I am trying to get information passed to PayPal. I had this almost all figured out then started messing with option fields and that is when it started messing up. Even though I have removed all the option fields and all their reference, I cannot get it back to a working state.
I am using an editable grid to pass the info to PayPal and an "On-Click" to remove the information from the temp cart. When moving the information from the temp cart to the final purchase is where the errors happen.
The code works up to the point it goes to write to the nbdOrder_Items. Sinple table, only 6 fields including the unique ID.
|
|
|
|
Edd
Posts: 547
|
| Posted: 09/08/2007, 12:00 AM |
|
NBDesign,
Your problem is in your SQL
SELECT product_id, quantity, product_price
FROM nbdCart_Items
WHERE a.product_id=b.product_id AND shopping_cart_id = " & DBnbdCart.ToSQL(Request.Cookies("nbdCart_id"),ccsInteger)
Look at the line WHERE a.product_id=b.product_id what are "a." and "b.", these are alias references that are meaningless.
Try inserting the following code after all your execute statements.
If DBnbdCart.Errors.Count > 0 Then
Response.Write "Database Error in SQL: & StrSQL & "<BR>"
Response.Write CCProcessError(DBnbdCart)
Response.End
End If
In future when you get a problem like this (and we all get them) use debug (or response.write) to get the values of your SQL statement before it executes and match that against a SQL query analyser.
Hope that helps.
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
NBDesign
Posts: 15
|
| Posted: 09/08/2007, 7:55 AM |
|
Quote Edd:
NBDesign,
Your problem is in your SQL
SELECT product_id, quantity, product_price
FROM nbdCart_Items
WHERE a.product_id=b.product_id AND shopping_cart_id = " & DBnbdCart.ToSQL(Request.Cookies("nbdCart_id"),ccsInteger)
Look at the line WHERE a.product_id=b.product_id what are "a." and "b.", these are alias references that are meaningless.
Try inserting the following code after all your execute statements.
If DBnbdCart.Errors.Count > 0 Then
Response.Write "Database Error in SQL: & StrSQL & "<BR>"
Response.Write CCProcessError(DBnbdCart)
Response.End
End If
In future when you get a problem like this (and we all get them) use debug (or response.write) to get the values of your SQL statement before it executes and match that against a SQL query analyser.
Hope that helps.
Edd
Hi Edd,
Thank you. Sometimes the little things get over looked. 
I removed the a and b reference and just left
& "WHERE shopping_cart_id = " & DBnbdCart.ToSQL(Request.Cookies("nbdCart_id"),ccsInteger)
I used your code and this is the error I am getting.
Quote :Database Error in SQL:
No value given for one or more required parameters. (Microsoft JET Database Engine)
Now I need to figure out which value is required that is not gettting passed. If I remove
While not Recordset.EOF
Recordset.MoveNext
Wend
from
While not Recordset.EOF
SQL = "INSERT INTO nbdOrder_Items (nbdSorders_id, product_id, quantity, product_price)" _
& "VALUES (" & DBnbdCart.ToSQL(last_order_id,ccsInteger) _
& "," & DBnbdCart.ToSQL(CCGetValue(RecordSet, "product_id"),ccsInteger) _
& "," & DBnbdCart.ToSQL(CCGetValue(RecordSet, "quantity"),ccsInteger) _
& "," & DBnbdCart.ToSQL(CCGetValue(RecordSet, "product_price"),ccsInteger) & ")"
DBnbdCart.Execute(SQL)
Recordset.MoveNext
Wend
It will insert the value of the shoping cart.... what ever the MAX value is and then fail..... no other info is written. However, before submitting the form, I see all the information in the HTML of the page.
Hmmmmm.
At least this gives me new areas to explore to find a resolve.
Thanks again Edd.....
|
|
|
|
NBDesign
Posts: 15
|
| Posted: 09/08/2007, 8:39 AM |
|
Got it! Thanks for all the help.
Edd, that little piece of code was a lifesaver..... 16+ hours of fussing with that code and your few little lines helped me in 20 min.
I owe you one
Thanks again.
|
|
|
|
|
