dbopensnapshot - VBA, ADO. Parámetros de conexión y consulta.

dbopendynaset

Question

Tengo excel script VBA:

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

El script funciona bien, pero quiero agregarle un parámetro. Por ejemplo, "where (parentid = myparam )", donde myparam se establece fuera de la cadena de consulta. ¿Cómo puedo hacerlo?

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

Answer 1

Debe utilizar un objeto ADODB.Command al que pueda agregar parámetros. Aquí está 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 el texto de comando es el marcador de posición para el parámetro. Creo, pero no estoy seguro, que el orden en que usted anexa los parámetros debe coincidir con el orden de las marcas de interrogación (cuando tiene más de una). No se deje engañar de que el parámetro se llame "parentid" porque no creo que ADO se preocupe por el nombre que no sea la identificación.

Answer 2

Ejemplo alternativo devolviendo un comando desde 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 a tener en cuenta:

1. Cuando se usa el tipo de datos adVarChar , se requiere el argumento de tamaño para cmd.CreateParameter (por ejemplo, 255). Si no se lo suministra, se produce un error 3708 en tiempo de ejecución: error definido por la aplicación o definido por el objeto, como se indica en la documentation :

   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, cmd.Parameters parámetros se cmd.Parameters consecuencia. Llamar a cmd.Parameters.Append después puede resultar en 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 documentation , que es ligeramente inexacta:

   Si la propiedad Preparada del objeto Comando se establece en Verdadero y el objeto Comando está enlazado a una conexión abierta cuando configura la propiedad ComandoTexto, ADO prepara la consulta (es decir, una forma compilada de la consulta almacenada por el proveedor) cuando llama a los métodos Ejecutar o Abrir.

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