Question Activate, Deactivate, then reactivate a ListBox DataSource

[Cobalt]

:Edit: Now with Video!! Take a look at my third post, fourth post in total


Hello all,

This is my first post, so let me know if I need to add any more information. I have also done a quick search and found nothing

Scenario:
I have Checkbox and a list box, checking the checkbox should:
a) Enable the List box,
b) Run a SQL query against a MySQL database (and should return 2 rows),
c) The data returned should bind with the Listbox, using both Display and ValueMember.

When the checkbox is unchecked:
a) The Listbox is disabled,
b) The Listbox is emptied.

The problem:
Everything above works ok, but if after Checking, Unchecking and then checking again the list box not display any contents like it does when it is checked initially.

The checking/unchecking of the box makes no difference until the form is closed and re opened. On the form being re-opened the checkbox works for one cycle then stops.

The odd:
I placed a button on the form, that when clicked shows a message box stating the number of rows in the listbox.
On form load the msgbox states 0
After checking the checkbox the msgbox states 2 and the listbox has two items (correct as the DB only has two items at the moment)
After unchecking the checkbox the msgbox states 0 and the listbox is empty
After checking the checkbox (again) the msgbox states 2 but the list box is empty!

The code is setup with a checkbox_CheckedChanged sub and a function called 'loadlistbox'.

the _CheckedChanged sub basically calls the function and passes a few parameters, one of which is the status of the checkbox.

If the Function gets the status as 'checked' it created the db connection runs the sql and sets the listbox.DataSource as the returned MySqlDataAdapter

If the Function gets the status as 'unchecked' it sets the listbox.DataSource = Nothing and the clears the listbox via listbox.items.clear

Can any kind soul help me figure out why the list box does not physically display the items on a 're-check' of the box but the listbox.items.count still sees them!

I can post code if needed. Please feel free to move this if it is in the wrong section!

Thanks, and kind regards.
Cobalt

 

[MattP]

Without your code it's hard to tell what's happening. I would use the CheckedChanged event of the CheckBox and enable or disable your Listbox based on whether it's checked or not.

    Private Sub CheckBox1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) _
        Handles CheckBox1.CheckedChanged

        If CheckBox1.Checked = True Then
            EnableListboxMethod()
        Else
            DisableListboxMethod()
        End If

    End Sub

 

[Cobalt]

Thanks for your reply, here is some code to help explain it all (I hope)

Legend-
cbAddSource - Checkbox
lbSource - Listbox that needs populating
txtAddSource - textbox that is not really important to this example

Checkbox

'Checkbox to see if Add Source has been selected
    
Private Sub cbAddSource_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chbAddSource.CheckedChanged
        
' Database has multiple tables, each with ID and Name. Each ID/Name has
' the database name prefixed, e.g table1_ID, table1_Name, table2_ID, table2_Name. In this case the table is called callSource
Dim dbFieldName As String = "callSource"

'SQL query that needs to run
Dim sql_query As String = "Select * from callsource where callSource_Active != 0"

        If chbAddSource.Checked = True Then

            Dim checkboxStatus As String = "true"
            
            lbSource.Enabled = True
            'call to Function, passing listbox to change, the query to run and the status of this checkbox       
            loadlistbox(lbSource, dbFieldName, sql_query, checkboxStatus)

            txtAddSource.Enabled = True
        Else
            ' No Rows available
            ' Empty the listbox of DataSource and clear items

            Dim checkboxStatus As String = "false"

            loadlistbox(lbSource, dbFieldName, sql_query, checkboxStatus)
            ' Disable the various parts of the form
            txtAddSource.Enabled = False
            btnAddSource.Enabled = False
            lbSource.Enabled = False
        End If
    End Sub

The Function:

Private Sub loadlistbox(ByVal lbToLoad As ListBox, ByVal dbFeildName As String, ByVal sql_query As String, ByVal checkboxName As String)

'Creates new DB connection        
Dim conn As New MySql.Data.MySqlClient.MySqlConnection

        If checkboxName = True Then

            Dim myCommand As New MySqlCommand
            Dim myAdapter As New MySqlDataAdapter
            Dim myData As New DataTable

            ' Create connection
'frmCallLog is another form with dbconnect db connect function. This works.
            conn = frmCallLog.dbConnect(conn)
            conn.Open()

            'Populate MySQL command with data
            myCommand.Connection = conn

            'sql_query passed into at sub level
            myCommand.CommandText = sql_query
            myAdapter.SelectCommand = myCommand
            myAdapter.Fill(myData)

            If myData.Rows.Count > 0 Then

                'Return has at least one row

                ' Sets the Display of the listbox being worked on to the Name field stored in the DB

                lbToLoad.DataSource = myData
                lbToLoad.ValueMember = dbFeildName & "_ID"
                lbToLoad.DisplayMember = dbFeildName & "_Name"
                lbToLoad.Enabled = True
                lbToLoad.Update()
                Me.Refresh()
                
            Else
                lbToLoad.Items.Add("None available")

            End If
            myCommand.Dispose()
            myAdapter.Dispose()
            myData.Clear()

        Else
            lbToLoad.DataSource = Nothing
            lbToLoad.Items.Clear()
            lbToLoad.Refresh()
        End If
        'myData.Dispose()
        conn.Close()
        conn.Dispose()
    End Sub

Hope this makes sense

 

[Cobalt]

Beacause this is so odd, and to test you would need a MySql db, I have taken a video of what happens.

It shows my form, the checkbox and listbox, along with the button press that displays the Count of the list box

Its a small avi of about 40 seconds, with the problem plain to see at about 20 seconds in.

Thanks!