What's New
Q & A
Tip Jar
C# Helper...
Follow VBHelper on Twitter
MSDN Visual Basic Community
TitleMake a DataSet at run time without using any design time controls in VB .NET
DescriptionThis example shows how to make a DataSet at run time without using any design time controls in VB .NET.
KeywordsDataSet, runtime, database, ADO.NET
CategoriesVB.NET, Database
When the form loads, the program composes a connect string and uses it to build an OleDbDataAdapter. It adds a table mapping to map the default (stupid) name Table to the selected table's real name Students. It then creates a DataSet and uses the adapter's Fill method to load data into it. It binds the DataSet's Students table to a DataGrid control so you can work with the data.
Private Const SELECT_STRING As String = _
    "SELECT * FROM Students"

' Load the DataSet.
Private Sub Form1_Load(ByVal sender As System.Object, ByVal _
    e As System.EventArgs) Handles MyBase.Load
    ' Compose the connect string.
    Dim db_name As String = Application.StartupPath
    db_name = db_name.Substring(0, _
        db_name.LastIndexOf("\")) & "\Students.mdb"
    connect_string = _
        "Data Source=" & db_name & ";" & _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Persist Security Info=False"

    ' Create the SqlDataAdapter.
    Dim data_adapter As New OleDbDataAdapter(SELECT_STRING, _

    ' Map "Table" to "Students."
    data_adapter.TableMappings.Add("Table", "Students")

    ' Fill the DataSet.
    m_DataSet = New DataSet

    ' Bind the DataGrid control to the Students DataTable.
    DataGrid1.SetDataBinding(m_DataSet, "Students")
End Sub
When the form is closing, the program creates a new OleDbAdapter and adds the same table mapping as before. It then makes a CommandBuilder to automatically create INSERT, UPDATE, and DELETE commands for the data. It finishes by calling the adapter's Update method to save changes to the data into the database.
' Save the data.
Private Sub Form1_Closing(ByVal sender As Object, ByVal e _
    As System.ComponentModel.CancelEventArgs) Handles _
    If m_DataSet.HasChanges() Then
        ' Create the DataAdapter.
        Dim data_adapter As New _
            OleDbDataAdapter(SELECT_STRING, CONNECT_STRING)

        ' Map Table to Students.
        data_adapter.TableMappings.Add("Table", "Students")

        ' Make the CommandBuilder generate the
        ' insert, update, and delete commands.
        Dim command_builder As New _

        ' Uncomment this code to see the INSERT,
        ' UPDATE, and DELETE commands.
        'Debug.WriteLine("*** INSERT ***")
        'Debug.WriteLine("*** UPDATE ***")
        'Debug.WriteLine("*** DELETE ***")

        ' Save the changes.
    End If
End Sub
For more information on database programming in VB .NET, see my book Visual Basic .NET Database Programming.
Copyright © 1997-2010 Rocky Mountain Computer Consulting, Inc.   All rights reserved.