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

Posted on 2006-05-19
Last Modified: 2010-04-23
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.  
Question by:bsturge
    LVL 4

    Expert Comment


    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.


    Author Comment

    Could you please help me with the code to connect to a database and 3 tables?
    LVL 6

    Expert Comment


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


    Author Comment

    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.

    LVL 6

    Accepted Solution

    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



    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
    If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now