none
RecordCount always returns -1 RRS feed

  • Question

  • Hello to all.

    I'm using the following in Excel 2007 code to retrieve data from an Access 2007 database:

    Public Function VeCustPer(Telf As String, mes As String) As Variant
        On Error GoTo erro
        Dim aux As String
        Dim rstph As ADODB.Recordset
        Dim phCmd As ADODB.Command
        Dim phPar1 As ADODB.Parameter
        Dim phPar2 As ADODB.Parameter
            
        Set rstph = New ADODB.Recordset
        Set phCmd = New ADODB.Command
        Set phPar1 = New ADODB.Parameter
        Set phPar2 = New ADODB.Parameter
        
        phCmd.CommandType = adCmdStoredProc
        phCmd.ActiveConnection = PhoneCnn
        phCmd.CommandText = "qryCustoPer"
        
        Set phPar1 = phCmd.CreateParameter
        Set phPar2 = phCmd.CreateParameter
               
        phPar1.Type = adChar
        phPar2.Type = adChar
        
        phPar1.Size = Len(Telf)
        phPar2.Size = Len(mes)
               
        phCmd.Parameters.Append phPar1
        phCmd.Parameters.Append phPar2
        
        phPar1.Value = Telf
        phPar2.Value = mes
        
        rstph.Open phCmd
       
        MsgBox rstph.RecordCount
        
        If rstph(0) Is Null Then
            VeCustPer = "Sem registos"
        Else
            VeCustPer = CDbl(rstph(0).Value)
        End If
        
    erro:     aux = MsgBox(CStr(Err.Number) & ":" & Err.Description, vbOKOnly, "ERRO --> " & Err.Source)
    End Function

    The query which populates the recordset is stored in the database and it is an SQL SUM.  My problem is that the recordset RecordCount property always returns -1 in spite of the query result (when the sum returns an amount or when it retuns null - meaning that there is no data). How can i deal with this? I would like the function only to return an amount when the result is not null. I also tried evaluating:

    rstph(0).Value

    but it returns "Invalid use of null"

    Thanks in advance for any kind help.

    Octavio

    Tuesday, July 3, 2012 11:04 AM

Answers

  • OK, in that case try IsNull(...) instead of ... Is Null:

        If IsNull(rstph(0).Value) Then
            VeCustPer = "Sem registos"
        Else
            VeCustPer = CDbl(rstph(0).Value)
        End If


    Regards, Hans Vogelaar

    • Marked as answer by octeixeira Tuesday, July 3, 2012 2:08 PM
    Tuesday, July 3, 2012 1:59 PM

All replies

  • Unless you explicitly specify a cursor type, ADO will open a recordset as a forward-only cursor. The record count for a forward-only cursor will always be -1.

    To check whether a recordset has records, inspect its EOF ("End Of File") property. Immediately after opening the recordset, EOF will be False if there are records and True if there aren't.

        If rstph.EOF = True Then
            VeCustPer = "Sem registos"
        Else
            VeCustPer = CDbl(rstph(0).Value)
        End If


    Regards, Hans Vogelaar

    Tuesday, July 3, 2012 11:57 AM
  • Hello.

    Thanks you for answering. I already tried that but EOF and BOF is False in both cases. No matter what the query returns (remember that it is a SUM, so it has maximum one record). It seems the only chance i have it to look at:

    rstph(0).Value

    But when i do that i get the message "Invalid use of null"

    Thanks

    Octavio


    • Edited by octeixeira Tuesday, July 3, 2012 1:42 PM
    Tuesday, July 3, 2012 1:41 PM
  • OK, in that case try IsNull(...) instead of ... Is Null:

        If IsNull(rstph(0).Value) Then
            VeCustPer = "Sem registos"
        Else
            VeCustPer = CDbl(rstph(0).Value)
        End If


    Regards, Hans Vogelaar

    • Marked as answer by octeixeira Tuesday, July 3, 2012 2:08 PM
    Tuesday, July 3, 2012 1:59 PM
  • HaHa my dear Hans! You save my day.

    Thanks a million!

    Octavio

    Tuesday, July 3, 2012 2:07 PM