Connection String to a DataSet RRS feed

  • Question

  • In Visual Studio 2008, I’ve created a DataSet that includes several tables:


        Dim CW_Dataset As New DataSet("CW")


        Dim CW_tbl_Parts As DataTable = CW_Dataset.Tables.Add("CW_tbl_Parts") 

        Dim CW_tbl_Cabinets As DataTable = CW_Dataset.Tables.Add("CW_tbl_Cabinets")

        Dim CW_tbl_Part_Classes As DataTable = CW_Dataset.Tables.Add("CW_tbl_Part_Classes")


    How do I create a connection to access these tables as if they were all part of, for example, a MS Access database?  I would like to be able to use SQLs to retrieve information from these tables to create reports (among other things).


    Here’s my connection for accessing an MS Access database:


        Using con_DB As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Test.mdb”)


    Is there an equivalent connection method for accessing the DataSet and its’ Tables I’ve created in Visual Studio 2008?


    Thank you in advance for your help!



    Thursday, November 5, 2009 4:24 PM

All replies

  • You could just add the DataTables as you call the Fill command to fetch the data:

           ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=..."
            Dim AccessConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
            AccessDataAdapter = New System.Data.OleDb.OleDbDataAdapter
            AccessDataAdapter.SelectCommand = New System.Data.OleDb.OleDbCommand("SELECT * FROM CW_tbl_Parts", AccessConnection)
            CW_Dataset = New DataSet("CW")
            AccessDataAdapter.Fill(CW_Dataset, "CW_tbl_Parts")

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, November 5, 2009 6:13 PM
  • Paul -

    Thanks for the feedback - unfortunately, I'm still a little confused.

    Do the following DIM statements create a new dataset (CW) with three tables (CW_tbl_Parts, CW_tbl_Cabinets, and CW_tbl_Part_Classes) in memory that is equivalent to a MS Access database (CW.mdb) with three tables (same names as above) on a hard drive?  My program fills these tables via SQLs and Reader functions from existing MS Access tables.Your response seems very similar to my method for populating the DataTables - does it do something more to associate the DataTables with the DataSet that I'm missing?

        Dim CW_Dataset As New DataSet("CW")

          Dim CW_tbl_Parts As DataTable = CW_Dataset.Tables.Add("CW_tbl_Parts")
          Dim CW_tbl_Cabinets As DataTable = CW_Dataset.Tables.Add("CW_tbl_Cabinets")
          Dim CW_tbl_Part_Classes As DataTable = CW_Dataset.Tables.Add("CW_tbl_Part_Classes")

    My problem is that I need to run an SQL against tables in several MS Access databases.  I felt the easiest way was to create a DataSet with DataTables, then use a single SQL against the DataTables within a single DataSet.  The SQL is rather large and doing multiple SQLs against multiple databases, then combining the results doesn't seem attractive.  I also have to run the SQL(s) multiple times as the user changes selections in comboboxes - doing this with DataTables rather than databases has got to be a lot quicker.

    I'm real comfortable using the following structure for filling DataTables via SQLs from databases, and I would like to use the same approach with the DataSet.
        Using con_DB As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Test.mdb”)

        End Using

    Is there a similar structure I can use to create a new DataTable from the "CW_tbl..." tables I've already defined?

    Thanks for your suggestions!

    Thursday, November 5, 2009 9:17 PM
  • Well you can't return the data for each one of your tables by executing a single call to the Access database, if that is what you're attempting to do. There is no support for returning multiple resultsets as there is in SQL Server. You can return a single resultset consisting of multiple tables through a join or union query but you can't automagically split the data up into multiple DataTables. Am I misunderstanding your issue?

    The code I posted returns the data for a single database table, creates a corresponding DataTable in the DataSet and fills it with the data as specified in the SQL statement. You would need to do this for each table, changing the connection and SQL statement as necessary. 

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, November 6, 2009 1:06 AM
  • Paul –


    I apologize if I’m not making my question clear.


    I am not trying to return the data for each of my DataSet DataTables all at once by executing a single call to the Access database.  I understand that concept, and I am very comfortable creating DataTables by using SQLs to populate these DataTables.  The DataTables I have listed in my “example” (CW_tbl_Parts, CW_Tbl_Cabinets, CW_tbl_Part_Classes) have already been created in VB and populated using SQL queries on MS Access databases and tables.  Using VB, I can retrieve information from these DataTables while totally disconnected from any MS Access databases.  I’m trying to get beyond this point.


    I understand what the code does that you posted.  It does exactly what I’m already doing in VB.


    I was trying to avoid posting pieces of my code ‘cause it’s somewhat lengthy, but perhaps I should.  The following code (cut-and-pasted, so a few pieces may be missing) is what I am using to populate a DataTable (CW_Table) from multiple tables in a single MS Access database:


        Dim CW_Dataset As New DataSet("CW")

        Dim CW_Table As DataTable = CW_Dataset.Tables.Add("CW_Table")


            With CW_Table


                .Columns.Add("Class Name", GetType(String))

                .Columns.Add("Class Description", GetType(String))

                .Columns.Add("Cabinet Type", GetType(String))

                .Columns.Add("Std No", GetType(Integer))

                .Columns.Add("Standard Description", GetType(String))

                .Columns.Add("Visited (A)", GetType(String))

                .Columns.Add("Option Selected (A)", GetType(String))

                .Columns.Add("Variable (A)", GetType(String))

                .Columns.Add("Value (A)", GetType(Single))

                .Columns.Add("Unit (A)", GetType(String))

                .Columns.Add("Visited (B)", GetType(String))

                .Columns.Add("Option Selected (B)", GetType(String))

                .Columns.Add("Variable (B)", GetType(String))

                .Columns.Add("Value (B)", GetType(Single))

                .Columns.Add("Unit (B)", GetType(String))


            End With


            Dim str_Select As String


            str_Select = "SELECT tblStandards_3_5.CabinetID, tblCabinets_3_5.Name, tblPartClasses_3_5.ClassName, tbl_Parts_3_5.Description, " & _

                                "tblStandards_3_5.StandardNum, Int([tblStandards_3_5.StandardNum]/1000) AS Cab_Type, " & _

                                "IIf(Int([tblStandards_3_5.StandardNum]/1000)=2,'Standard 45', " & _

                                    "IIf(Int([tblStandards_3_5.StandardNum]/1000)=4,'Standard 90', " & _

                                    "IIf(Int([tblStandards_3_5.StandardNum]/1000)=6,'Wall', " & _

                                    "IIf(Int([tblStandards_3_5.StandardNum]/1000)=8,'Wall 45', " & _

                                    "IIf(Int([tblStandards_3_5.StandardNum]/1000)=10,'Wall 90', " & _

                                    "IIf(Int([tblStandards_3_5.StandardNum]/1000)=12,'Tall','Standard')))))) AS Cab_Type_Name, " & _

                                "tbl_CIW_Cabinet_Wizard_3_5.Standard_Description, tblStandards_3_5.Visited, " & _

                                    "Choose([tblstandards_3_5.button]+1,[tbl_CIW_Cabinet_Wizard_3_5.button_0], " & _

                                        "[tbl_CIW_Cabinet_Wizard_3_5.button_1],[tbl_CIW_Cabinet_Wizard_3_5.button_2], " & _

                                        "[tbl_CIW_Cabinet_Wizard_3_5.button_3],[tbl_CIW_Cabinet_Wizard_3_5.button_4], " & _

                                        "[tbl_CIW_Cabinet_Wizard_3_5.button_5]) AS Button_Option, " & _

                                    "Choose([tblstandards_3_5.button]+1,[tbl_CIW_cabinet_wizard_3_5.variable_0], " & _

                                        "[tbl_CIW_cabinet_wizard_3_5.variable_1],[tbl_CIW_cabinet_wizard_3_5.variable_2], " & _

                                        "[tbl_CIW_cabinet_wizard_3_5.variable_3],[tbl_CIW_cabinet_wizard_3_5.variable_4], " & _

                                        "[tbl_CIW_cabinet_wizard_3_5.variable_5]) AS Button_Variable, tblStandards_3_5.Value, " & _

                                        "Choose([tblstandards_3_5.unit]+1,'','in.','mm.') AS Button_Unit " & _

                         "FROM (tblPartClasses_3_5 " & _

                                "INNER JOIN (tblPartTypes_3_5 " & _

                                "INNER JOIN tbl_Parts_3_5 ON tblPartTypes_3_5.PartName = tbl_Parts_3_5.Name) " & _

                                    "ON tblPartClasses_3_5.PartClassID = tblPartTypes_3_5.PartClass) " & _

                                "INNER JOIN (tblPartStandards_3_5 " & _

                                "INNER JOIN ((tblStandards_3_5 " & _

                                "INNER JOIN tblCabinets_3_5 ON tblStandards_3_5.CabinetID = tblCabinets_3_5.ID) " & _

                                "INNER JOIN tbl_CIW_Cabinet_Wizard_3_5 " & _

                                    "ON tblStandards_3_5.StdID = tbl_CIW_Cabinet_Wizard_3_5.Standard_ID) " & _

                                    "ON tblPartStandards_3_5.StdID = tblStandards_3_5.StdID) " & _

                                    "ON tblPartTypes_3_5.PartTypeID = tblPartStandards_3_5.PartTypeID " & _

                         "WHERE(((tblStandards_3_5.CabinetID) = " & int_CM_ID & ")) " & _

                         "ORDER BY tblPartClasses_3_5.ClassName, tbl_Parts_3_5.Description, Int([tblStandards_3_5.StandardNum]/1000), tblStandards_3_5.StandardNum"


            Using con_DB As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\CIW\Solid Explorer\Solid Explorer.mdb")


                Dim OleDB_Command As New OleDbCommand(str_Select, con_DB)

                Dim Reader As OleDbDataReader




                Reader = OleDB_Command.ExecuteReader()




                While Reader.Read


                    CW_Table.Rows.Add(New Object() _

                        { _

                        Reader("ClassName"), _

                        Reader("Description"), _

                        Reader("Cab_Type_Name"), _

                        CInt(Reader("StandardNum")), _

                        Reader("Standard_Description"), _

                        SE_Common_Class.Boolean_Yes_No(CBool(Reader("Visited"))), _

                        Null_to_Empty(Reader("Button_Option")), _

                        Null_to_Empty(Reader("Button_Variable")), _

                        CSng(Reader("Value")), _

                        Null_to_Empty(Reader("Button_Unit")) _



                End While




            End Using


    This code works fine and does exactly what I want it to do.


    What I am desperately trying to accomplish is to perform the same function of populating the CW_Table DataTable from the DataTables I’ve already populated (CW_tbl_Parts, CW_Tbl_Cabinets, CW_tbl_Part_Classes – there are actually more DataTables required, I just tried to keep things simple in this question).  Ideally, I could change the connection string in the “Using con_DB…” statement, re-direct all the MS Access table references to the DataTable references, and use the same SQL statement.


    One side issue I am trying to resolve with this question is that I am actually building two DataTables using SQL queries from two different MS Access databases (not two tables from the same database, multiple tables from two separate databases), then combining these to DataTables into a single DataTable for generating information to populate a DataGridView.  If I had all the tables from the two MS Access databases as individual DataTables, I am hoping to use a single SQL against these DataTables for generating the information I need to populate the DataGridView.


    I don’t know how to re-word this question any differently.  I’m sure I’m using the wrong terminology in my question that makes it confusing, to which I sincerely apologize.


    If this doesn’t help clarify what I am attempting, then I’ll assume what I’m asking is so convoluted that it can’t be done.


    Thank you for the time you’ve spent addressing my question.




    Friday, November 6, 2009 4:24 PM