connection error when trying to execute an UPDATE

[jarv]

Please help, I get a connection error when trying to execute an UPDATE

ADODB.Connection error '800a0e79'

Operation is not allowed when the object is open.

/paddycampbell.www.co.uk/www/shop/response_page.asp, line 31



Set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionString = MM_CON_Database_STRING
rs.Source = "SELECT `Colour`, tblProductOptions.ID AS ProdOptionsID, tblCustomerOrders.OrderNumber, tblCustomerOrders.qty, tblCustomerOrders.productCode, tblCustomerOrders.option1, tblCustomerOrders.option2, tblProductOptions.Stock FROM tblProductOptions INNER JOIN tblCustomerOrders ON tblProductOptions.ProductID = tblCustomerOrders.productCode WHERE tblCustomerOrders.OrderNumber =" & request.querystring("OrderNumber")&" AND tblCustomerOrders.option2 LIKE `Colour` AND tblCustomerOrders.option1 LIKE `Size`"
rs.Open()

dim newStock

WHILE NOT rs.EOF
response.write("QTY: "& rs("qty")&"<br />")
ProdCode123 = rs("productCode")
ProdSize = rs("option1")
ProdColor = rs("option2")
response.write("Product Code: "& ProdCode123 &"<br />")
response.write("Size: "& ProdSize &"<br />")
response.write("Colour: "& ProdColor &"<br />")
response.write("Colour: "& ProdColor &"<br />")

newStock = rs("Stock") - rs("qty")

sql = "UPDATE tblProductOptions set Stock = " & newStock & " WHERE id = " & rs("ProdOptionsID")
conn.open
conn.Execute sql

rs.MoveNext() :: Wend

[Old Pedant]

You should only open a connection *ONCE*, no matter how many SQL queries of any kind you use with the one connection.

Remove that second "conn.Open" line.