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

Posted on 2008-11-10
Medium Priority
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

ElrondCT earned 2000 total points
ID: 22931909
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

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

571 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