时间戳

首页 > 解决方案 > 如何从连接到数据库的组合框中删除项目并自动刷新？

vb.net - 如何从连接到数据库的组合框中删除项目并自动刷新？

问题描述

我这里有点麻烦。我在这里要做的是删除连接到数据库的组合框中的一个项目，删除后，它会自动刷新组合框的内容。

        Dim cons As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\athan\Documents\PAGEANT.accdb")

        Using cons


            With cmd
                .Connection = cons
                .CommandText = "DELETE FROM  Judges Where Fullname = @FullName"
                .Parameters.AddWithValue("@FullName", cmbJudges.SelectedItem.ToString)
                .Connection.Open()
                .ExecuteNonQuery()
                .Connection.Close()

                MessageBox.Show("RECORD HAS BEEN DELETED", "DELETE", MessageBoxButtons.OK, MessageBoxIcon.Information)


                txtFullNameJudge.Text = Nothing
                txtContactJudge.Text = Nothing
                txtUserNameJudge.Text = Nothing
                txtPasswordJudge.Text = Nothing
                cmbJudges.Text = ""

            End With
        End Using

它正在删除一条记录，但它不会刷新组合框的内容，然后我无法删除其他数据。看来我只能删除单个数据。然后我需要重新运行程序，以便在删除生效之前。

标签： vb.netcomboboxvisual-studio-2019

解决方案

第一种方法代码更多，但我认为第二种方法更接近@jmcilhinney 在评论中建议的方法，可能会造成问题。我认为DataAdapter 需要一个主键才能成为 Select 的一部分。如果FullName不是 Judges 表的主键，则此方法可能不起作用。

您可能希望将dt(the Datatable) 设为 Form 级别的变量，这样您就不必从.DataSource属性中提取和转换它。然后，填充组合的方法和删除裁判的方法都可以使用它。如果您这样做，请务必删除局部变量。

Private Sub FillJudgesCombo()
    Dim dt As New DataTable
    Using cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\athan\Documents\PAGEANT.accdb"),
            cmd As New OleDbCommand("Select FullName From Judges")
        cn.Open()
        dt.Load(cmd.ExecuteReader)
    End Using
    cmbJudges.DisplayMember = "FullName"
    cmbJudges.DataSource = dt
End Sub

Private Sub DeleteJudge1()
    'I stored this in a local variable because we are using it more tha once in this method
    Dim NameToDelete = cmbJudges.Text
    'To update the database
    Using cons As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\athan\Documents\PAGEANT.accdb"),
            cmd As New OleDbCommand("DELETE FROM  Judges Where Fullname = @FullName", cons)
        'had to guess at the datatype and field size - check your database
        'The Add method is much preferred to the .AddWithValue
        cmd.Parameters.Add("@FullName", OleDbType.VarChar, 200).Value = NameToDelete
        cons.Open()
        cmd.ExecuteNonQuery()
    End Using
    'To update the combo box by editing DataTable
    Dim dt = DirectCast(ComboBox1.DataSource, DataTable)
    'This line uses and interpolated string indicated by the $ preceding the string.
    'This allows to insearch a variable in place in the string surronded by braces.
    Dim dr = dt.Select($"FullName = {NameToDelete}")
    dr(0).Delete()
    dt.AcceptChanges()
    ResetTextBoxesAndCombo()
    MessageBox.Show("RECORD HAS BEEN DELETED", "DELETE", MessageBoxButtons.OK, MessageBoxIcon.Information)
End Sub

Private Sub ResetTextBoxesAndCombo()
    'Offloaded this because it really has nothing to do with deleting a judge.
    txtFullNameJudge.Text = Nothing
    txtContactJudge.Text = Nothing
    txtUserNameJudge.Text = Nothing
    txtPasswordJudge.Text = Nothing
    cmbJudges.SelectedIndex = -1
End Sub

Private Sub DeleteJudge2()
    Dim NameToDelete = cmdJudges.Text
    Dim dt = DirectCast(ComboBox1.DataSource, DataTable)
    'The Select method has no idea how many rows are returned
    'so it returns an array of DataRows
    Dim dr = dt.Select($"FullName = {NameToDelete}")
    'We are only interested in the first item in the array.
    'The array should only have one element.
    dr(0).Delete()
    Using con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\athan\Documents\PAGEANT.accdb"),
            da As New OleDbDataAdapter("Select FullName From PAGEANT", con)
        Dim cmdBuild As New OleDbCommandBuilder(da)
        da.Update(dt)
    End Using
    ResetTextBoxesAndCombo()
    MessageBox.Show("RECORD HAS BEEN DELETED", "DELETE", MessageBoxButtons.OK, MessageBoxIcon.Information)
End Sub

推荐阅读