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

I am beginning to program in VB.net 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.  
Thanks
Belinda
bsturgeAsked:
Who is Participating?
 
cubixSoftwareCommented:
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.Fill(dsCustomers)
da.selectCommand = selectProducts
da.Fill(dsProducts)
da.selectCommand = selectEmployees
da.Fill(dsEmployees)

End Sub

==============================



0
 
RJeyaPrakashCommented:
hai

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.



regards
jp
0
 
bsturgeAuthor Commented:
Could you please help me with the code to connect to a database and 3 tables?
0
 
cubixSoftwareCommented:
Hi

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
        myConnection.Open()

        ' 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")

============================
0
 
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.

0
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.