Update database after changes

[blueye89]

Hi , here I am again. My question is how to save changes in my "db.mdb" file after clicking on specific buttons. Example, if I delete some record in vb datagridview,after click on Delete button it will be represent in "db.mdb". I resolve problem to add new record and after click on Save button it works correctly. I just need to set two more buttons. One of them is Delete button and other one is save button but after I some modification on some record. I hope that someone can help me.

Public Class MainForm

    Dim conn As New OleDb.OleDbConnection
#Region "Refresh Data"
    Private Sub RefreshData()
        If Not conn.State = ConnectionState.Open Then
            'open connection
            conn.Open()
        End If

        Dim da As New OleDb.OleDbDataAdapter("SELECT FirstName as [FirstName], " & _
                                             " LastName " & _
                                             " FROM Table1 ORDER BY FirstName", conn)
        Dim dt As New DataTable
        'fill data to datatable
        da.Fill(dt)

        'offer data in data table into datagridview
        Me.DataGridView1.DataSource = dt

        'close connection
        conn.Close()
    End Sub
#End Region

#Region "Update db"
    Sub Update_db()
        Dim cmd As New OleDb.OleDbCommand
        If Not conn.State = ConnectionState.Open Then
            'Open connection if it is not yet open
            conn.Open()
        End If

        cmd.Connection = conn
        'check whether add new or update
        If Me.txtFirstName.Tag & "" = "" Then
            'Add new 
            'Add data to table
            cmd.CommandText = "INSERT INTO Table1(FirstName,LastName) " & _
                                " VALUES('" & Me.txtFirstName.Text & "','" & Me.txtLastName.Text & "')"

            cmd.ExecuteNonQuery()
        Else
            'Update data in table
            cmd.CommandText = "UPDATE Table1 " & _
                        " SET FirstName=" & Me.txtFirstName.Text & _
                        ", LastName='" & Me.txtLastName.Text & "'" & _
                                   " WHERE FirstName=" & Me.txtFirstName.Tag
            cmd.ExecuteNonQuery()
        End If

        'Refresh data
        RefreshData()

        'Close connection
        conn.Close()

    End Sub
#End Region

#Region "Send data from DB to TXT"
    Sub Fill_txt()
        txtFirstName.Text = DataGridView1.SelectedRows(0).Cells(0).Value
        txtLastName.Text = DataGridView1.SelectedRows(0).Cells(1).Value
    End Sub
#End Region

#Region "Disable TXT"
    Sub Disable_TXT()
        txtFirstName.Enabled = False
        txtFirstName.BackColor = Color.LightGray
        txtLastName.Enabled = False
        txtLastName.BackColor = Color.LightGray
    End Sub
#End Region

#Region "Enable TXT"
    Sub Enable_TXT()
        txtFirstName.Enabled = True
        txtFirstName.BackColor = Color.GhostWhite
        txtLastName.Enabled = True
        txtLastName.BackColor = Color.GhostWhite
        'Focus on txtFirstName
        txtFirstName.Focus()
    End Sub
#End Region

#Region "Clear TXT"
    Sub Clear_TXT()
        txtFirstName.Text = ""
        txtLastName.Text = ""
    End Sub
#End Region

#Region "Unlock btnSave"
    Private Sub Unlock_btnSave(ByVal Sender As Object, ByVal e As EventArgs)
        btnSave.Enabled = Not (txtFirstName.Text = String.Empty OrElse txtLastName.Text = String.Empty)
    End Sub
#End Region

#Region "Disable btn(Edit,Delete,Save,Cancel)"
    Sub Disable_btnEdit_Delete_Save_Cancel()
        btnEdit.Enabled = False
        btnDelete.Enabled = False
        btnSave.Enabled = False
        btnCancel.Enabled = False
    End Sub
#End Region

#Region "btnAdd Click Event"
    Sub btnAddClickEvent()
        btnAdd.Enabled = False
        btnEdit.Enabled = False
        btnDelete.Enabled = False
        btnCancel.Enabled = True
    End Sub
#End Region

#Region "btnEdit Click Event"
    Sub btnEditClickEvent()
        btnAdd.Enabled = False
        btnEdit.Enabled = False
        btnDelete.Enabled = False
        btnSave.Enabled = True
        btnCancel.Enabled = True
    End Sub
#End Region
#Region "btnDelete Click Event"
    Sub btnDeleteClickEvent()
        Dim dr As DataGridViewRow
        For Each dr In DataGridView1.SelectedRows
            DataGridView1.Rows.Remove(dr)
        Next
    End Sub
#End Region
#Region "btnCancel Click Event"
    Sub btnCancelClickEvent()
        btnAdd.Enabled = True
        btnEdit.Enabled = True
        btnDelete.Enabled = True
        btnCancel.Enabled = False
    End Sub
