My RecordSet always returns -1 in its RecordCount property

1

The select is done correctly, because when controlling the Fields property it shows me the names of the fields in the debugging. This is my code:

Dim RS_Personas As Object
Set RS_Personas = New ADODB.Recordset

Dim Consulta_SQL As String

If Nombre = "" Then
    Nombre = "%"
End If

If Apellido = "" Then
    Apellido = "%"
End If

Consulta_SQL = "SELECT * FROM Personas WHERE Nombre LIKE '%@Nombre%' AND Apellido LIKE '%@Apellido%'"

If IdPersona <> "" Then
    Consulta_SQL = Consulta_SQL & " AND IdPersona = @IdPersona"
End If

Dim Obj_Consulta As Object
Set Obj_Consulta = New ADODB.Command
Dim Obj_Conexion As Object
Set Obj_Conexion = New ADODB.Connection

Obj_Conexion.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=HKA_Test"
Obj_Conexion.CommandTimeout = 0
Obj_Conexion.Open

Obj_Consulta.CommandText = Consulta_SQL
Obj_Consulta.CommandType = adCmdText

Obj_Consulta.Prepared = True
'Agregamos parametros
Obj_Consulta.Parameters.Append (Obj_Consulta.CreateParameter("@Nombre", adVarChar, adParamInput, 255, Nombre))
Obj_Consulta.Parameters.Append (Obj_Consulta.CreateParameter("@Apellido", adVarChar, adParamInput, 255, Apellido))

If IdPersona <> "" Then
    Obj_Consulta.Parameters.Append (Obj_Consulta.CreateParameter("@IdPersona", adInteger, adParamInput, 4, IdPersona))
End If

Obj_Consulta.ActiveConnection = Obj_Conexion


RS_Personas.CursorLocation = adUseServer
RS_Personas.CursorType = adOpenStatic
RS_Personas.LockType = adLockReadOnly

Set RS_Personas = Obj_Consulta.Execute

Set Buscar = RS_Personas
    
asked by Marcos Gonzalez 01.08.2017 в 14:43
source

1 answer

1

That's a fairly common mistake and I do not think it has a simple solution. Microsoft same says it can return -1 depending on whether the cursor type or the database provider supports it or not. If you need to know the number of records you can do a SELECT COUNT , and if all you need is to go through the records, check Not rs.EOF

    
answered by 01.08.2017 в 15:47