1

Topic: How to receive edited Recordset from UDF

Is MDB with the tables connected on a SQL Server. On type server UDF:

CREATE FUNCTION [dbo]. [TestUDF]
(
@forCode int
)
RETURNS TABLE
AS
RETURN
(
SELECT top (100) PERCENT * FROM Box WHERE Box. Code = forCode
)

How from it to receive edited Recordset?
I tried, just as I open SP:
[spoiler]

Public Function OpenSP (ByVal name As String, cmdType As ADODB.CommandTypeEnum, ParamArray ParamsAndValues ()) As ADODB.Recordset
Dim e As Error
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim param As ADODB.Parameter
Dim rst As ADODB.Recordset
Dim expr As String
Dim I As Long
On Error GoTo err_me
Set cnn = New ADODB.Connection
cnn. Open ConnString
Set cmd = New ADODB.Command
Set cmd. ActiveConnection = cnn
Set rst = New ADODB.Recordset
Set rst. ActiveConnection = cnn
cmd. CommandText = name
cmd. CommandType = cmdType
cmd. NamedParameters = True
' evaluated params
On Error Resume Next
For Each param In cmd. Parameters
If param. Direction = adParamInput Or param. Direction = adParamInputOutput Then
expr = GetOriginalName (param.name)
param. Value = Eval (expr)
End If
Next param
On Error GoTo err_me
' passed params
For I = LBound (ParamsAndValues) To UBound (ParamsAndValues) Step 2
Set param = cmd. Parameters (AND ParamsAndValues (I))
param. Value = ParamsAndValues (I + 1)
Next I
rst. CursorType = adOpenDynamic
rst. CursorLocation = adUseClient
rst. Open cmd
Set OpenSP = rst
exit_me:
Exit Function
err_me:
Debug. Print Now, Err. Number, Err. Description
For Each e In DBEngine. Errors
Debug. Print "", e. Number, e.source, e. Description
Next e
Debug. Print, name
Debug. Assert False
Err. Clear
Resume exit_me
End Function

[/spoiler]
Call

? openSP ("TestUDF", adCmdText, "forCode", 7777).RecordCount

But an error already at a stage of operation with a collection of parameters.

2

Re: How to receive edited Recordset from UDF

Hammering on a collection and causing through the text

Dim rst As ADODB.Recordset
Set rst = OpenSP ("SELECT * FROM TestUDF (7777)", adCmdText)
==> rst! [testField] = 1
rst. Update

I receive an error

Visual Basic wrote:

Current object Recordset does not support update. It is connected to restriction of the supplier or with the selected type of lock.

Though it is set

rst. CursorType = adOpenDynamic
rst. CursorLocation = adUseClient

The provider in a line of connection "SQLNCLI11"

3

Re: How to receive edited Recordset from UDF

In a variant with text request an error found and corrected - did not suffice

rst. LockType = adLockOptimistic

And here is how to a collection of parameters to reach at reversal by name of UDF - did not understand.

4

Re: How to receive edited Recordset from UDF

Understood: name UDF should be put in square brackets.

5

Re: How to receive edited Recordset from UDF

The magic ceased to work: for cmd. CommandType = adCmdTable at cmd. CommandText = "[UDF]" a collection cmd. Parameters it is empty.

6

Re: How to receive edited Recordset from UDF

Quitted a situation so: I form one more temporal Team, to it I put CommandType = adCmdStoredProc , I sort out its parameters, and for initial through Parameters. Append and CreateParameter I set appropriate parameters. Then for an initial command I substitute in the text [UDF] on [UDF] (???....??) .