使用数据绑定从数据库中删除记录

Question

我已经构建了您可以在下图中看到的小应用程序。

我现在工作的删除按钮，但我坚持。当我点击它时，似乎没有任何事情发生。有谁能告诉我我错过了什么吗？点击删除按钮时，我想完全删除数据库中的记录。

Public Class Form1 
    Dim connection As New OleDb.OleDbConnection  
    Dim sql As String        
    Dim DataAdapter As OleDb.OleDbDataAdapter  
    Dim Datatable As New DataTable     
    Dim ProjectBindingSource As New BindingSource 
    Dim sql_delete As String 

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load 
     Try 
      connection.ConnectionString = "PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source=Z:\Corporate Reporting\2014_15\Corporate\QuarterlyReporting.accdb" 

      connection.Open() 
      sql = "SELECT * FROM Projects_Application ORDER BY ProjectID" 
      DataAdapter = New OleDb.OleDbDataAdapter(sql, connection) 
      DataAdapter.Fill(Datatable) 

      ProjectBindingSource.DataSource = Datatable 

      With cmbSearch 
       .DisplayMember = "ProjectID" 
       .ValueMember = "ProjectID" 
       .DataSource = ProjectBindingSource 
      End With 

      NavigateRecords() 

      connection.Close() 
     Catch ex As Exception 
      MsgBox(ex.Message) 
     End Try 
    End Sub 

    Private Sub NavigateRecords() 
     txtProgammeID.DataBindings.Clear() 
     txtProgramme.DataBindings.Clear() 
     txtProjectName.DataBindings.Clear() 
     txtProgammeID.DataBindings.Add("Text", ProjectBindingSource, "ProgrammeID") 
     txtProgramme.DataBindings.Add("Text", ProjectBindingSource, "Programme") 
     txtProjectName.DataBindings.Add("Text", ProjectBindingSource, "ProjectName") 
    End Sub 

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click 
     Dim Project_To_Delete As String 
     Project_To_Delete = txtProjectID.Text 
     Dim Delete As New OleDb.OleDbCommand("DELETE FROM Projects_Application WHERE ProjectID = " & Project_To_Delete, connection) 

     Delete.Parameters.Add(Project_To_Delete, OleDb.OleDbType.Integer, 3, "ProjectID") 
     DataAdapter.DeleteCommand = Delete 

     DataAdapter.Update(Datatable) 
     ProjectBindingSource.EndEdit() 
     ProjectBindingSource.DataSource = Datatable 
    End Sub  
End Class 

Answer 1

您应该使用一个参数创建一次delete命令：项目ID，而不是在sql字符串中使用实际的项目ID。

的命令应该是这样的

Dim Delete As New OleDb.OleDbCommand("DELETE FROM Projects_Application WHERE ProjectID = ?", connection) 

，你应该添加参数一样

Delete.Parameters.Add("ProjectID", OleDb.OleDbType.Integer, 3, "ProjectID") 

同时确保连接实际上是开放的，不是封闭/处置。

---

的DataTable/DataAdapter背后的想法是，你配置DataAdapter的命令一次，一个DataTable内完成所有的工作，并在完成后，只需拨打Update和DataAdapter需要是实际执行的SQL语句

所以要删除记录，只需删除DataTable中的特定行，然后调用AcceptChanges，然后通过调用DataAdapter.Update(Datatable)来更新db。