Update databese after changes

Question

0.00/5 (No votes)

See more:

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.

VB

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

Posted 17-Jan-13 14:32pm

blueye89

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

F.moghaddampoor · Answer 1 · 2013-01-17T14:39:00

use linq to sql and create procedures for deleting and updating and storing
more easier.

example for deleting:

SQL

ALTER PROCEDURE dbo.Deleterowfromtable @Row int
AS
DELETE FROM Table2
WHERE Row=@Row
RETURN

example for storing:

SQL

ALTER PROCEDURE dbo.Storeintable (@Row int, @Year nvarchar(50), @Month nvarchar(50), @Day nvarchar(50), @Hour nvarchar(50), @Minute nvarchar(50), @second nvarchar(50), @Repetition nvarchar(50), @X nvarchar(50) , @Y nvarchar(50) , @Z nvarchar(50),@Operator nvarchar(50) )
AS
INSERT INTO Table2 VALUES(@Row , @Year , @Month , @Day , @Hour , @Minute , @second , @Repetition , @X , @Y , @Z, @Operator  )
RETURN

example for updating:

SQL

ALTER PROCEDURE dbo.Updatetable @Row int, @Year nvarchar(50), @Month nvarchar(50), @Day nvarchar(50), @Hour nvarchar(50), @Minute nvarchar(50), @second nvarchar(50), @Repetition nvarchar(50), @X nvarchar(50) , @Y nvarchar(50) , @Z nvarchar(50),@Operator nvarchar(50)
AS
UPDATE Table2 SET Row=@Row, Year=@Year, Month=@Month, Day=@Day, Hour=@Hour, Minute=@Minute, Second=@Second, Repetition=@Repetition, X=@X, Y=@Y, Z=@Z, Operator=@Operator
WHERE Row=@Row
RETURN