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]it's more readable with

 tags[/edit]

Edited April 28, 2003 by Robby

[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, you're "If ArryEditUsers.Count > 0 Then" won't 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 I'll try and duplicate this to see what the problem might be :p

 

-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:p

[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!