更改后更新数据库 [英] Update databese after changes

问题描述

我又来了。我的问题是如何在单击特定按钮后保存db.mdb文件中的更改。例如，如果我在vb datagridview中删除了一些记录，单击Delete按钮后它将在db.mdb中表示。我解决了添加新记录的问题，点击保存按钮后，它可以正常工作。我只需要设置两个按钮。其中一个是删除按钮，另一个是保存按钮，但在我对某些记录进行了一些修改后。我希望有人帮助我。

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.

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

推荐答案

使用linq to sql并创建删除，更新和存储的程序

更容易。



删除示例：

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

example for deleting:

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

存储示例：

example for storing:

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:

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

这篇关于更改后更新数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！