如何编辑Datagrid的记录，并在数据库

Question

这是我的代码从数据库加载数据到DataGrid中

Private Sub Records_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 
    Dim connString As String = "Provider=Microsoft.Ace.Oledb.12.0; Data Source=" & My.Application.Info.DirectoryPath.ToString() & "\BackUp\Database3.Accdb;" 
    Dim MyConn As OleDbConnection 
    Dim da As OleDbDataAdapter 
    Dim ds As DataSet 
    Dim tables As DataTableCollection 
    Dim source1 As New BindingSource 

    MyConn = New OleDbConnection 
    MyConn.ConnectionString = connString 
    ds = New DataSet 
    tables = ds.Tables 
    da = New OleDbDataAdapter("Select * from [userinfo] ORDER BY ID", MyConn) 
    da.Fill(ds, "userinfo") 'Change items to your database name 
    Dim cb = New OleDbCommandBuilder(da) 
    Dim view As New DataView(tables(0)) 
    source1.DataSource = view 
    DataGridView1.DataSource = view 

End Sub 


Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click 
    If RequiredEntry() = True Then 
     Return 
    End If 
    Try 
     Dim cn As New OleDbConnection("Provider=Microsoft.Ace.Oledb.12.0; Data Source=" & My.Application.Info.DirectoryPath.ToString() & "\BackUp\Database3.Accdb;") 

     If cn.State = ConnectionState.Closed Then cn.Open() 

     Dim sSQL As String = "insert into [userinfo]([username],[password],[FirstName],[LastName],[Account]) values(@username,@password,@FirstName,@LastName,@Account)" 

     Dim cmd As OleDbCommand = New OleDbCommand(sSQL, cn) 


     ' UserName 
     If txtPassword.Text = txtConfirm.Text Then 
      Dim username As OleDbParameter = New OleDbParameter("@username", OleDbType.VarWChar, 50) 
      username.Value = txtUser.Text.ToString() 
      cmd.Parameters.Add(username) 
     Else 
      MsgBox("Password not matched") 

     End If 

     'password 
     Dim password As OleDbParameter = New OleDbParameter("@password", OleDbType.VarWChar, 50) 
     password.Value = txtPassword.Text.ToString() 
     cmd.Parameters.Add(password) 


     'First Name 
     Dim FirstName As OleDbParameter = New OleDbParameter("@FirstName", OleDbType.VarWChar, 50) 
     FirstName.Value = txtFirstName.Text.ToString() 
     cmd.Parameters.Add(FirstName) 


     ' Last Name 
     Dim LastName As OleDbParameter = New OleDbParameter("@LastName", OleDbType.VarWChar, 50) 
     LastName.Value = txtLastName.Text.ToString() 
     cmd.Parameters.Add(LastName) 

     'Account 

     Dim Account As OleDbParameter = New OleDbParameter("@Account", OleDbType.VarWChar, 50) 
     Account.Value = cboAccount.GetItemText(cboAccount.SelectedItem) 
     cmd.Parameters.Add(Account) 



     If cmd.ExecuteNonQuery() Then 
      cn.Close() 
      MessageBox.Show("New User is Added successfully.", "Record Saved") 
      Call clear() 
      Me.Hide() 
      FileMaintenance.Show() 


     Else 
      MsgBox("New User Addition Failed ", MsgBoxStyle.Critical, "Addition Failed") 
      Return 
     End If 

    Catch ex As Exception 
     Exit Sub 
    End Try 
End Sub 
Private Sub NewUser_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles Me.KeyDown 
    e.SuppressKeyPress = e.Control 
    If e.KeyCode = Keys.Enter Then 
     SendKeys.Send("{tab}") 
    End If 

End Sub 

，这是我删除

Private Sub cmdDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDelete.Click 
    Dim connString As String = "Provider=Microsoft.Ace.Oledb.12.0; Data Source=" & My.Application.Info.DirectoryPath.ToString() & "\BackUp\Database3.Accdb;" 
    Dim MyConn As OleDbConnection 
    Dim da As OleDbDataAdapter 
    Dim ds As DataSet 
    Dim tables As DataTableCollection 
    Dim source1 As New BindingSource 
    Dim rows As String 


    Try 
     MyConn = New OleDbConnection 
     MyConn.ConnectionString = connString 
     ds = New DataSet 
     tables = (ds.Tables) 
     rows = DataGridView1.SelectedRows(0).Cells(0).Value.ToString() 
     da = New OleDbDataAdapter("Delete * from [userinfo] where ID=" & rows, MyConn) 
     da.Fill(ds, "userinfo") 
     Records_Load(sender, e) 

    Catch ex As Exception 
     MessageBox.Show("cannot delete empty records") 
    End Try 

如何编辑我的数据从datagrid视图以及我的数据库 也如何锁定数据网格输入字段意外

Answer 1

您的代码是非常错误的。您首先正确检索数据，但是您插入和删除不正确。

这个想法是，您使用一个数据适配器进行全部四个操作，即选择，插入，更新和删除。这就是为什么它具有SelectCommand,InsertCommand,UpdateCommand和DeleteCommand属性。您调用Fill并执行SelectCommand中的SQL以将数据检索到DataTable。然后，您应该对所需的所有更改进行更改，即插入，更新和删除DataTable中的数据。完成后，您可以在数据适配器上调用Update，并根据需要执行InsertCommand,UpdateCommand和DeleteCommand中的SQL，以将所有更改保存回数据库。下面是我写了一个很久以前的例子：

Private connection As New SqlConnection("connection string here") 
Private adapter As New SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem", _ 
             connection) 
Private table As New DataTable 

Private Sub InitialiseDataAdapter() 
    Dim delete As New SqlCommand("DELETE FROM StockItem WHERE ID = @ID", Me.connection) 
    Dim insert As New SqlCommand("INSERT INTO StockItem (Name, Quantity, Unit) VALUES (@Name, @Quantity, @Unit)", Me.connection) 
    Dim update As New SqlCommand("UPDATE StockItem SET Name = @Name, Quantity = @Quantity, Unit = @Unit WHERE ID = @ID", Me.connection) 

    delete.Parameters.Add("@ID", SqlDbType.Int, 4, "ID") 

    insert.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name") 
    insert.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity") 
    insert.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit") 

    update.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name") 
    update.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity") 
    update.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit") 
    update.Parameters.Add("@ID", SqlDbType.Int, 4, "ID") 

    Me.adapter.DeleteCommand = delete 
    Me.adapter.InsertCommand = insert 
    Me.adapter.UpdateCommand = update 

    Me.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey 
End Sub 

Private Sub GetData() 
    'Retrieve the data. 
    Me.adapter.Fill(Me.table) 

    'The table can be used here to display and edit the data. 
    'That will most likely involve data-binding but that is not a data access issue. 
End Sub 

Private Sub SaveData() 
    'Save the changes. 
    Me.adapter.Update(Me.table) 
End Sub 

注意，这个例子是为SQL Server写，但你只是换了所有的SqlClient类型OleDb类型和它的作品的访问。

如果您想要因某种原因立即保存更改，那么在每次更改后只需致电Update即可。

顺便说一句，您的连接字符串应该这样写：

Dim connString As String = "Provider=Microsoft.Ace.Oledb.12.0; Data Source=|DataDirectory|\BackUp\Database3.Accdb;"