Listbox update with datasource

archer_coal

archer_coal

Well-known member
Joined
Apr 4, 2003
Messages
96
I have a listbox bound to an oledb data connection with command buttons for delete and add.
When the user selects an item and clicks on delete, the record is deleted fine but i cant get the listbox to show the changes without error.

If i reload the listbox and its binding, it shows the previous items as well as the reloaded data.

If i refresh the listbox nothing happens.
If i try to use somthing like Listbox1.item.clear I get an error telling me i cannot clear when the datasource is set.

How do i update the listbox to show the changes made after delete?
 
If the record is deleted by the delete method, do the following:
Code: 
listbox.DataSource = System.DBNull.Value
then reset the datasource back to its previous value.

Jon
 
re

I think I must not be using the best method to achieve this. The comments made on this problem seem to make sense in theory but for my program do not :p

here is the code im toying with
Code: 
Private m_DataAdapter As OleDbDataAdapter
Private m_DataSet As New DataSet()

Private Sub load_listbox()
        m_DataAdapter = New OleDbDataAdapter( _
          "SELECT * FROM reminders ORDER BY id", _
          "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=C:\data\20304aa1.mdb")

         Fill the DataSet.
        m_DataAdapter.Fill(m_DataSet)

        ListBox1.DataSource = m_DataSet.Tables(0)
        ListBox2.DataSource = m_DataSet.Tables(0)
        ListBox1.ValueMember = "reminder"
        ListBox2.ValueMember = "id"
    End Sub

Then the routine for deleting the item from the listbox

Private Sub cmdDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDelete.Click
        Dim testmsg As Integer
        Dim iVar As String
        iVar = ListBox2.SelectedItem(0)

        Label1.Text = iVar
        testmsg = MsgBox("Are you sure you want to remove this reminder?", 1, "Delete Reminder")
        If testmsg = 1 Then
            
            Dim myConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\data\20304aa1.mdb")
            myConnection.Open()
            Dim MyCommand As New OleDbCommand("DELETE * FROM reminders where id = " & iVar, myConnection)
            MyCommand.ExecuteNonQuery()
            myConnection.Close()

            MyCommand.Dispose()
            ListBox1.DataSource = Nothing
            ListBox2.DataSource = Nothing

            ListBox1.Items.Clear()
            ListBox2.Items.Clear()
         
            load_listbox()
        Else
            MsgBox("Reminder has not been removed")
        End If

    End Sub

After Listbox1 and 2 are set to nothing and are cleared, the previous items in the listboxes are replaced with System.Data.DataRowView
 
Re: re

Code: 
Private m_DataAdapter As OleDbDataAdapter
Private m_DataSet As New DataSet()

Private Sub load_listbox()
        m_DataAdapter = New OleDbDataAdapter( _
          "SELECT * FROM reminders ORDER BY id", _
          "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=C:\data\20304aa1.mdb")

         Fill the DataSet.
        m_DataAdapter.Fill(m_DataSet)

        ListBox1.DataSource = m_DataSet.Tables(0)
        ListBox2.DataSource = m_DataSet.Tables(0)
        ListBox1.ValueMember = "reminder"
        ListBox2.ValueMember = "id"
    End Sub

Then the routine for deleting the item from the listbox

Private Sub cmdDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDelete.Click
        Dim testmsg As Integer
        Dim iVar As String
        iVar = ListBox2.SelectedItem(0)

        Label1.Text = iVar
        testmsg = MsgBox("Are you sure you want to remove this reminder?", 1, "Delete Reminder")
        If testmsg = 1 Then
            
            Dim myConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\data\20304aa1.mdb")
            myConnection.Open()
            Dim MyCommand As New OleDbCommand("DELETE * FROM reminders where id = " & iVar, myConnection)
            MyCommand.ExecuteNonQuery()
            myConnection.Close()

            MyCommand.Dispose()
            ListBox1.DataSource = Nothing
            ListBox2.DataSource = Nothing

            ListBox1.Items.Clear()
            ListBox2.Items.Clear()

         m_DataSet.Tables.Clear()   add this here, this will complete the clearing of data so you can restore it in the load_listbox call
          
             load_listbox()
        Else
            MsgBox("Reminder has not been removed")
        End If

    End Sub
 
re

I also had to add Listbox1.ValueMember = Nothing to the delete Button to get rid of the error. Just in case anyone else was following this thread i thought i should post it.

Thanks for your help everyone! It works fine now.

Code: 
Private Sub cmdDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDelete.Click
        Dim testmsg As Integer
        Dim iVar As String
        iVar = ListBox2.SelectedItem(0)

        Label1.Text = iVar
        testmsg = MsgBox("Are you sure you want to remove this reminder?", 1, "Delete Reminder")
        If testmsg = 1 Then
            
            Dim myConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\data\20304aa1.mdb")
            myConnection.Open()
            Dim MyCommand As New OleDbCommand("DELETE * FROM reminders where id = " & iVar, myConnection)
            MyCommand.ExecuteNonQuery()
            myConnection.Close()

            MyCommand.Dispose()
            ListBox1.DataSource = Nothing
            ListBox2.DataSource = Nothing
            Listbox1.ValueMember = Nothing   added
            Listbox1.ValueMember = Nothing   added
            ListBox1.Items.Clear()
            ListBox2.Items.Clear()
            

         m_DataSet.Tables.Clear()   add this here, this will complete the clearing of data so you can restore it in the load_listbox call
          
             load_listbox()
        Else
            MsgBox("Reminder has not been removed")
        End If

    End Sub
 
Back
Top