ADO Recordset Getting Closed in C#
Hi,
I am working with ADO / C# and am encountering a problem where I get an error: "Operation is Not Allowed When Object is Closed", when trying to access a recordset. I am making a stored procedure call ("GetNextCustNo"). The stored procedure updates a key table and then returns the next unique key value. The code below calls the stored procedure. If I change the stored procedure so it doesn't update the table, ie it's a straight select query there is no error in the following code. But as soon as I change the value in the stored procedure via either an update,insert, or delete it seems to close the connection. The same code was working fine in VB. Has any one encountered a similar problem. Thank you.
ADODB.
Connection Conn =new ADODB.Connection();ADODB.
Recordset rsCustDetails =new ADODB.Recordset();Conn.CursorLocation = ADODB.
CursorLocationEnum.adUseClient;Conn.Open(ConnectStr,
"","", -1);rsCustDetails.Open(
"GetNextCustNo", Conn,CursorTypeEnum.adOpenDynamic,LockTypeEnum.adLockOptimistic, -1);int
id=Convert.ToInt32(rsCustDetails.Fields["CustNo"].Value);rsCustDetails.Open("GetNextCustNO", Conn,CursorTypeEnum.adOpenDynamic,LockTypeEnum.adLockOptimistic, -1);
I don't think it's the connection that's it's complaining about, but rather the recordset itself. If your stored procedure does an update before returning data, most likely the number of rows affected by the update is coming back as a separate resultset. In that case, rsCustDetails ends up as a closed recordset. You can try something like the following after you open the recordset:
object ra;
rsCustDetails = rsCustDetails.NextRecordset(out ra);
Another option is to add "SET NOCOUNT ON" at the beginning of your stored procedure, to prevent the update counts from being returned. This is an easy thing to try to determine if this is the problem or not, before you try to change any code. You could also check rsCustDetails.State to see if it really is the recordset that's closed, as opposed to the connection.
The same thing should be happening with VB6 as well, so I am surprised you aren't seeing it there. C# is just providing a wrapper around the ADO COM objects, but the real work is still being done by the same ADO component you would have used under VB6.
Thanks,
Sarah
Please Mark as Answer if this answers your question, or Unmark as Answer if it is marked and you feel it is not answered.
Thanks Sarah. The problem was exactly as you were saying. We edited the Stored Procedure and after the variable declarations added a Begin / End block to enclose the whole stored procedure code and in the begin/end block we added SET NOCOUNT ON and the calls started working. Basically SQL2005 stored procedure templates add that call by default but old code for SQL2000 didn't have it. As well, don't know why the VB was working but one thing we did see was that the exceptions were thrown by the Interops which VB doesn't have.
Thanks again
Dwight
