Deleting An Item From A List Box

[MarkItZero]

Ok,
This problem has been a real pain for me

I have a list box on a form which lists the UserNames in my DB(Access). I have a delete method which deletes the currently selected UserName from the DB. However, I cant seem to be able to get the listbox to properly refresh (ie not show the newly deleted UserName) For some reason, after I complete the delete method, the UserName still shows up in the listbox.

Here is my code:

Populate LstUsers
Sub PopulateLstUsers()
Fill Dataset
        OleDbDataAdapter1.Fill(DsEditUsers1, "Users")

        Declare Array
        Dim ArryEditUsers As ArrayList = New ArrayList()
        Dim RowEditUsers As DataRow

        Fill Array
        For Each RowEditUsers In DsEditUsers1.Tables("Users").Rows
            ArryEditUsers.Add(New LookupItem(RowEditUsers("UserID"), RowEditUsers("UserName").ToString()))
        Next

        Fill List
        If ArryEditUsers.Count > 0 Then
            lstUsers.DataSource = ArryEditUsers
            lstUsers.DisplayMember = "Text"
            lstUsers.ValueMember = "ID"
        End If

        Declare LookupItem
        Dim li As LookupItem
        Fill LookupItem
        li = DirectCast(lstUsers.SelectedItem, LookupItem)
        Display List
        DisplayUserInfo(li.ID)
End Sub

Display User Info
Sub DisplayUserInfo(ByVal ID as Integer)
Display other columns from record in textboxes
End Sub

Click Delete
Private Sub CmdDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdDelete.Click

        Are You Sure
        If MsgBox("Are You Sure", MsgBoxStyle.OKCancel, "Service Estimate: Warning") = MsgBoxResult.OK Then

            Declare ADODB Objects
            Dim cnnDelete As New ADODB.Connection()

            Open ADODB Connection
            cnnDelete.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source=C:\Estimate\EstimatesDB.mdb;", _
                   "Admin", "")

            Declare RecordSet
            Dim rstDelete As New ADODB.Recordset()

            Query
            Dim StrDelete As String = "Select * from Users Where UserID = " & txtUserID.Text & ""

            Fill RecordSet
            With rstDelete
                .ActiveConnection = cnnDelete
                .Open(StrDelete, , _
                    ADODB.CursorTypeEnum.adOpenKeyset, _
                    ADODB.LockTypeEnum.adLockOptimistic)
            End With

            Update RecordSet
            rstDelete.Delete()

            Close Connection
            cnnDelete.Close()

            MsgBox
            MsgBox("The User Has Been Deleted", , "Service Estimate")

            Refresh LstUsers
            PopulateLstUsers()

        Else

            MsgBox Not Deleted
            MsgBox("User " & txtEditUserName.Text & ", Not Deleted", , "Service Estimate")

        End If

End Sub

I hope that code is readable.

Any suggestions?
Thanks!

[edit]its more readable with

 tags[/edit]

 

[Robby]

try adding lstUsers.Items.Clear() at the top of PopulateLstUsers()

 

[MarkItZero]

That doesnt seem to work either.
I actually have to leave the form and come back before the changes will be shown. Though the record is actually deleted from the DB right away.

Strange and aggravating

 

[MarkItZero]

Anyone have any ideas? I am still stuck on this same problem.

 

[Nerseus]

Do you, by chance, only have one user in the listbox and after the delete you have none? If so, youre "If ArryEditUsers.Count > 0 Then" wont reset the datasource.

Also, why in the world are you using ADODB.Recordset??? I refuse to test this code out of principal. If you fix it to use ADO.NET Ill try and duplicate this to see what the problem might be

-Nerseus

 

[MarkItZero]

I have been testing the code with multiple users, so the ArryEditUsers.Count > 0 shouldnt cause any problems (though it is something I will have to watch out for later)

My code is very piecemeal. Before starting this project I did not have any experience with VB, so I have been learning on the go and patching code in from wherever I can find it. So someone either on this site or in one of my books, I dont remember which, suggested I use ADODB. And it worked, so I kept it.

Maybe next project I will take the time to learn the new fancy methods

 

[jfackler]

I agree with Nersues, you should move on to the .net methods.
Having said that, and in support of why you should, you are combining the old and the new and are experiencing the
logical outcome....
You have declared and are using a dataset and dataadapter from which you are populating your list box.
The recordset method is updating your database but the dataset is not updating....thus the array will not update and consequently, the listbox will not update.

Time to move to the .net ways....

Jon

 

[MarkItZero]

Well,
I took your advice, moved into the 21st century with those fancy new-fangled ADO.Net methods...and it now works perfectly!

Thank you both for your help and advice.

Strangely, I went back to find out where I copied the ADODB code from, and it was from a Microsoft Press book called Programing Visual Basic .Net for Access. For some reason the author teaches the ADODB way of accessing data for the first 8 chapters of the book and then not until the end does he lightly touch upon the ADO.Net method. It would have saved me a bunch of time if I had done it the right way first, now I have quite a bit of code to go back and update!