Recordset executing, but not opening 
Author Message
 Recordset executing, but not opening

I'm attempting to execute the following sproc in the SQL DB "BackOffice"
from my VB COM object:

        CREATE PROCEDURE spGetNextMagicKey

        AS


        BEGIN



        END

If, in my VB code, I create a DB connection this way:

        Dim DBConn As ADODB.Connection

        Dim strConn As String

        Set DBConn = CreateObject("ADODB.Connection")

        strConn = "Provider=sqloledb.1;Extended
Properties='server=OGRE;database=BackOffice;uid=user1;pwd=password"'"

        DBConn.ConnectionString = strConn

        DBConn.Open

and open the recordset using the above DB connection as below, RsTmp.State
is set to adStateClosed:

      Dim sSql As String

      Dim RsTmp As ADODB.Recordset

        sSql = "spGetNextMagicKey"

        RsTmp.Open sSql, DBConn, adOpenStatic, adLockOptimistic

However, if I create a DB connection using a System DSN as below and open as
above, rsTmp.State is set to adStateOpen, as I expect.

        Dim DBConn As ADODB.Connection

        Set DBConn = New ADODB.Connection

        DBConn.Open "MYDSN", "user1", "password"

Can someone please tell me what may be happening here? I can't see any
obvious reason why I shouldn't be able to do the following:

        If Not (RsTmp.EOF And RsTmp.BOF) Then

            Dim lMyVal As Long

            lMyVal = RsTmp!NextKey

        End If 'Not (Rs.EOF And Rs.BOF)

Thank You,

Shawn



Sat, 17 Dec 2005 04:50:19 GMT  
 Recordset executing, but not opening
One more thing. It seems that I've traced the problem down to the following:

            update next_image_key

    where the_key = 'A'

In the sproc referenced in my previous post, I'm calling another sproc as
such:


That sproc looks like this:

            CREATE procedure GetNextImageKey
    as

    begin
        begin tran


        from    next_image_key
                        where the_key = 'A'


                        update next_image_key

                        where the_key = 'A'

        commit tran


        return(0)
    end

As I said, commenting out the update command will allow me to open a
recordset with the NextKey in it from within my VB app.

Can someone explain why the update command is causing me this grief and how
I might get around it?

Thanks,
Shawn


Quote:
> I'm attempting to execute the following sproc in the SQL DB "BackOffice"
> from my VB COM object:

>         CREATE PROCEDURE spGetNextMagicKey

>         AS


>         BEGIN



>         END

> If, in my VB code, I create a DB connection this way:

>         Dim DBConn As ADODB.Connection

>         Dim strConn As String

>         Set DBConn = CreateObject("ADODB.Connection")

>         strConn = "Provider=sqloledb.1;Extended
> Properties='server=OGRE;database=BackOffice;uid=user1;pwd=password"'"

>         DBConn.ConnectionString = strConn

>         DBConn.Open

> and open the recordset using the above DB connection as below, RsTmp.State
> is set to adStateClosed:

>       Dim sSql As String

>       Dim RsTmp As ADODB.Recordset

>         sSql = "spGetNextMagicKey"

>         RsTmp.Open sSql, DBConn, adOpenStatic, adLockOptimistic

> However, if I create a DB connection using a System DSN as below and open
as
> above, rsTmp.State is set to adStateOpen, as I expect.

>         Dim DBConn As ADODB.Connection

>         Set DBConn = New ADODB.Connection

>         DBConn.Open "MYDSN", "user1", "password"

> Can someone please tell me what may be happening here? I can't see any
> obvious reason why I shouldn't be able to do the following:

>         If Not (RsTmp.EOF And RsTmp.BOF) Then

>             Dim lMyVal As Long

>             lMyVal = RsTmp!NextKey

>         End If 'Not (Rs.EOF And Rs.BOF)

> Thank You,

> Shawn



Sat, 17 Dec 2005 22:05:06 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Connection.Execute vs. Recordset.Open or Command.Open

2. Item Open Event does not execute..

3. Opening recordsets(OpenRecordset vs. Recordset.Open)

4. Connection not Open + Server has not yet been opened error messages

5. recordset not open ???

6. Error not EOF - Open empty recordset

7. Recordset not open after calling sp_helpdb

8. Opened Recordsets don't show up in Recordsets Collection

9. Recordset Experts...SQL statement on an open recordset?

10. Open ADO recordset on another ADO recordset - possible?

11. Open a recordset of another recordset

12. open recordset, change connection, update recordset?

 

 
Powered by phpBB® Forum Software