VB & Oracle

oracle United States
  • 18 years ago

    Could anyone help me on this ?


    There are 1000s & 1000s of data on the Oracle database. Iam using a VB frontend to display them, but it is very very slow since the no. of records is huge


    I need to do a kind of Paging. The query should bring only 100 records at a time. If I click the "Next page" button, the query should go to the database again and bring the next 100 records. Any help ?


    I tried the Paging properties of the ADO object , but the delay is the same. It brings all the records at  the first time, but this is NOT what I want



    Thanks so much in advance

  • 18 years ago

    How abt  retreiving only first 100 records in the first screen and then retreiving the next 100 and so on by using RowNum ?


    is it still slow?

  • 18 years ago

    It may be slow regardless of what you do since each time ADO is accessing Oracle, it has to retrieve so much information, but you may want to try something like this (if you have a field in your table that is an incrementing number field):

    Code:
    Dim myConn as new ADODB.Connection
    Dim rst as new ADODB.Recordset
    Dim FieldLow as long
    Dim FieldHigh as long


    db.open "PROVIDER=MSDAORA.1; Data Source=MySID; User ID=user; Password=password;"


    Set rst.ActiveConnection = myConn


    fieldlow = -1  ' set this to the first number of your ID - 1, assumed 0 in this example
    fieldhigh = fieldlow + 100
    rst.Open "SELECT * FROM MyTable WHERE FieldID>" & FieldLow& " AND FieldID<=" & FieldHigh
    do until rst.eof and rst.bof
     'manipulate your recordset data for the specified interval
     rst.close
     fieldlow = fieldhigh
     fieldhigh = fieldlow + 100
     rst.open "SELECT * FROM MyTable WHERE FieldID>" & FieldLow & " AND FieldID<=" & FieldHigh
    loop


    rst.close
    myconn.close


    set rst = nothing
    set myconn= nothing

    I hope that helps you out a little bit.

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“My definition of an expert in any field is a person who knows enough about what's really going on to be scared.” - P. J. Plauger