Can anyone point me in the right direction?

For some reason the stored proc is returning a closed recordset. The proc is executing and updating the table but for a reason unknown to me the recordset object is closed and errors out when I try to read it.

/ ************************************************** ****

    Dim rs As ADODB.Recordset
    Dim lError As Long
    Dim strError As String

    On Error GoTo ErrorHandler

    Set rs = gCn.Execute("Exec bsp_sRefSeed")
    ' Test for results
    ' Code is raising an error here
    If rs.BOF And rs.EOF Then
        ' Records where not found
        sRef = 0
    Else
        ' Return the sRef number
        sRef = rs!Seed
    End If

     etc ...

/ ************************************************** **
Stored Proc

USE BPROSCHD

GO

CREATE PROC bsp_sRefSeed

AS

/*
This proc will read the seed value
increment the seed value, and return the
value that was read.

This will ensure that no two parts receive the
same sRef number.

The seed value can only be 1 - 9999
*/

    Declare @Val int

SELECT TOP 1 @Val = Seed
FROM tblsRefSeed WITH (NOLOCK)

IF @Val <= 9998
-- The value is less than 9998 increment by one
BEGIN
    Update tblsRefSeed WITH (UPDLOCK)
    SET Seed = Seed + 1
END
IF @Val >= 9999
-- The value is at the maximum value set to 1
BEGIN
    Update tblsRefSeed WITH (UPDLOCK)
    SET Seed = 1
END

SELECT TOP 1 Seed
FROM tblsRefSeed WITH (NOLOCK)

GO

Any help will be greatly appreciated.


Larry Asher

__________________
Larry Asher

Hi Larry,

Its a common problem with updates, what's happening is that the recordset you get back has no fields, just the "1 row affected" message from the update statement.

Either add Set rs = rs.NextRecordset before you try the read, or just add SET NOCOUNT ON to your stored proc.

BTW your locking hints look a bit odd (presumably coz you've tried all sorts of options to get it working?). Don't you just need an UPDLOCK on the first select statement?

hth
Phil

In regards to rs.NextRecordset the SPROC should be returning a recordset containing one column/field, in this case it is the Seed field. The last statement in the SPROC is a SELECT statement which should make this a row returning call.

In regards to the locking hints each statement, SELECT or UPDATE, executes individually so the locking hints are in effect only while the statement is being executed. In addition you cannot use an UPDLOCK in a SELECT statement. I also think that locking hints need to be thought out carefully especially the NOLOCK.

The part that has me most confused here is things are not working as they have been (years) and I cannot seem to find the answers I am looking for. So, I find myself having to create some work arounds that I am not happy with. One because I do not think they are as good of a solution and secondly because I am not exactly sure why things are working as they did.

When I say not working as they did let me explain. In the past I used a dll to manage calls to the database. I changed this to an ActiveX exe so that I could share the connection object. Now when I try to set the active connection property of the command object I get an error saying it is of the wrong type, wrong arguments, wrong params, etc. This happened once when I was recently revising the dll as well. However, when this happened in the dll it was resolved, after all else failed and I re-booted my machine. No such luck with the ActiveX exe. However, all of the other database calls/functions work properly. Except I am left without any means to call a row returning SPROC.

If I make the connection object local I can set the active connection property of the command object, but still unable to return the rows from the SPROC. What I am getting in return is a closed recordset object?

This is a bit more wordy than I would like and probably lacking some details which I would be happy to provide if anyone thinks it may help.

Once again any help will be very much appreciated.

Larry Asher

Larry, just because you have only one select statement that doesn't mean you only get one recordset! I assume you're using the SQLOLEDB driver? With this driver each SQL statement within a stored procedure returns a "result", either a count of rows affected, or a resultset. In the case of your stored proc you are getting back 2 recordsets: the 1st is the count of rows affected by the UPDATE statement and the 2nd is the resultset from the SELECT statement. That is why you are seeing the 'closed recordset' error.

Yes you're right. personally I would put the whole thing in a TRANS and issue and UPDLOCK only against the SELECT statement - but it's up to you of course.

Nonsense. I quote from BOL
"Use update locks instead of shared locks while reading a table, and hold locks until the end of the statement or transaction. UPDLOCK has the advantage of allowing you to read data (without blocking other readers) and update it later with the assurance that the data has not changed since you last read it. "

rgds
Phil

Returning more than one recordset was something I was not aware of. I could write my SPROC to address that. Also, I do stand corrected in regards to the UPLOCK as well. I had misunderstood the BOL definition of NOLOCK.

"Do not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement."

So, thank you very much.

Do you have any ideas as to why the command object would not allow me to set the active connection property?

Here is the essence of what is happening. I have an ActiveX exe that accepts a connection object as a parameter by reference. When I try to set the command objects active connection property an error is raise arguments of wrong type, wrong parameters, etc.

To Phil I am much obliged, thanks.


Larry Asher

Larry, I think its a COM/ADO problem. You say you used to have it in a DLL but you changed it to an EXE? Well you know that DLLs run in-process and EXEs run out-of-process right? So in COM terms what you are trying to do now is marshall the Connection object across a process boundary, but you're getting problems because ADO doesn't know how to marshall Connections, Commands etc across process boundaries. I think you'll have to either revert back to a DLL, or change your EXE to accept the connection string and re-create the connection (not as bad as it sounds because with ADO's connection pooling you'll just get the same connection object back out of the pool without the overhead of re-creating it).

hth
Phil

Phil,

I was think the problem was related to out of process versus in process.

I am going to have to do some reading on connection pooling then. That is what I was trying to accomplish with the COM Server. I am trying to avoid a problem I ran into last year, not really a problem but I didn't like it. I have several dlls that access the database, such as, a security dll and other that provide specific functionality. In the end when I went it looked back at the SQL Enterprise Manager each user had about three connection to the database (each dll) - not what I wanted. The problem arises with encapsulation so I thought I could use the ActiveX exe to manage connections to the database if the client requested a global connection then if the named connection exist it set the connection equal to that connection object, otherwise a new one is created.

I think you understand what I am trying to do and perhaps I have gone about it the wrong way.

Once again thank you very much.

Larry Asher

I've known about this for awhile but don't know why Microsoft choose to put this behavior in with the SQLOLEDB driver?

What is the purpose of sending the update rowcount back as the first recordset? Can it actually be used? I have not been able to actually read this.

Do you have a code snippet where this might be a useful feature?

Thanks in advance for your comments.

RB

I don't know of any useful purpose because like yourself I have been unable to read multiple recordsets - it is not supported. So therefore it requires to calls to the database instead of just one.

You've got me?

Larry Asher

I can read multiple recordsets just fine but not the one the uses up space for the "number of rows" returned.

Try this:

1. Create a proc that does an insert
2. Select the identity value (alias it if you want it to have a field name)
3. Do another select (Select * from sometable)
4. See which ones you can read / access from VB Code (I'm doing it in VB6)

I can not read/use/open up the first recordset returned from the insert statement which would be the number of rows.

I can using the following code "set rs = rs.nextrecordset" access the identity value generated by sql.

I can then use "set rs=rs.nextrecordset" to access and work with all the records selected using the Select * statement.


My question is more of an architecture "why" would Microsoft ship the insert statement's "rows affected" back when it is in a recordset that can't be accessed. (At least I don't know how to.)

RB