Deleting An Item From A List Box

M

MarkItZero

Freshman
Joined
Apr 10, 2003
Messages
43
Location
under the desk in my office
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:
Visual Basic: 
'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
Visual Basic: 
 tags[/edit]
 
Last edited by a moderator:
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
 
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
 
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
 
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!
 
Back
Top