Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    33

    Unanswered: display huge table in VB form

    In VB form, I have to display a huge table from SQL DB.

    It could not display by Flex Grid because the Flex Grid has limitation problem(can't more then 350,000 cells).
    So I am thinking to use webbrowser to display table in html page.
    I believe that using the html page table, the limitation problem will gone. However, when I am doing the test, the speed is tooo slow. The following is my code. Is there any one can give me any idea. Maybe html isn't a good choice?

    <code>
    Private Sub Command1_Click()
    Dim rsCon As New ADODB.Connection
    Dim rsLocal As New ADODB.Recordset
    Dim fso As New FileSystemObject
    Dim str As String
    filenum = FreeFile
    str = "<html>"
    str = str + "<body><h1>Display Huge Table</h1>"
    str = str + "<table><tr><th>UserId</th><tr>"

    Open "2.html" For Output As filenum

    rsCon.Open "DSN", "", ""
    rsLocal.Open "select top 20000 [Phone] from PhoneNumber", rsCon, adOpenKeyset, adLockOptimistic
    While Not rsLocal.EOF
    str = str + "<table><tr><td>" + rsLocal(0) + "</td><tr>"
    rsLocal.MoveNext
    Wend
    str = str + "</table></body></html>"
    Print #filenum, str
    rsLocal.Close
    Set rsLocal = Nothing
    Close filenum
    WebBrowser1.Navigate App.Path + "\2.html"

    End Sub

    Private Sub Form_Load()

    WebBrowser1.Navigate App.Path + "\2.html"
    End Sub
    </code>

  2. #2
    Join Date
    Nov 2004
    Posts
    108

    maybe is your network

    Is good to reduce the amount of round trips in the network but requesting a large amount of data at once (depending on the network specifications and traffic within network) is not the best option always.

    • Try setting recordset properties to adOpenForwardOnly, adLockReadOnly and the rsLocal.CursorLocation=adUseClient and/or
    • Try requesting less data at the time
    Code:
    For recAmount = 4000 To 20000 Step 4000
        rsLocal.Open "select top 4000 [Phone] from PhoneNumber Where [Phone]>'" & _
            lastPhone & "' Order by [Phone]", rsCon, adOpenForwardOnly, adLockReadOnly
        While Not rsLocal.EOF
            Str = Str + "<table><tr><td>" + rsLocal(0) + "</td><tr>"
            lastPhone = rsLocal!Phone
            rsLocal.MoveNext
        Wend
    Next
    EDIT: The top clause is meaningless unless there is an order by clause so I edited the select statement
    Last edited by tuenty; 11-24-04 at 10:15.
    to err is human ; to really mess things up requires a computer

  3. #3
    Join Date
    Feb 2004
    Posts
    33
    Thank tuenty for replying. But I am afraid I have to display the whole records at the same time in some case. It depends on the user's requirement.
    But you do give me a hint, using grids but display about 1000 records as once. and make the user to choose next grid.

    I kept wondering how come using html page table is so slow. I tested for 20,000 records and it took about 5-10 mins to load the table.

    Quote Originally Posted by tuenty
    Is good to reduce the amount of round trips in the network but requesting a large amount of data at once (depending on the network specifications and traffic within network) is not the best option always.

    • Try setting recordset properties to adOpenForwardOnly, adLockReadOnly and the rsLocal.CursorLocation=adUseClient and/or
    • Try requesting less data at the time
    Code:
    For recAmount = 4000 To 20000 Step 4000
        rsLocal.Open "select top 4000 [Phone] from PhoneNumber Where [Phone]>'" & _
            lastPhone & "'", rsCon, adOpenForwardOnly, adLockReadOnly
        While Not rsLocal.EOF
            Str = Str + "<table><tr><td>" + rsLocal(0) + "</td><tr>"
            lastPhone = rsLocal!Phone
            rsLocal.MoveNext
        Wend
    Next

  4. #4
    Join Date
    May 2004
    Posts
    97
    This isn't going to help you out on the overall speed of dispaying the entire recordset, but you can keep the user for committing suicide while the html page loads.

    When you do a table in html, the table will not show anyhing until the table is comleted. So, your users wait..and wait...and wait. But, if your html page was composed of tables of 100, or even 500 or 1000 records, as each table is completed it is displayed. That way, the users can see and scroll through the first records whilt the last are still being retrieved from the database.

    Just a little code modification to do that. Then, you may have to do manual width columns instead of auto width because each table will auto adjust to the data within the cells.

    Just something for you to possible use to ease the pain while you find a real solution.

  5. #5
    Join Date
    May 2004
    Posts
    97
    And I thought the MSFlexGrid control was only limited to the amount of memory in the computer the program runs on....

Posting Permissions

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