Creating ADODB Parameters on the fly

I have put together a small wrapper class to simplify creating parameterized ADODB queries with VB6/VBA. At this point I'm keeping things simple, so it's only supporting input parameters and from what I've tested it seems to work exactly as intended.

The main reason for writing this, is because creating SQL Injection -safe queries with ADODB involves creating an ADODB.Parameter for each parameter value, which can be combersome; to a beginner it's much easier to just concatenate the values into the command string.

The first thing I did was creating a "converter" class to take any value and spit out an ADODB.Parameter object - I called that class AdoValueConverter:

AdoValueConverter Class

`Option Explicit

Public Function ToStringParameter(ByVal value As Variant, ByVal direction As ADODB.ParameterDirectionEnum) As ADODB.Parameter

Dim stringValue As String
stringValue = CStr(value)

Dim result As New ADODB.Parameter
With result
.type = adVarChar
.direction = direction
.size = Len(stringValue)
.value = stringValue
End With

Set ToStringParameter = result

End Function

Public Function ToIntegerParameter(ByVal value As Variant, ByVal direction As ADODB.ParameterDirectionEnum) As ADODB.Parameter

Dim integerValue As Long
integerValue = CLng(value)

Dim result As New ADODB.Parameter
With result
.type = adInteger
.direction = direction
.value = integerValue
End With

Set ToIntegerParameter = result

End Function

Public Function ToLongParameter(ByVal value As Variant, ByVal direction As ADODB.ParameterDirectionEnum) As ADODB.Parameter

Set ToLongParameter = ToIntegerParameter(value, direction)

End Function

Public Function ToDoubleParameter(ByVal value As Variant, ByVal direction As ADODB.ParameterDirectionEnum) As ADODB.Parameter

Dim doubleValue As Double
doubleValue = CDbl(value)

Dim result As New

This seems extra complexity with no purpose.

You take any type variable and automatically convert it to a parameter (this is good).

But then something strange happens, you look at the type of the variable and convert that to a string so you can call a function named after the type to do a standard set of options that only change based on the type.

Why have all these functions -- you don't use them anywhere else in your design. Create a function that makes a parameter based on type -- this is what you are actually doing.

Public Function ToParameter(ByVal value As Variant, ByVal direction As ADODB.ParameterDirectionEnum) As ADODB.Parameter

    Dim result As New ADODB.Parameter

    result.direction = direction

    Select  TypeName(value)
      Case "String"
        result.type = adVarChar
        result.size = Len(CStr(value))
        result.value = CStr(value)
      Case "Integer"
        result.type = adInteger
        result.value = CLng(value)
      Case "Double"
        result.type = adDouble
        result.value = CDbl(value)
    End Select

    Set ToParameter = result

End Function

If you feel the function is getting "to long", then make a helper function that sets direction, type and value on a new ADODB.Parameter and re-factor all those lines out.

I'm fairly sure you don't need to cast "value" to the type as you do, you have already checked its type and you are not changing the type.

Remember, unless there is a reason to do something all the extra stuff is just extra stuff.

StackExchange Code Review Q#46312, answer score: 18