Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Stored Procedure not working 2

Status
Not open for further replies.
tfstom

tfstom

Programmer
Sep 28, 2002
190
US
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,

Tom.
 
Sort by date Sort by votes
I get the same error if I change the open line to:

rs.Open sqlQuery,connectionToDatabase,adOpenStatic,adLockPessimistic
 
Upvote 0 Downvote
have you tried to open using instead of:
rs.Open sqlQuery,connectionToDatabase,3,2
(adStatic, adLockPessimistic - I hate numbers)
as:
rs.Open sqlQuery,connectionToDatabase,adStatic, adLockOptimistic
(lock records only on update)

 
Upvote 0 Downvote
Actually, I don't know why I am having the problem, but if it seems to fail happen when I am doing an OLE connection, but works fine when I do an ODBC connection.

I was just trying to see if the "adOpenStatic, adLockPessimistic" parameters might help - but they had nothing to do with it.

The stored procedure is (and I know I have given this in another post, but we never figured out the problem as I thought it was something else) -

Code: 
CREATE PROCEDURE getLastBook as
declare @book char(5)

select @book=max(book_key) from booking
update booking set status = 25 where book_key = "70005"
select @book

This is passing back the variable @book as well as the update.

Code: 
<HTML>
<HEAD>
<TITLE>ABC Corporation</TITLE>
</HEAD>
<BODY>

<H2>ABC Corporation Employee Telephone Extensions</H2>
<P>

<%
Dim connectionToDatabase
Dim recordCollection

Set connectionToDatabase=Server.CreateObject("ADODB.Connection")

'connectionToDatabase.Open "Driver=SQL Server; Server=steg; uid=sa; pwd="

connectionToDatabase.Open "Provider=sqloledb;data source=steg;" & _
"initial catalog=travdata;" & _
"user id=sa;password="

Set recordCollection =connectionToDatabase.Execute("exec getLastBook")

if not recordCollection.eof then
    response.write(recordCollection(0))
end if

connectionToDatabase.Close
Set connectionToDatabase=Nothing

%>
</BODY>
</HTML>

You will notice 2 connection open calls. In this case, I have the ODBC one commented out and get the following error:

Code: 
Error Type:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
/connectDB13.asp, line 22

If I comment out the 2nd one (the OLE connection) and uncomment the ODBC open, it works fine and passes back the book number. The page returned is:

Code: 
ABC Corporation Employee Telephone Extensions
70094

This is what I would expect - Why doesn't the OLE connection work - it does do the update, BTW, even though it doesn't pass back the booking (or at least the recordset is not opened - as the .execute is supposed to do and does in the ODBC type).
 
Upvote 0 Downvote
I ran into this situation once. I seem to remember having to write some code like the following:

While rs.state <> adstateopen
set rs = rs.NextRecordset
wend

Do this process right after "rs.Open sqlQuery,connectionToDatabase,3,2"

The reason you are having this problem is because you are doing multiple processes(Select,Update, and then Select again) within the stored procedure and I think ADO handles each as a separate recordset.

 
Upvote 0 Downvote
How would ADO know what is happening inside of the Stored Procedure? This is done on the Server and only the results would be sent back.

Also, why would it work with ODBC and not OLE?
 
Upvote 0 Downvote
Hi Chaps

I have just come across this and have discovered, half by accident, and half by some distant memory, that (and I stand to be corrected) you cannot return a recordset from a stored procedure if there is an update in the SP as well.

Comment out the update and try it again - I will have to rethink my SP to do what I want it to do and return the recordset.

Derren
[Mediocre talent - spread really thin]
 
Upvote 0 Downvote
I have another update, which is that you need to add

SET NOCOUNT ON

to the SP. This will prevent the messages such as "4 row(s) affected" from being returned.

As there are more than one messages returned from the SP it must freak out the connection. I have added it to my SP and the recordset is finally being created.

Is this the answer? Don't know, but for now it seems to work, but feels fragile ...

Derren
[Mediocre talent - spread really thin]
 
Upvote 0 Downvote
I have already done that, and you are right about the fact that if you comment out the update, it works fine.

But ODBC has no problem with it. Why does OLE?

And why would there be a problem anyway. The driver doesn't know that there is an update in the stored procedure. It only tells Sql Server to run it. If you can't have both, it would really take away some of the reason for using SPs. Many SPs do both. If this were the case, I would have to make 2 classes of SPs - one type for OLE and one type for everything else.
 
Upvote 0 Downvote
Thanks derren... I came accross your post and that is exactly what I was looking for.. for hours!

SET NOCOUNT ON

worked for me I just placed it at the start of the sp

thanks again!!

this site rocks!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
iis woos (not displaying pages correctly)
Replies
1
Views
167
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
Multi-selection dropdown list does not work
Replies
0
Views
81
leifoet
leifoet
leifoet
  • Locked
  • Question
ASP window.open not working without activating pop up blokker
Replies
5
Views
133
ChrisHirst
ChrisHirst
Ambientguitar
  • Locked
  • Question
Issue with this not calling the Sub in Classic ASP page
Replies
5
Views
222
MarkSweetland
MarkSweetland
SitesMasstec
  • Locked
  • Question
Sub-folder does not show green padlock
Replies
8
Views
156
ChrisHirst
ChrisHirst

Part and Inventory Search

Sponsor

Back
Top