Can the fields be returned with names in a ADODB Result set for an output parameterized stored procedure

Question

Although I've worked a lot with VBA over the years, I'm finding my first opportunity to use it to connect to a MS SQL database and execute a number of different queries. This issue is about having the result set returned have named fields. Currently, and after some struggle with the parameters, I've gotten my query working. It executes a parameterized stored procedure that has one input and two output parameters.

Here is the sp:

ALTER PROCEDURE [dbo].[uspGetFamily] 
-- Add the parameters for the stored procedure here
@FormulaNum     varchar(10),
@FamilyID       bigint OUTPUT,
@FamilyName     varchar(20) OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT @FamilyID = v.FamilyID, @FamilyName = f.FamilyName FROM utblVariants as v
    INNER JOIN dbo.utblFamilies as f
    ON v.FamilyID = f.FamilyID
WHERE v.FormulaNo = @FormulaNum

SELECT @FamilyID, @FamilyName
END

And here is the relevant part of the VBA code:

'open the connection to the database
oConn.Open sConnString

'setup the command parameter including the sp parameters
With oCmd
    .ActiveConnection = oConn
    .CommandText = "BWDG.dbo.uspGetFamily"
    .CommandType = adCmdStoredProc
    'Formul number input parameter
    .Parameters.Append .CreateParameter("@FormulaNum", adVarChar, adParamInput, 10, Trim(txtFromFormula.Value))
    'Formula Family ID returned as an output parameter
    .Parameters.Append .CreateParameter("@FamilyID", adBigInt, adParamOutput, , Null)
    'Formula Family name returned as an output parameter
    .Parameters.Append .CreateParameter("@FamilyName", adVarChar, adParamOutput, 20, Null)

End With

'Get the resultset from the executed connection command
oRS.Open oCmd
'Set oRS = oConn.Execute(sExecString)

If Not (oRS.EOF And oRS.BOF) Then
    oRS.MoveFirst
    'Parse the data to corresponding variables
    iFromFamilyID = oRS(0)
    sFromFamily = oRS(1)

    lblFromFamily.Caption = sFromFamily

End If

I would like the items in the result set oRS to return with the fields named. Currently the names of the fields are empty. Field Names in the result set

This code will get reused (I'm just performing development testing currently) and I would like it to be more intuitive, not only for me, but for others who may end up maintaining this.

So, the question, is there a way to have the result set populated so the fields are named and thereby allow the item references to be the names instead of index number?

Answer 1

You have out parameters... why do you need to select them and return them in a recordset at all?

'...

Dim familyId As ADODB.Parameter
Dim familyName As ADODB.Parameter

With cmd
    '...
    Set familyId = .CreateParameter("@FamilyID", adBigInt, adParamOutput, , Null)
    .Paramters.Append familyId
    Set familyName = .CreateParameter("@FamilyName", adVarChar, adParamOutput, 20, Null)
    .Parameters.Append familyName

    .Execute
End With

And then you can retrieve the out parameter values right there:

lblFromFamily.Caption = familyName.Value

No recordset needs to get involved.

---

Alternatively, if you want to select the results and return a Recordset, you can do that - but then, drop the out parameters!

In that case you'll need to alias your columns, as was already mentioned in comments.

SELECT @FamilyID FamilyID, @FamilyName FamilyName

(the As keyword can be included, optionally, if you prefer that)

And then you can retrieve the fields by name:

familyId = rs.Fields("FamilyID").Value
familyName = rs.Fields("FamilyName").Value

Or, using the equivalent shorthand "bang" notation (assuming column aliases are legal VBA identifiers):

familyId = rs!FamilyID
familyName = rs!FamilyName