Oli wrote:
Hi
Here is the problem section of code.....
<%
else
intOrderID = cstr(Session("OrderID"))
set rsProd = Server.CreateObject("ADODB.Recordset")
rsProd.Open "SELECT * FROM itemsOrdered " _
& "WHERE OrderID="& intOrderID, _
Cn, adOpenDynamic, adLockPessimistic, adCmdText
while not rsProd.EOF
element = "quant" & rsProd("productID")
intQuant = Request.form(element)
if intQuant <> "" and isNumeric(intQuant) then
if intQuant = 0 then
rsProd.Delete
else
rsProd("quantity") = intQuant
end if
end if
rsProd.Update
rsProd.MoveNext
wend
.....................
The error is ADODB.Recordset (0x800A0E7D)
The connection cannot be used to perform this operation. It is either
closed or invalid in this context.
/basket.asp, line 109
Line 109 is set rsProd = Server.CreateObject("ADODB.Recordset")
I doubt that this is really line 109. This statement will never generate
that error. It must be one of the later lines.
Why are you opening such an expensive cursor (dynamic, pessimistic lock)?
You are limiting the ability of your application to handle many users.
Depending on what kind of database this is (always tell us the type and
version of your database: it's always relevant), and how many rows are in
this table, you coulc possibly be locking all other users completely out of
this table while this loop is occurring.
Also, I don't know how many columns your table has, but it seems very
wasteful to use selstar ("select * ") to pull them all down when you only
seem to need work with two of them, especially when you consider that using
selstar requires ADO to make two trips to your database instead of one.
Instead of looping through a recordset, you might want to consider looping
through your form variables instead, constructing the appropriate sql
statements (or better yet, passing the values to a stored procedure) to
accomplish the deletion or update. Something like this*:
dim sSQL, prodid
intOrderID = cstr(Session("OrderID"))
for each element in request.form
if left(element,5) = "quant" then
prodid = mid(element,6)
intQuant = Request.form(element)
if len(intQuant) > 0 then
if CInt(intQuant) = 0 then
sSQL = "DELETE FROM itemsOrdered " & _
"WHERE OrderID="& intOrderID & " AND " & _
"productID = " & prodid
else
sSQL = "UPDATE itemsOrdered " & _
"SET quantity = " & intQuant & _
" WHERE OrderID="& intOrderID & " AND " & _
"productID = " & prodid
end if
cn.Execute sSQL,,adCmdText
end if
end if
next
*I don't know the datatype of productID. I assumed above that it was
numeric. If it is actually character, change the above statements to:
"productID = '" & prodid & "'"
HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
