coding to connect to a multiple tables in a database using sql

I am beginning to program in having used VB6 in the past, and need to connect to multiple tables using sql statements with joins.    How can I code these, and do I need a separate data adapter for each table or can I use one for the database and declare the dataset for each table.  
Who is Participating?
No you do not need a data adapter per dataset, in fact the data adapter is only used as a mechanism to load the data into your dataset and should only be in scope during that procedure.

To fill data into the dataset you specify a select command to run for the data adapter


Private Sub  BuildDatasets()

dim selectCustomers as sqlCommand
dim selectProducts as sqlCommand
dimEmployees as sqlCommand
dim da as dataadapter
dim dsCustomers as dataset
dim dsProducts as dataset
dim dsEmployees as dataset

' do something here to build up the customer select command
' do something here to build up the products select command
' do something here to build up the employees select command

da.selectCommand = selectCustomers
da.selectCommand = selectProducts
da.selectCommand = selectEmployees

End Sub



u can use a single dataadapter for selecting rows from multiple tables. And u can also create single table in a dataset for all the columns u need from multiple tables.

bsturgeAuthor Commented:
Could you please help me with the code to connect to a database and 3 tables?

If it is to an access database then you can do the following

        Dim da As New OleDbDataAdapter
        Dim selectCmd As New OleDbCommand
        myConnection.ConnectionString = connString

        ' define the select command
        With selectCmd
            .Connection = myConnection
            .CommandText = "Select * From Customers As c Inner Join Orders As o On c.CustomerID = o.CustomerID"
            .CommandType = CommandType.Text
        End With

        ' fill dataset
        With da
            .SelectCommand = selectCmd
            .Fill(ds, "Customers")
        End With

        ' display in the grid
        DataGrid1.DataSource = ds.Tables("Customers")

bsturgeAuthor Commented:
One thing I am not clear about is to work with 3 datasets, do I need a data a data adapter for each.  For instance if I need to view the Customers dataset and the Products dataset and the Employees from the same database.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.