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

22

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

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

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

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

Ваш Ответ

2   ответа
4

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

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

When using adVarChar data type, the size argument to cmd.CreateParameter (e.g. 255) is required. Not supplying it results a run-time error 3708: Application-defined or object-defined error, as indicated in the documentation:

If you specify a variable-length data type in the Type argument, you must either pass a Size argument or set the Size property of the Parameter object before appending it to the Parameters collection; otherwise, an error occurs.

If the cmd.ActiveConnection property is set when cmd.CommandText is set, and cmd.CommandText contains named parameters, cmd.Parameters will be populated accordingly. Calling cmd.Parameters.Append afterwards could result in duplicates. For example:

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

I believe this is what is meant in the documentation, which is slightly inaccurate:

If the Prepared property of the Command object is set to True and the Command object is bound to an open connection when you set the CommandText property, ADO prepares the query (that is, a compiled form of the query that is stored by the provider) when you call the Execute or Open methods.

As a workaround, either set cmd.CommandText or cmd.ActiveConnection after adding parameters.

всякий раз, когда я пытаюсь сделать это, я всегда получаю ошибку во время выполненияMust Declare the scalar variable "@name"
38

к которому вы можете добавить параметры. Вот как это выглядит

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

Я считаю, что вы правы по порядку параметров. Это сбило меня с толку, когда я пришел из C # .Net, а затем кодировал VBScript.
@ LuigiMackenzieC.Brito, которая побеждает цель использования параметризованных запросов. Использование объединения переменных, содержащих пользовательские входные данные, для построения строк SQL-запросов - плохая практика. (Потому что, что если myparam = & quot; parentid; SELECT * from userpasswords & quot; или если подключающийся пользователь имеет права на запись: myparam = & quot; parentid; вставить в userpasswords ... & quot; или просто быть троллей, myparam = & quot; parentid; удалить из таблицы & quot;)
Спасибо, вы работаете нормально :) Alexey
Или вы можете просто ... .CommandText = & SELECT * FROM table WHERE parentid = & quot; & Амп; myparam & amp; & Quot ;; & Quot;
@ LuigiMackenzieC.Brito. Тем не менее, вы не упомянули об этом в своем комментарии, оставив тем самым опасность того, что кто-то может использовать ваш метод, не зная об SQL-инъекциях. Не рекомендуется делать конкат строки для запросов, даже если вы каким-то образом отфильтровали опасный код (в чем я сильно сомневаюсь).

Похожие вопросы