troubleshooting Question

Refresh/Reload datatable/dataset

Avatar of wiswalld
wiswalldFlag for United States of America asked on
Visual Basic.NET
3 Comments1 Solution5012 ViewsLast Modified:
How do I refresh/reload the datatable/dataset to reflect changes using this code (If a user makes a change I want it to reflect on the datagridview):

Private connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\CADDVAD\CAD-050107.mdb"


#Region " Database Transactions"

    Private daEmployee As System.Data.OleDb.OleDbDataAdapter
    Private daShift As System.Data.OleDb.OleDbDataAdapter
    Private Sub LoadDataSet()

        daEmployee = New System.Data.OleDb.OleDbDataAdapter()
        daEmployee.SelectCommand = New OleDb.OleDbCommand()
        daEmployee.SelectCommand.Connection = New OleDb.OleDbConnection(connectionString)

        daShift = New System.Data.OleDb.OleDbDataAdapter()
        daShift.SelectCommand = New OleDb.OleDbCommand()
        daShift.SelectCommand.Connection = New OleDb.OleDbConnection(connectionString)

        'Fill tbl_employee
        daEmployee.SelectCommand.CommandText = "SELECT * FROM tbl_employee"
        daEmployee.Fill(m_DataSet.Tables("tbl_employee"))

        'Fill tbl_ShiftTime
        daShift.SelectCommand.CommandText = "SELECT * FROM tbl_ShiftTime"
        daShift.Fill(m_DataSet.Tables("tbl_ShiftTime"))

    End Sub


    Private Sub UpdateDataBase()
        Dim db As OleDb.OleDbCommandBuilder

        db = New OleDb.OleDbCommandBuilder(daEmployee)
        daEmployee.Update(m_DataSet.Tables("tbl_employee"))

        'This shift table data is only used in a combobox,
        '    so we don't need to update it.  But if we did,
        '    just uncomment this code.
        'db = New OleDb.OleDbCommandBuilder(daShift)
        'daShift.Update(m_DataSet.Tables("tbl_ShiftTime"))



    End Sub

#End Region    'Database Transactions


#Region " Private Methods"


#Region " Create DataSet"
    Private m_DataSet As New DataSet()
    Private Sub CreateDataSet()
        'Create Employee Table
        Dim tblEmployee As New DataTable("tbl_employee")

        '*** Create Columns for table
        Dim colAutonumber As New DataColumn("AutoNumber")
        colAutonumber.Unique = True
        colAutonumber.AutoIncrement = True
        colAutonumber.AutoIncrementSeed = 1
        colAutonumber.AutoIncrementStep = 1


        Dim colDate1 As New DataColumn("Date1")
        colDate1.DataType = System.Type.GetType("System.DateTime")

        Dim colName As New DataColumn("Name")
        Dim colOfficer2 As New DataColumn("Officer2")
        Dim colShift As New DataColumn("Shift")
        Dim colUnit As New DataColumn("Unit")
        Dim colAssignment As New DataColumn("Assignment")
        Dim colAvailable As New DataColumn("Available")
        '*** End Create Columns for table

        'Add columns to table
        tblEmployee.Columns.AddRange(New DataColumn() _
            {colAutonumber, colDate1, colName, colOfficer2, _
            colShift, colUnit, colAssignment, colAvailable})


        'Create Shift table
        Dim tblShift As New DataTable("tbl_ShiftTime")

        '*** Create Columns for table
        Dim colShift_Time As New DataColumn("Shift_Time")
        Dim colShift2 As New DataColumn("Shift")
        '*** End Create Columns for table

        'Add columns to table
        tblShift.Columns.AddRange(New DataColumn() {colShift_Time, colShift2})


        'Add both tables to dataset
        m_DataSet.Tables.AddRange(New DataTable() {tblEmployee, tblShift})

    End Sub

#End Region    'Create DataSet


#Region " Create Controls"

    Private Sub CreateComboBox()
        'Create a label for the combobox
        Dim lbl As New Label()
        lbl.Text = "Select a Shift:  "
        lbl.Name = "lblShift"
        lbl.AutoSize = True
        lbl.Location = New Point(22, 21)


        'Location 115, 18
        Dim combo As New ComboBox()
        combo.Name = "comboShift"
        combo.Location = New Point(115, 18)

        AddHandler combo.SelectedIndexChanged, AddressOf comboShift_SelectedIndexChanged

        'Add new controls to form
        Me.Controls.AddRange(New Control() {lbl, combo})

    End Sub




    Private Sub CreateButton()
        Dim btn As New Button()
        btn.Name = "btnSave"
        btn.Text = "Save"
        btn.Location = New Point(25, 225)

        AddHandler btn.Click, AddressOf btnSave_Click

        Me.Controls.Add(btn)

    End Sub

#End Region    'Create Controls


#Region " Load Controls"



    Private Sub LoadDataGrid()
        Dim dv As DataView = m_DataSet.Tables("tbl_employee").DefaultView
        dv.RowFilter = "Shift='" & Me.ComboBox2.Text & "'"
        DataGridView1.DataSource = dv

    End Sub

#End Region    'Load Controls


#End Region    'Private Methods


#Region " Control Event Handlers"

    Private Sub frmEmployee_Load(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the '_CAD_050107DataSet.tbl_shifttime' table. You can move, or remove it, as needed.
        Me.Tbl_shifttimeTableAdapter.Fill(Me._CAD_050107DataSet.tbl_shifttime)
        'TODO: This line of code loads data into the '_CAD_050107DataSet.TblShiftStatus' table. You can move, or remove it, as needed.
        Me.TblShiftStatusTableAdapter.Fill(Me._CAD_050107DataSet.TblShiftStatus)
        CreateDataSet()
        LoadDataSet()
        LoadDataGrid()

    End Sub

    Private Sub btnSave_Click(ByVal sender As Object, ByVal e As EventArgs)
        UpdateDataBase()

    End Sub

    Private Sub comboShift_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
        If DirectCast(sender, ComboBox).SelectedValue.ToString() <> "" Then
            LoadDataGrid()
        End If
    End Sub

#End Region    'Control Event Handlers
ASKER CERTIFIED SOLUTION
Justin_W

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros