Access database usage question

[Terry Maguire]

I am an old VB6 programmer, and we have recently gone to VB.NET. I am struggling to duplicate some of the things I had done in VB5 and VB6 to the new language.

What I need to do is to use VB.NET code to :

#1: Open an MS Access database
#2: Retrieve the data from one of the tables into an array
#3: Once I have the array filled, I can process the data in the array via VB.NET code

If my DB is ddddd and my table within the DB is ttttt, what code do I need to open the database and read each member from the table sequentially until the end of the table?

Thanks for answering.

 

[Earl]

A connection, a datareader, and an array. Here's an example using a combo,
but you could do the same with an array:

Dim strSelect as String = "SELECT ...."

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
DB.Name & ";"
Dim connDB As OleDbConnection = New OleDbConnection(strConn)

Dim cmd As New OleDbCommand(strSelect, connDB)
Dim dr As OleDbDataReader

connDB.Open()
dr = cmd.ExecuteReader()

While (dr.Read())
Dim strResult As String
If Not dr.IsDBNull(0) Then strResult = dr.GetString(0)
If strResult <> "" Then
cmbResults.Items.Add(strResult)
End If
End While

dr.Close()

connDB.Close()

Me.Close()

I am an old VB6 programmer, and we have recently gone to VB.NET. I am
struggling to duplicate some of the things I had done in VB5 and VB6 to the
new language.

What I need to do is to use VB.NET code to :

#1: Open an MS Access database
#2: Retrieve the data from one of the tables into an array
#3: Once I have the array filled, I can process the data in the array via
VB.NET code

If my DB is ddddd and my table within the DB is ttttt, what code do I need
to open the database and read each member from the table sequentially until
the end of the table?

Thanks for answering.

 

[Bernie Yaeger]

Hi Terry,

I'd recommend using a dataset/datatable and you can then disregard an array, as you can read through the dataset (it is, essentially, an array of data in memory, and modifiable back to the backend, if you so choose). For this you should use an oledb data adapter. You can use the connection string that Earl displayed, but you'll need to set the selectcommand of the data adapter and then use the fill method. Take a look at google or msdn for some of the fairly easy details of this. Here's a simple example (where a connection object has already been established):
Dim datblarchives As New OleDbDataAdapter("select * from tblarchives where id = " & Chr(39) & glformidnum & Chr(39), OleDbConnection1)

Dim dstblarchives As New DataSet("tblarchives")

datblarchives.Fill(dstblarchives, "tblarchives")

HTH,

Bernie Yaeger

I am an old VB6 programmer, and we have recently gone to VB.NET. I am struggling to duplicate some of the things I had done in VB5 and VB6 to the new language.

What I need to do is to use VB.NET code to :

#1: Open an MS Access database
#2: Retrieve the data from one of the tables into an array
#3: Once I have the array filled, I can process the data in the array via VB.NET code

If my DB is ddddd and my table within the DB is ttttt, what code do I need to open the database and read each member from the table sequentially until the end of the table?

Thanks for answering.

 

[Cor Ligthert]

Terry,

In addition to Earl and Bennie.

A Dataset is an object that holds datatables. You can compare Datatables
with recordset, although the major difference is that datasets/datatables
are disconnected and a recordset stays connected to the database.

To go through a datatable as with a movenext from a recordset can be.

dim mytable as datatable = ds.tables(0) ' you can do it with the dataset
direct as well
dim mydatarow as datarow1 = mydatatable.rows(0) ' which is the first row it
it extist
did mydatarow as datarow2 = mydatatable.rows(1) ' which is the second row if
it exist

(In a lot of cases is the currencymanager used, that has nothing to do with
money however with the current position in the datatable)

Both the datareader and datasets are good to use.

When you use it in connection with a "data" control as the datatagrid,
combobox, and listbox or with a "single item" control as the textbox,
checkbox, label or whatever, than the dataset/datatable is in my opinion
the best choise.

When you use it with a control as a treeview or a listview that has to be
loaded in advance, the datareader is in my opinion a better choise.

I hope this gives some more ideas?

Cor

"Terry Maguire" (e-mail address removed)

I am an old VB6 programmer, and we have recently gone to VB.NET. I am
struggling to duplicate some of the things I had done in VB5 and VB6 to the
new language.

What I need to do is to use VB.NET code to :

#1: Open an MS Access database
#2: Retrieve the data from one of the tables into an array
#3: Once I have the array filled, I can process the data in the array via
VB.NET code

If my DB is ddddd and my table within the DB is ttttt, what code do I need
to open the database and read each member from the table sequentially until
the end of the table?

Thanks for answering.