Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100

    Unanswered: Need help with the most bizarre thing I've ever seen

    I wrote a function in VB to return the number of rows in a recordset since whenever I tried to use the .RecordCount property, I always got -1 back.
    Here's the function:

    Code:
    Public Function RecordsetSize(strSource)
    Dim rst As ADODB.Recordset
    Dim rstArray, rstcount As Variant
        Set rst = cnConn.Execute(strSource)
        If Not rst.EOF Then
            rstArray = rst.GetRows()
            rstcount = UBound(rstArray, 2) + 1
        End If
        RecordsetSize = rstcount
        rst.Close
        Set rst = Nothing
    End Function
    Then I realized that this function was too slow since I was always passing it the sources for open recordsets...I decided to re-write it and just pass it the recordset itself:

    Code:
    Public Function RecordsetSize2(rst As ADODB.Recordset) As Integer
        Dim rstArray, rstcount As Variant
        If Not rst.EOF Then
            rstArray = rst.GetRows()
            rstcount = UBound(rstArray, 2) + 1
        End If
        RecordsetSize2 = rstcount
    End Function
    The second function returns values just fine. I want to use it to count the size of result sets so that I don't have to hard code where they're pasted in Excel; I can use the size to place them without actually knowing how big they are. When I do this with the first function, it works fine. When I do it with the second one, it doesn't work.

    Code:
    Dim StartPosition as Integer
    StartPosition = 4
        Call MoveDataExcel(rst(0), xlSheet, StartPosition)
        StartPosition = StartPosition + RecordsetSize(strSource(0)) + 2
    When I do this, the value of StartPosition is 4, plus the size of the recordset...let's say it's 12 rows...then 2, making 18. When I use RecordsetSize2(rst(0)) + 2 instead of RecordsetSize(strSource(0)) + 2, StartPosition only increments by 2.

    I don't understand why. I've had several people in the office look at this and they can't figure it out either. Can anyone help?? I mean, I know I've asked some stupid questions before but this one is a real stumper, I think.

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    .Recordcount depends on the cursorlocation and the provider.

    You MUST use a client-side cursor. You can use the Supports method to determine if a provider supports a given feature in the recordset.

    Note that it's a LOT faster to get the record count using the SQL Count function rather than retrieving an entire recordset just to count it.

    Code:
    Select Count(*) from TableName...
    Finally, as far as your question.
    When I use RecordsetSize2(rst(0)) + 2 instead of RecordsetSize(strSource(0)) + 2, StartPosition only increments by 2.
    It's pretty simple. The first function accepts a string containing the SQL code.

    The second function accepts the recordset itself. But, rst(0) is NOT a recordset. rst is a recordset. rst(0) is the shortcut for rst.fields(0).Value. You are passing the contents of a field to the function and not the recordset. And, somewhere in the code leading up to this, you've probably disabled error reporting (On Error Resume Next) which is NOT a good thing.

    btw. Using variants instead of declared data types is a lot less efficient.

    Code:
    Dim rstcount As Variant, rstArray as Long
    also a side note.

    With VB6,
    Code:
    Dim N, I as Long
    causes I to be declared as a long, but N is declared as a variant. In order to specify both as longs, you need to
    Code:
    Dim N as Long, I as Long
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •