VBScript, ADO and query parameters (question marks), a handy function

sql_parametric.vbs

' inspired by:
' http://stackoverflow.com/questions/2557606/how-do-i-associate-parameters-to-command-objects-in-ado-with-vbscript
' http://stackoverflow.com/questions/10352211/vba-ado-connection-and-query-parameters

' The function takes three parameters: ADOConnection; sql (string); params, an Array() of parameters
Function sqlOperationParametric(ADOConnection,sql,params)
  Dim Cm, Pm, i, p

  Set Cm = CreateObject("ADODB.Command")
  With Cm
    .ActiveConnection = ADOConnection
    .CommandText = sql
    .CommandType = adCmdText
    
    For i=0 To UBound(params)
      p = params(i)
      Set Pm = .CreateParameter()
      With Pm
        .Direction = adParamInput
        Select Case VarType(p)
          Case vbString
            .Type = adVarChar
            .Size = Len(p)
            .Value = p
          Case vbInteger, vbLong, vbSingle, vbDouble
            .Type = adDouble
            Pm.Value = p
          Case vbDate
            .Type = adDBTimeStamp
            .Value = p
          ' sure there should be other cases, but these are the most used
        End Select
      End With
      .Parameters.Append Pm
    Next
    
    Set sqlOperationParametric = .Execute
  End With
End Function