Parameterized Queries in Visual Basic

The following example shows how you can use parameterized queries in your VB script.

Private Sub SampleParameterizedQuery()
    Dim ihConnectString As String
    Dim ihRecordSet     As ADODB.Recordset 
    Dim ihConnection    As ADODB.Connection
    Dim ihParameter     As ADODB.Parameter
    Dim ihCommand       As ADODB.Command

    'Set Up the Historian Connect String...
    Set ihConnectString = "Provider=ihOLEDB.iHistorian.1;User Id=;Password="

    'Create Our Other Objects...
    Set ihConnection = CreateObject("ADODB.Connection")
    Set ihRecordSet = CreateObject("ADODB.Recordset")
    Set ihCommand = CreateObject("ADODB.Command")

    'Open the Connection to the Historian Archiver...
    ihConnection.ConnectionString = ihConnectString
    ihConnection.Open
    'Set up the Command Object
    With ihCommand

        'Set the Active Connection to the Historian Connection Opened Above..
        .ActiveConnection = ihConnection

        'Set the Command Text to a Parameterized Sql Statement....
        .CommandText = "select * from ihTags where datatype = ?"

        'Set the Type of the Command...
        .CommandType = adCmdText

        'Refresh Our Parameter List...
        .Parameters.Refresh
    End With

    'Create a Single Parameter Object...
    Set ihParameter = ihCommand.CreateParameter("Temp", adChar, adParamInput, 100)
    'Set the Parameters Value...
    ihParameter.Value = "SingleFloat"
    'Add the Parameter to the Command Object...
    ihCommand.Parameters.Append ihParameter
    'Run the Command!
    Set ihRecordSet = ihCommand.Execute
End Sub