select with join into a dataset (1 Viewer)

[PhilBear]

I have a database with multipe tables in Access. I am using VB.NET to create a front end to the database.

I need to be able to do a select on one or more tables.

Here is a select statement that I created in MS Access to show all the alternate names for the herb "clove".

SELECT HerbOilName.CommonName, AlternateNameTable.AlternameName FROM HerbOilName INNER JOIN AlternateNameTable ON HerbOilName.Index = AlternateNameTable.CommonNameLink WHERE (((HerbOilName.CommonName)="clove"));

How do I get this select statement into a dataset in vb.net that I can manipulate?

 

[skea]

Pretty simple,
1) create a connection to the database
2) instantiate a new dataset
2) instantiate a new dataAdapter
3)Open The connection
4) fill to the dataset with the resultset of your SQL Statement.
5) Close and dispose your connection and dataAdapter.

If you are new to this, there many threads here that have examples or links to examples or .NET tutorials.

 

[PhilBear]

If you know of a message with an example of this please provide a link or message number.

 

[skea]

Here is a simple way to do it.

Dim conn As OleDbConnection = New OleDbConnection(strOleDbConnection)
        Dim strSQl As String = "YOUR SQL Statement Here"
        Dim da As OleDbDataAdapter = New OleDbDataAdapter(strSQL, conn)
        Dim ds As DataSet = New DataSet
        Try
            conn.Open()
            da.Fill(ds, myTable)
        Catch exOle As OleDbException
            MessageBox.Show(exOle.Message, "SQL Error:")
        Catch ex As Exception
            MessageBox.Show(ex.Message, "General Error:")
        Finally
            conn.Close()
            conn.Dispose()
            da.Dispose()
        End Try