Results 1 to 1 of 1
11-12-12, 18:46 #1Registered User
- Join Date
- Apr 2004
- outside the rim
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.
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) ENDCode:
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 Propertyhave fun!
click to email
Independent Development Consultant
Biz db & reports - SQL, Access, Crystal, Seradex and more
Small, custom, unique programs - Office, VBA, .NET
Favorite message from Windows:
There was an error displaying the previous error message
Sadly, there was no error number to look up ...