VB.NET. How to random read a record in a database knowing the record number?

I have an external DBase IV file created and continually updated by 3rd party software.  Using OLEDB I can sequentially read through the DB all ok.   see attached code snippet.
Question 1: Knowing  a record number, how can I immediately jump to and read that record?  
Question 2: How can I read through the database using an index?  I have a *.MDX file for each data file which looks like it contains 10 indexes.  How can I use these index files?  If I could scan the index then jump to the record, Q 1 above would be answered.
Question 3: How can I write into (over) a field in a record immediately after reading that record?
I suspect some of the answers entail learning SQL.

Thanks experts.  This is a great website!  If you can insert into my code snippet that would be great.  Or new code is fine too.

Option Strict Off
Option Explicit On
Imports System
Imports System.IO
Imports System.Text.RegularExpressions
Imports VB = Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports System.Data.OleDb
   Private Sub ConnectdBaseDataReader()
        'Dim sName As String = "myDataBase"
        Dim f As String = sName & ".dbf"
        Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & sDb & _
                ";Extended Properties=dBase IV"
        Dim dBaseConnect As New System.Data.OleDb.OleDbConnection(ConnectionString)
        Dim dBaseCommand As New System.Data.OleDb.OleDbCommand("SELECT * FROM " & f, dBaseConnect)
        Dim out As String = homeDir & "testDB.txt"
        FileOpen(1, out, OpenMode.Output)
        PrintLine(1, "Start")
        Dim i, k As Short
        Dim rs As System.Data.OleDb.OleDbDataReader = dBaseCommand.ExecuteReader()
        For i = 0 To rs.FieldCount - 1
            PrintLine(1, rs.GetName(i).ToString & " " & rs.GetDataTypeName(i).ToString)
        Next i
        Do While rs.Read()
            For k = 0 To rs.FieldCount - 1
                PrintLine(1, k & ":" & rs.GetName(k).ToString & ":" & rs(k).ToString)
            Next k
    End Sub

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You're going to need to change from using an OleDbDataReader to an OleDbDataAdapter, and load the data into a dataset. A DataReader can only go forward row by row through a database, with read-only access; a DataAdapter provides read-write and random access capabilities.

It's best, if you know the structure of the incoming database, to create a dataset and datatable first in the proper structure to receive the data. If dsDBase is the dataset and dtDBase is the datatable, you would then create an instance, such as

Dim dsDBaseA as New dsDBase

then load the data:


Now you can retrieve row n by "dsDBaseA.dtDBase(n)", and if you've predefined the structure so that you have a strongly typed dataset, you can reference individual fields such as

strTest = dsDBaseA.dtDBase(n).fldTest

You can also make changes to the datatable and save it back to the original database:

dsDBaseA.dtDBase(n).fldTest = "New data here"

I don't know if there's any way to use the MDX indexes, unfortunately.

For future reference, the zone you want to use for VB .NET programming is "MS Visual Basic"; the "VB Database Programming" zone is actually intended for VB 6.0, so many of the .NET experts don't check messages there. It's unfortunate that EE doesn't label the zones a little more clearly.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brm88Author Commented:
Thanks much for steering me to OleDbDataAdapter!  That's exactly what I needed.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.