Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,001

    Connection Closed but still doing business?

    Hello,

    Not sure if this is the right place, so I will post in both: SQL Server and Excel

    I have an sp in SQL Server that catches 2 parameters, does a lookup, and returns the result.

    I have a procedure in Excel that opens an ADO connection STATIC, calls the stored procedure with the parameters attached, and fields the result in a local ado recordset. It then closes the connection.

    My understanding was that closing the connection orphans the recordset, and that the static parameter would makes the connection read only anyway. I also understood that the sp would return a discrete set of data, not an open connection to the tables.

    I have found that these understandings are not correct, because adding data in the local recordset is inserting values into the SQL server tables. This should not be happening.

    SQL Server:
    Code:
    PROCEDURE [dbo].[sp_Ups_ItemExtraData]
    	@ItemNum as varchar(25),
    	@ItemExtra as varchar(25)
    
    AS
    	
    BEGIN
    	
    	SET NOCOUNT ON;	
    	SELECT dbo.Items.ItemNo As Item, dbo.ItemExtralineDetails.DescriptionMed As Detail, dbo.ItemExtra.DescriptionMed As Value
    	FROM   dbo.Items INNER JOIN
    		   dbo.ItemExtra ON dbo.Items.ItemID = dbo.ItemExtra.ItemID INNER JOIN
    		   dbo.ItemExtralineDetails ON dbo.ItemExtra.ItemExtralineDetailID = dbo.ItemExtralineDetails.ItemExtralineDetailID
    	WHERE (dbo.ItemExtralineDetails.DescriptionMed = @ItemExtra) AND (dbo.Items.ItemNo = @ItemNum)
    END
    Excel:
    Code:
    Public Property Get FetchSQLbyADO(sproc As String, rs As ADODB.Recordset, Optional pars As ADODB.Parameters) As Boolean
    
        Dim cnn As ADODB.Connection
        Dim cmd As ADODB.Command
        Dim par As ADODB.Parameter
    
        
        On Error GoTo FetchError
    
        ' Establish connection
        Set cnn = New ADODB.Connection
        cnn.ConnectionString = ConnectionString
        cnn.Open
    
        ' Setup command
        Set cmd = New ADODB.Command
        cmd.ActiveConnection = cnn
        cmd.CommandText = sproc
        cmd.CommandType = adCmdStoredProc
        If Not pars Is Nothing Then
            For Each par In pars
                cmd.Parameters.Append par
            Next
        End If
    
       ' Open recordset
        Set rs = New ADODB.Recordset
        rs.CursorLocation = adUseClient
        rs.CacheSize = 100000
        rs.Open cmd, , adOpenStatic, adLockOptimistic
    
        FetchSQLbyADO = True
    
    FetchDone:
        Set cnn = Nothing
        Exit Property
    
    FetchError:
        MsgBox "Error " & Err.Number & vbCrLf & Err.Description, vbCritical + vbOKOnly, "Fetch Error"
        FetchSQLbyADO = False
        Resume FetchDone
    
    End Property
    What am I doing wrong?
    have fun,
    tc

    Small, custom, unique programs
    email
    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,569
    It is not possible to update a recordset produced by a stored procedure.
    You're missing something in your architecture.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,001
    I understand that is should not be possible.

    However, using the exact code shown in SQL Server 2008R2 and Exel 2010, I can reproduce the creation of records where all the fields are null in the SQL server table by adding rows to the recordset in VBA.

    The only thing I can figure is that when the recordset is opened in VBA, it creates it's own connection to the datasource. However, the STATIC parameter should still prevent writing data back. Not to mention the sp should be closed once it returns the original dataset.

    My work around (because I was in a rush and had to move on) was to create a second recordset in Excel, copy the smallish dataset line by line to the new recordset and close the original recordset. The cost is a few milliseconds of processor time, but it worked.

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,001
    Still curious about this one.
    How does the code shown write data back to the database?
    have fun,
    tc

    Small, custom, unique programs
    email
    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •