• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

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
0
bsturge
Asked:
bsturge
  • 2
  • 2
1 Solution
 
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
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now