Consulta SQL usando variable

Question

Hola, como puedo hacer una consulta sql usando parametros

Public Function BuscarClienteXId(codigo As String) As ADODB.Recordset
  Dim rs As ADODB.Recordset
  Dim strSQL As String
  Dim cmd As New Command
  Conectar
  strSQL = "SELECT cliRuc, cliRazonSocial, cliCiudad, cliDireccion, cliTelefono, cliCelular WHERE cliCodigo=@codigo FROM Cliente"
  cmd.Parameters.Append cmd.CreateParameter("@codigo", adVarChar, adParamOutput, 6, codigo)
  Set rs = New ADODB.Recordset
  rs.Open strSQL, cnMDB, adOpenStatic, adLockOptimistic
  Set BuscarClienteXId = rs
End Function

---

Pedro Avila

Answer 1

Hola

Prueba de esta forma

Public Function BuscarClienteXId(codigo As String) As ADODB.Recordset

    Dim strSQL as string
    Dim cmd As New ADODB.Command

    strSQL = "SELECT cliRuc, cliRazonSocial, cliCiudad, cliDireccion, cliTelefono, cliCelular FROM Cliente WHERE cliCodigo=@codigoend" 
    cmd.CreateParameter("@codigo", adVarChar, adParamOutput, 6, codigo)
    
    Set BuscarClienteXId = cmd.Execute
    
End Function

Answer 2

Me da error me dice debe declarar la variable escalar @Codigo

---

Pedro Avila

Answer 3

Hola

e modificado y sin querer he borrado lineas

    Public Function Consulta(ByVal codigo As String) As ADODB.Recordset
        Dim sql As String
        Dim cmd As New ADODB.Command
 
        sql = "SELECT campo1 FROM tabla WHERE campo=@campo"
        cmd.CommandText = sql
        cmd.ActiveConnection = conexion
 
        cmd.Parameters.Append (cmd.CreateParameter("@campo", adVarChar, adParamOutput, 6, codigo))

        Consulta = cmd.Execute
    End Function

Answer 4

Me dice que la conexion esta cerrada

Public Function BuscarClienteXId(codigo As String) As ADODB.Recordset
'  Dim rs As ADODB.Recordset
  Dim cmd As New Command
  Dim strSQL As String
'  Conectar
  
  strSQL = "SELECT cliRuc, cliRazonSocial, cliCiudad, cliDireccion, cliTelefono, cliCelular FROM Cliente WHERE cliCodigo=@codigo"
  cmd.CommandText = strSQL
  cmd.ActiveConnection = cnMDB
'  cmd.CommandType = adCmdText
  
  cmd.Parameters.Append cmd.CreateParameter("@codigo", adVarChar, adParamOutput, 6, codigo)
'  Set rs = New ADODB.Recordset
'  rs.Open strSQL, cnMDB, adOpenStatic, adLockOptimistic
'  Set rs = cmd.Execute
  Set BuscarClienteXId = cmd.Execute
End Function

---

Pedro Avila

Answer 5

y si abres la conexion

Public Function BuscarClienteXId(codigo As String) As ADODB.Recordset
  Dim cmd As New Command
  Dim strSQL As String
  
  Conectar
  
  strSQL = "SELECT cliRuc, cliRazonSocial, cliCiudad, cliDireccion, cliTelefono, cliCelular FROM Cliente WHERE cliCodigo=@codigo"
  cmd.CommandText = strSQL
  cmd.ActiveConnection = cnMDB
  
  cmd.Parameters.Append cmd.CreateParameter("@codigo", adVarChar, adParamOutput, 6, codigo)
  Set BuscarClienteXId = cmd.Execute
  
End Function

Answer 6

De esa manera me dice que debo de declarar la variable @codigo, creo que mejor esto lo hago de la manera que se, pq de esta manera no puedo hacerlo se que se puede pero no se como

---

Pedro Avila