ADO Recordset Updateable Cursor

[alanf]

Does anyone know if there's a way, using ADO from VB, to connect to a SQL Server db, and execute a stored procedure, passing it a parameter, and open up an UPDATEABLE client-side cursor in VB? As far as I can see, in order to pass a parameter to a stored procedure, you must use the ADO command object, and this can only open a firehose cursor.

 

[CodeFish]

Hi alanf,

Yes, Try:-

Public Function GetStoredProcRS(vConnection, sStoredProcName As String) As ADODB.Recordset
'----------------------------------------------------------
' Author: Codefish
'
' Date: 23/03/2001
'
' History: -
'
' Purpose: - Executes Stored Procedure.
'
' Notes: - Returns ADO Recordset if Completed Successfully
'
' The returned Recordset object is always a read-only, forward-only cursor.
' If you need a Recordset object with more functionality, first create a
' Recordset object with the desired property settings, then use the Recordset
' object's Open method to execute the query and return the desired cursor type.
'
'----------------------------------------------------------

'Declare ADO Objects
Dim oCmd As ADODB.Command
Dim oRec As ADODB.Recordset

'Instantiate ADO Objects
Set oRec = New ADODB.Recordset
Set oCmd = New ADODB.Command

With oCmd
'Set the connection to use for this command
.ActiveConnection = vConnection

' Set the properties of the command
.CommandType = adCmdStoredProc
.CommandText = sStoredProcName
End With

'Create Recordset - Assign Properties
With oRec
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Open oCmd
End With

Set GetStoredProcRS = oRec

'Release Reference
Set oCmd = Nothing
Set oRec = Nothing

End Function

 

[alanf]

Thanks. That works.