VBA, ADO.Connection y parámetros de consulta

Tengo el script de Excel VBA:

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

La secuencia de comandos funciona bien, pero quiero agregarle un parámetro. Por ejemplo “where (parentid = myparam )”, donde myparam se estableció fuera de la cadena de consulta. ¿Cómo puedo hacerlo?

Por supuesto, puedo modificar la cadena de consulta, pero creo que no es muy inteligente.

Necesita usar un objeto ADODB.Command al que puede agregar parámetros. Esto es básicamente lo que parece

 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 

El signo de interrogación en CommandText es el marcador de posición para el parámetro. Creo, pero no estoy seguro, que el orden en el que se agregan los parámetros debe coincidir con el orden de los signos de interrogación (cuando tiene más de uno). No se deje engañar por el hecho de que el parámetro se llame “parentid” porque no creo que ADO se preocupe por el nombre más que por su identificación.

Ejemplo alternativo que devuelve un comando de una función:

 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 par de cosas para tener en cuenta:

  1. Cuando se utiliza el tipo de datos adVarChar , se requiere el argumento de tamaño para cmd.CreateParameter (por ejemplo, 255). Al no proporcionarlo, se genera un error 3708 en tiempo de ejecución: error definido por la aplicación o definido por el objeto, como se indica en la documentación :

    Si especifica un tipo de datos de longitud variable en el argumento Tipo, debe pasar un argumento Tamaño o establecer la propiedad Tamaño del objeto Parámetro antes de agregarlo a la colección Parámetros; de lo contrario, se produce un error.

  2. Si la propiedad cmd.ActiveConnection se establece cuando se establece cmd.CommandText y cmd.CommandText contiene parámetros con nombre, los cmd.Parameters se completarán en consecuencia. Llamar a cmd.Parameters.Append luego podría dar como resultado duplicados. Por ejemplo:

     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 

    Creo que esto es lo que se entiende en la documentación , que es ligeramente inexacta:

    Si la propiedad Preparado del objeto Command se establece en True y el objeto Command está vinculado a una conexión abierta cuando establece la propiedad CommandText, ADO prepara la consulta (es decir, una forma comstackda de la consulta almacenada por el proveedor) cuando llamas a los métodos Ejecutar o Abrir.

    Como solución cmd.CommandText , establezca cmd.CommandText o cmd.ActiveConnection después de agregar parámetros.