I have a stored procedure that will not work, if I have an update in it. I assume this has something to do with a readonly access. I am running SQL Server 6.5 and I can run it fine from the query program, so the problem is not with Sql Server, but from the recordset access.
Here is the ASP code:
************************************************
Set connectionToDatabase=Server.CreateObject("ADODB.Connection")
connectionToDatabase.ConnectionTimeout = 60
connectionToDatabase.Open "Provider=sqloledb;data source=steg;" & _
"initial catalog=travdata;" & "user id=tfs;password=tfstom"
Set rs=Server.CreateObject("ADODB.Recordset")
billingName = title(0) & firstName(0) & lastName(0)
sqlQuery = "exec getLastBook"
response.write("sqlQuery = " & sqlQuery & "<br>")
rs.Open sqlQuery,connectionToDatabase,3,2
response.write("errors = " & connectionToDatabase.Errors.Count & "<br>")
if not rs.eof then
bookNum = rs(0)
end if
response.write("bookNum = " & bookNum)
rs.close
******************************************************
The error I get on my page is:
*************************************************
Error Type:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
/thetravelsite/sabreBookingDone.asp, line 59
***************************************************
The error is on the "if not rs.eof then" line.
I don't get this error if I take the update statement out of the Stored Procedure and have just the select statement there.
If I take out the .eof line so the page will display, connectionToDatabase.Errors.Count shows 0.
Why would the recordset be closed and how can I check to see why?
Thanks,