VBA, ADO. Параметры подключения и запроса

Question

У меня есть Excel VBA скрипт:

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

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

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

Answer 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 заботится об имени, кроме как для идентификации.

Answer 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 тип данных, аргумент размера 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 после добавления параметров.