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

Posted on 2008-11-10
Last Modified: 2013-12-20
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

Question by:brm88
    LVL 20

    Accepted Solution

    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.

    Author Closing Comment

    Thanks much for steering me to OleDbDataAdapter!  That's exactly what I needed.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
    If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now