Параметры VBA, ADO.Connection и query

У меня есть excel VBA script: 

Set cоnn = CreateObject("ADODB.Connection")
conn.Open "report"
Set rs = conn.Execute("select * from table" )

Script работает нормально, но я хочу добавить к нему параметр. Например, "where (parentid = myparam)", где myparam установил внешнюю строку запроса. Как я могу это сделать?

Конечно, я могу изменить строку запроса, но я думаю, что это не очень мудро.

Ответы

Ответ 1

Вам нужно использовать объект ADODB.Command, к которому вы можете добавить параметры. Вот в основном то, что выглядит как

Sub adotest()

    Dim Cn As ADODB.Connection
    Dim Cm As ADODB.Command
    Dim Pm As ADODB.Parameter
    Dim Rs as ADODB.Recordset

    Set Cn = New ADODB.Connection
    Cn.Open "mystring"
    Set Cm = New ADODB.Command
    With Cm
        .ActiveConnection = Cn
        .CommandText = "SELECT * FROM table WHERE parentid=?;"
        .CommandType = adCmdText

        Set Pm = .CreateParameter("parentid", adNumeric, adParamInput)
        Pm.Value = 1

        .Parameters.Append Pm

        Set Rs = .Execute
    End With

End Sub

Значок вопроса в CommandText является заполнителем для параметра. Я считаю, но я не уверен, что порядок, который вы добавляете, должен соответствовать порядку вопросительных знаков (когда у вас более одного). Не обманывайтесь, что параметр называется "parentid", потому что я не думаю, что ADO заботится об имени, отличном от идентификации.

Ответ 2

Альтернативный пример возврата команды из функции:

Function BuildCommand(conn As ADODB.Connection) As ADODB.Command
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = conn
    cmd.CommandType = adCmdText
    cmd.Parameters.Append cmd.CreateParameter("@name", adVarChar, adParamInput, 255, "Dave")
    cmd.CommandText = "SELECT * FROM users WHERE name = @name;"
    Set BuildCommand = cmd
End Function

Несколько вещей, чтобы отметить:

  1. При использовании adVarChar данных adVarChar аргумент size для cmd.CreateParameter (например, 255). Если его не предоставить, возникает ошибка времени выполнения 3708: ошибка приложения или объекта, как указано в документации:

    Если вы указываете тип данных переменной длины в аргументе Type, вы должны либо передать аргумент Size, либо установить свойство Size объекта Parameter перед добавлением его в коллекцию Parameters; в противном случае возникает ошибка.

  2. Если свойство cmd.ActiveConnection установлено, когда установлено cmd.CommandText, а cmd.CommandText содержит именованные параметры, cmd.Parameters будет заполняться соответствующим образом. Вызов cmd.Parameters.Append впоследствии может привести к дублированию. Например:

    cmd.ActiveConnection = conn
cmd.CommandType = adCmdText
Debug.Print cmd.Parameters.Count ' 0

cmd.CommandText = "SELECT * FROM users WHERE name = @name;"
Debug.Print cmd.Parameters.Count ' 1

cmd.Parameters.Append cmd.CreateParameter("@name", adVarChar, adParamInput, 255, "Dave")
Debug.Print cmd.Parameters.Count ' 2

    Я полагаю, что это то, что подразумевается в документации, которая немного неточна:

    Если для свойства Prepared объекта Command установлено значение True, а объект Command связан с открытым соединением при установке свойства CommandText, ADO подготавливает запрос (т.е. Скомпилированная форма запроса, которая сохраняется поставщиком). когда вы вызываете методы Execute или Open.

    В качестве обходного пути установите cmd.CommandText или cmd.ActiveConnection после добавления параметров.