Inserting Multilple Fields into SQL Server 2014 with an Excel VBA UserForm?

Question

Can anyone show me how to multiple fields using with an inputbox using this method?

I would like to be able to insert a persons Name, Height, Gender, and Salary using a single UserForm? So far I only know how to insert one field at a time like in the code below.

Dim SQL as ADODB.Command
Set SQL = NEW ADODB.Command
SQL.CommandText = "INSERT INTO [myStuff].[dbo].[myThings](Salary) VALUES    (?)"
SQL.Parameters.Append SQL.CreateParameter("Name",adNumeric,adParamInput,"Length of input here", MyInputBox.Value) ' Perhaps you have to validate MyInputBox.Value
SQL.ActiveConnection = CN
SQL.Execute

Not exactly a duplicate, but [this answer](https://stackoverflow.com/a/43522802/1188513) actually explains things. — Mathieu Guindon, Oct 19 '17 at 17:06

Victor K · Answer 1 · 2019-04-18T15:18:57.330

You could concatenate it into the query, but that in most cases it is a bad idea. Here is a parameterised version (see below). Note that you have to open a connection that are suitable for writing and possibly validate input of your InputBox.Value.

Dim SQL as ADODB.Command
Set SQL = NEW ADODB.Command
    SQL.CommandText = "INSERT INTO [myStuff].[dbo].[myThings](Salary) VALUES (?)"
    SQL.Parameters.Append SQL.CreateParameter("Name",adNumeric,adParamInput,"Length of input here", MyInputBox.Value) ' Perhaps you have to validate MyInputBox.Value
    SQL.ActiveConnection = CN
    SQL.Execute

Edit: Here is a wrapper function that I use to simplify parameterising (you need to create and ADODB.Command object):

Function APCP( _
    ByRef Command As ADODB.Command, _
    ByVal DTE As DataTypeEnum, _
    ByVal PDE As ParameterDirectionEnum, _
    ByVal Value As Variant)
'APCP stand for "Append Parameter Create Parameter"
With Command
    Dim I As Long
    I = .Parameters.Count
    If Len(Value) > 0 Then
    .Parameters.Append .CreateParameter("f" & I, DTE, PDE, Len(Value), Value)
    Else
        Select Case DTE
        Case adNumeric
            Value = 0
            .Parameters.Append .CreateParameter("f" & I, DTE, PDE, 1, Value)
        Case adDate
            .Parameters.Append .CreateParameter("f" & I, DTE, PDE, 1, Null)
        Case Else
            .Parameters.Append .CreateParameter("f" & I, DTE, PDE, 1, vbNullString)
        End Select
    End If
End With
End Function

'Use Cases:
APCP YourADODBCommandObject, AdNumeric,adParamInput, 100
APCP YourADODBCommandObject, AdBoolean,adParamInput, True
APCP YourADODBCommandObject, AdChar,adParamInput, "Some Text"

Note that the function includes handling for zero-length parameters (in Select Case block) and you may need to change it to be in line with you requirements.

Thanks for 1) posting an answer to redeem that comment and 2) editing that answer to include some description. (upvoted) — Mathieu Guindon, Oct 19 '17 at 17:10

Inserting Multilple Fields into SQL Server 2014 with an Excel VBA UserForm?

1 Answers1

Linked