code to read in Access tables to a dataset

ruairi · February 11th, 2005, 12:03 PM

Hi.

I'm completely new to VB.Net and v. limited in VB!

Can someone point give me a simple piece of code to read data from several tables in a db called SUBMAN into a Dataset and transfer that to a textbox?

thanks in advance.

Here's as far as I've got!

    Private Sub btnCountry_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCountry.Click

        Dim DS As System.Data.DataSet
        Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
        Dim MyConnection As System.Data.OleDb.OleDbConnection

        ' connect to the database
        MyConnection = New OleDbConnection( _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & "c:\DB\SUBMAN.mdb" & ";")
        MyConnection.Open()

        Dim sqlstring As String
        ' create the sqlstring ---- how do I get the command to use it?

        sqlstring = "SELECT title, firstname, lastname, email, town, postcode, country"
        sqlstring &= " FROM SUBSCRIBERS, ADDRESSES WHERE country = " & "'" & ComboCountry.Text & "'"

        DS = New System.Data.DataSet()
        MyCommand.Fill(DS)
        MyConnection.Close()

        'process each row

        ' Clear the variable

        'get the data in each column

        ' remove trailing coma

        ' Write the data to the text box
        txtResults.Text &= ' datafrom dataset

        ' close the conection and clean up
        MyConnection.Close()
        MyConnection.Dispose()
        MyConnection = Nothing
        DS.Dispose()
        DS = Nothing
        MyCommand.Dispose()
        MyCommand = Nothing

    End Sub

mehdi62b · February 11th, 2005, 02:18 PM

Quote:

quote: MyConnection.Dispose()
        MyConnection = Nothing
        DS.Dispose()
        DS = Nothing
        MyCommand.Dispose()
        MyCommand = Nothing

1-why you had such lines in your code,remember that in .NET all such task accomplished by Garbage Collector
2-for DataSet there is no need for opening the connection(Fill method would open and close the connection),

Code:

Dim dstAuthors As DataSet
Dim conAuthors As OleDbConnection
Dim dadAuthors As OleDbDataAdapter

dstAuthors = New DataSet()
conAuthors = New OleDbConnection( "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=c:\Authors.mdb" )
dadAuthors = New OleDbDataAdapter( "Select * From Authors", conAuthors )
dadAuthors.Fill( dstAuthors, "Authors" )

then you can work on the DataSet and go on ..

_____________
Mehdi.
software student.

ruairi · February 11th, 2005, 02:34 PM

Hi Mehdi,

thanks for the tips, can you help again?

How do I transfer the contents of the dataset DS to the text box txtResults?

I hope you can help again, thanks again,

Ru.

mehdi62b · February 11th, 2005, 06:10 PM

you can get every cell in your DataSet,

Code:

CType(DS.Tables([TableNumber or TableName]).Rows([RowNumber])([ColumnNumber or ColumnName]),String)

look into MSDN.

_____________
Mehdi.
software student.

ruairi · February 12th, 2005, 06:03 PM

Thanks again Mehdi,

you're magic! I'll just go and try all that now!

Ru.