VBA, ADO.Parametry połączenia i zapytania

Question

VBA, ADO.Parametry połączenia i zapytania

Mam skrypt Excela VBA:

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

Skrypt działa dobrze, ale chcę dodać do niego parametr. Na przykład " where (parentid = myparam)", Gdzie myparam ustawiono poza łańcuchem zapytania. Jak mogę to zrobić?

Oczywiście mogę zmodyfikować ciąg zapytania, ale myślę, że nie jest to zbyt mądre.

19

vba ado

Author: Alexey, 2012-04-27

Source

2 answers

Alternatywny przykład zwracający polecenie z funkcji:

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

Kilka rzeczy do zapamiętania:

Podczas używania typu danych adVarChar, wymagany jest argument size do cmd.CreateParameter (np. 255). Nie podanie go powoduje błąd w czasie wykonywania 3708: błąd zdefiniowany przez aplikację lub obiekt, jak wskazano w dokumentacji :

Jeśli podasz typ danych o zmiennej długości w argumencie Type, musisz przekazać argument Size lub ustawić rozmiar właściwość obiektu parametru przed dodaniem go do kolekcji parametrów; w przeciwnym razie wystąpi błąd.
Jeśli właściwość cmd.ActiveConnection jest ustawiona, gdy cmd.CommandText jest ustawiona, a cmd.CommandText zawiera nazwane parametry, cmd.Parameters zostanie odpowiednio wypełniona. Wywołanie cmd.Parameters.Append później może spowodować duplikaty. Na przykład:
```
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
```
Wydaje mi się, że o to chodzi w } dokumentacji , która jest nieco niedokładna:

Jeśli przygotowana własność obiekt Command jest ustawiony na True, a obiekt Command jest powiązany z otwartym połączeniem gdy ustawisz właściwość CommandText, ADO przygotowuje zapytanie (czyli skompilowaną formę zapytania przechowywaną przez dostawcę) podczas wywoływania metod Execute lub Open.

Jako obejście można ustawić cmd.CommandText lub cmd.ActiveConnection Po dodaniu parametrów.

2

Author: David Kennedy,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-12-18 18:13:37

score 33 · Accepted Answer

Musisz użyć ADODB.Obiekt polecenia, do którego można dodać parametry. Oto w zasadzie jak to wygląda

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

Znak zapytania w tekście poleceń jest symbolem zastępczym dla parametru. Wierzę, ale nie jestem pewien, że kolejność dodawania parametrów musi odpowiadać kolejności znaków zapytania (jeśli masz więcej niż jeden). Nie daj się zwieść, że parametr ma nazwę "parentid", ponieważ nie sądzę, aby ADO dbał o nazwę inną niż dla identyfikacji.