Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
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.
Actually, I don't know why I am having the problem, but if it seems to fail when I am doing an OLE connection, but works fine when I do an ODBC connection.
Someone said that the problem was that you couldn't do an update and a select in the same SP. That didn't make any sense. 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?
The stored procedure is -
*******************************************
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.
******************************************<HTML>
<HEAD>
<TITLE>ABC Corporation</TITLE>
</HEAD>
<BODY>
<H2>ABC Corporation Employee Telephone Extensions</H2>
<%
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:
*****************************************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:
******************************************
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).
 |
 |
 |
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
