VBA, ADO.Connection e parametri di query

Ho scritto script VBA excel:

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

Lo script funziona bene, ma voglio aggiungere dei parametri. Ad esempio “where (parentid = myparam )”, dove myparam è stato impostato all’esterno della stringa di query. Come posso farlo?

Naturalmente posso modificare la stringa di query, ma penso che non sia molto saggio.

È necessario utilizzare un object ADODB.Command a cui è ansible aggiungere parametri. Ecco in sostanza come appare

 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 

Il punto interrogativo nel CommandText è il segnaposto per il parametro. Credo, ma non sono sicuro, che l’ordine in cui apponi i parametri deve corrispondere all’ordine dei punti interrogativi (quando ne hai più di uno). Non lasciatevi ingannare dal fatto che il parametro sia denominato “parentid” perché non penso che ADO si preoccupi del nome diverso dall’identificazione.

Esempio alternativo che restituisce un comando da una funzione:

 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 

Un paio di cose da notare:

  1. Quando si utilizza il tipo di dati adVarChar , è necessario l’argomento dimensione per cmd.CreateParameter (ad esempio 255). Non fornendola risulta un errore di run-time 3708: Errore definito dall’applicazione o definito dall’object, come indicato nella documentazione :

    Se si specifica un tipo di dati a lunghezza variabile nell’argomento Type, è necessario passare un argomento Size o impostare la proprietà Size dell’object Parameter prima di aggiungerlo alla raccolta Parameters; altrimenti, si verifica un errore.

  2. Se la proprietà cmd.CommandText è impostata quando viene impostato cmd.CommandText e cmd.CommandText contiene parametri con nome, cmd.Parameters verrà popolato di conseguenza. Chiamando cmd.Parameters.Append seguito potrebbe risultare in duplicati. Per esempio:

     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 

    Credo che questo sia ciò che si intende nella documentazione , che è leggermente impreciso:

    Se la proprietà Prepared dell’object Command è impostata su True e l’object Command è associato a una connessione aperta quando si imposta la proprietà CommandText, ADO prepara la query (ovvero, una forma compilata della query memorizzata dal provider) quando chiami i metodi Execute o Open.

    Per ovviare al problema, impostare cmd.CommandText o cmd.CommandText dopo aver aggiunto i parametri.