If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > PC based Database Applications > Microsoft Excel > Connection Closed but still doing business?

LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Registered User
Join Date: Apr 2004
Location: outside the rim
Posts: 1,001
Connection Closed but still doing business?


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:
PROCEDURE [dbo].[sp_Ups_ItemExtraData]
	@ItemNum as varchar(25),
	@ItemExtra as varchar(25)

	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)
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

    ' 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
    End If

   ' Open recordset
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.CacheSize = 100000
    rs.Open cmd, , adOpenStatic, adLockOptimistic

    FetchSQLbyADO = True

    Set cnn = Nothing
    Exit Property

    MsgBox "Error " & Err.Number & vbCrLf & Err.Description, vbCritical + vbOKOnly, "Fetch Error"
    FetchSQLbyADO = False
    Resume FetchDone

End Property
What am I doing wrong?
have fun,

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

Sadly, there was no error number to look up ...
Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On