Update databese after changes

[topsykretts]

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 help me.

Here is a code:

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

 

[jmcilhinney]

You are doing it all wrong. I think I may already have addressed this in another of your posts but it appears that you have ignored my advice. DO NOT call ExecuteNonQuery at all. DO NOT retrieve data from the database over and over. Retrieve the data once and once only, edit it locally and then save all the changes together. I believe that I have already directed you to this thread of mine before too. As I believe I said then, use the examples that use a data adapter as your basis.

Retrieving and Saving Data in Databases

As I believe I have already said to you more than once, use a data adapter to Fill a DataTable, edit that DataTable and then use the same data adapter to Update the database. My example shows exactly how to do that.

You are really making this all far more difficult than it needs to be. You should start by binding your populated DataTable to a BindingSource and then binding that to the DataGridView AND the TextBoxes, e.g.

myBindingSource.DataSource = myDataTable
myDataGridView.DataSource = myBindingSource
myTextBox.DataBindings.Add("Text", myBindingSource, "ColumnNameHere")

Now, whenever the user selects a row in the grid, the field values will automatically be displayed in the TextBox(es). They can then simply edit the fields in the TextBoxes and those changes will be automatically pushed to the DataTable and the grid when they navigate to the next record. You can force the change to be committed by calling EndEdit on the BindingSource, which you would need to do before saving in case the user has made an edit and not navigated away.

To add a new record you call AddNew on the BindingSource. Again, the new record will be displayed in the TextBox(es) and EndEdit will commit that row to the DataTable. To delete a record you call RemoveCurrent on the BindingSource.

Once you've done with all your edits, you use the data adapter to Update the database.

 

[topsykretts]

As you see my friend I am beginner on relation vb-msacces and I don't understand correctly what you said to me. In one week searching I scarcely made this application which is not complete as I said. If some modify my code I will see where I was wrong and that will be my leason for the future. This is the best way to learn fast because as I said I am an beginner.
Thank you anyway.

 

[jmcilhinney]

There's nothing wrong with being a beginner and not knowing how to do something but if someone tells you and shows you what to do and you still don't do it then that is a problem. If you're not going to follow my advice then there's no point my providing any more, so I'll say good luck and post on this subject no more.