#End Region



#Region "DataGridView Content Alignment"
    Sub DGV_Content_Aligment()
        DataGridView1.Columns(0).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter 'Aligns the Header Text
        DataGridView1.Columns(1).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter 'Aligns the Header Text

        DataGridView1.Columns(0).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter 'Just aligns the cell contents
        DataGridView1.Columns(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter 'Just aligns the cell contents

    End Sub

#End Region

#Region "Disable DGV Column Sorting"
    Sub Disable_DGV_Column_Sorting()
        For Each dgvCol As DataGridViewColumn In Me.DataGridView1.Columns
            dgvCol.SortMode = DataGridViewColumnSortMode.NotSortable
            'Also by an column
            'DataGridView1.Columns(0).SortMode = DataGridViewColumnSortMode.NotSortable
        Next
    End Sub
#End Region

    Private Sub MainForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        'Disable btn(Edit,Delete,Save,Cancel)
        Call Disable_btnEdit_Delete_Save_Cancel()

        'Unlock btnSave
        AddHandler txtFirstName.TextChanged, AddressOf Unlock_btnSave
        AddHandler txtLastName.TextChanged, AddressOf Unlock_btnSave

        'Disable TXT
        Call Disable_TXT()

        'Make connection
        conn = New OleDb.OleDbConnection
        conn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0; Data Source=|DataDirectory|\db.mdb"

        'Refresh Data
        Me.RefreshData()

        'DataGridView Content Alignment
        Call DGV_Content_Aligment()

        'Disable DGV Column Sorting
        Call Disable_DGV_Column_Sorting()

        'Send data from DB to TXT
        Call Fill_txt()

        'btnCancel Click Event
        Call btnCancelClickEvent()

        'Disable btnSave when text fields are inactive 
        If txtFirstName.Enabled = False Then
            btnSave.Enabled = False
        End If
    End Sub

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

        'Clear TXT
        Call Clear_TXT()

        'btnAdd Click Event
        Call btnAddClickEvent()
        'Enable TXT
        Call Enable_TXT()

    End Sub

    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        If MsgBox("Are you sure? ", MsgBoxStyle.Question + MsgBoxStyle.YesNo) = Windows.Forms.DialogResult.Yes Then
            Me.Close()
        Else
            'Focus on btnAdd
            btnAdd.Focus()
        End If
    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        'Disable TXT
        Call Disable_TXT()

        'btnCancel Click Event
        Call btnCancelClickEvent()

        'Send data from DB to TXT
        Call Fill_txt()

        'Disable btnSave after Cancel click
        btnSave.Enabled = False


    End Sub

    Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
        'btnEdit Click Event
        Call btnEditClickEvent()

        'Enable TXT
        Call Enable_TXT()

    End Sub

    Private Sub DataGridView1_CellMouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView1.CellMouseClick
        'Send data from DB to TXT
        Call Fill_txt()

        'btnCancel Click Event
        Call btnCancelClickEvent()

        'Disable btnSave after mouse click on DGV
        btnSave.Enabled = False

        'Disable TXT
        Call Disable_TXT()

    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        'Update db
        Call Update_db()
        'Clear TXT
        Call Clear_TXT()
    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click

        'btnDelete Click Event
        Call btnDeleteClickEvent()

        'Send data from DB to TXT
        Call Fill_txt()

        'Update db after deleted row

    End Sub
End Class

[IanRyder]

Hi,

If you want to populate a DataGridView in code, as you are doing, then there is a much easier way to ensure that any updates you make to the DataGridView are persisted to your data source. Once you have bound your DataGridView's DataSource property to a DataSet or DataTable after loading your data source using a DataAdapter, you can then use a CommandBuilder to create the SQL command expressions to handle all of your Insert, Update and Delete requirements on the single click of a button using the DataAdapter.Update method.

Have a look at this quick example using the Employees table from the sample Northwind Database:-

Imports System.Data.OleDb

Public Class Form1
  Dim oleConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='D:\Access Databases\northwind.mdb'")
  Dim employeeAdapter As New OleDbDataAdapter("Select * From Employees", oleConn)
  Dim employeeCmdBuilder As New OleDbCommandBuilder(employeeAdapter)
  Dim myDS As New DataSet

  Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    employeeAdapter.Fill(myDS, "Employees")
    DataGridView1.DataSource = myDS.Tables(0)
  End Sub

  Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    employeeAdapter.Update(myDS.Tables(0))
  End Sub
End Class

If you run the above code in a new form and then make Changes, Insertions and Deletions to the rows of the DataGridView you will see that when you click the button all your changes will be saved to your database in once go and therefore no need for additional subroutines to manage the individual SQL command routines.

Hope that helps.

Cheers,

Ian