Force Listbox to Refresh

[Randy]

I have button that deletes items from a SQL datatable. The items are
displayed for the user in listbox, which is bound to the dataset. The
code works just fine insofar as it deletes the correct record from the
dataset and the datasource. What I need to make it do is to refresh
the listbox so that it reflects that the record has been deleted.
Right now, the listbox removes the first item from the list and leaves
the deleted item displayed. If I close the form and reopen it, all
appears as it should (e.g. the correct item is gone and the listbox
displays correctly). How can I force the listbox to refresh as if the
form were re-loading.?

Here is the code:

Dim strSQL As String
strSQL = "DELETE Category WHERE Category = @Cat"
Dim cmdDelete As New SqlCommand(strSQL, cn)
cmdDelete.Parameters.AddWithValue("@Cat",
lbCat.SelectedValue)

Try
cn.Open()
Dim intRecordsAffected As Integer
intRecordsAffected = cmdDelete.ExecuteNonQuery()
If intRecordsAffected = 1 Then
CategoryBindingSource.RemoveCurrent()
CategoryBindingSource.MoveFirst()

End If
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
cn.Close()
End Try

Thanks a lot,
Randy

 

[Cor Ligthert [MVP]]

Randy mostly is this the most simple one

listbox.datasource = nothing
listbox.datasource = thedatasource

Cor

 

[Herfried K. Wagner [MVP]]

Randy mostly is this the most simple one

listbox.datasource = nothing
listbox.datasource = thedatasource

.... which can be written as 'ListBox1.DataSource = TheDataSource' too. It's
not necessary to set the property to 'Nothing' there.

 

[Cor Ligthert [MVP]]

Herfried,

You are right, I thought that I had a bad expirience in that, but accoording
all my writing about setting to nothing it would be crazy.

Cor

 

[Randy]

OK, as simple as this looks, it isn't working for me. Since my
original post, I have changed my databinding so that I am now binding
the listbox to a dataview (so that I can sort the data in the
listbox). Here is my current code, which is intended to incorporate
your suggestions:

Dim strSQL As String
strSQL = "DELETE Category WHERE Category = @Cat"
Dim cmdDelete As New SqlCommand(strSQL, cn)
cmdDelete.Parameters.AddWithValue("@Cat",
lbCat.SelectedValue)

Try
cn.Open()
Dim intRecordsAffected As Integer
intRecordsAffected = cmdDelete.ExecuteNonQuery()
If intRecordsAffected = 1 Then
Dim vueCat As New DataView
With vueCat
.Table = ds.Category 'ds is my dataset
.Sort = "Category"
.RowStateFilter = DataViewRowState.CurrentRows
End With

With lbCat
.DataSource = vueCat
.DisplayMember = "Category"
.ValueMember = "Category"
End With
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
cn.Close()
End Try

I think that my problem has something to do with the
DataRowViewState. I've played around with this setting and achieved
different results. However, none of the options works in the various
scenarios that I need to update the binding with (Insert, Delete,
Update).

Any advice is appreciated.
Randy

 

[Randy]

Just to close out this post, I resolved the issue by simply filling my
dataadapter. All the stuff inside the If-Then statement is gone,
replaced with da.fill.

Thanks for the help, Cor and Herfried.