none
Reload combobox data when source is datatable based on a query RRS feed

  • Question

  • Hi Gurus of VB.Net,

    I have a winform with several comboboxes. The "main" combo box has a datatable based on this code

    Dim strSQL As String = "Select Competition_Idx, Comp_Name as Value1, Format(Comp_Date,'dd/MM/yyyy') as Value2, Comp_Venue, Chairperson, Compere, Scrutineer, Music, CoChair, ascAssoc, imgImage, imgExt FROM dbo.tblCompetitions ORDER BY Comp_Date DESC"
    
                    Using com As SqlCommand = New SqlCommand(strSQL, conn)
                        Dim dr As SqlDataReader = com.ExecuteReader()
                        ct.Load(dr)
                        If Not ct.Columns.Contains("Name") Then
                            ct.Columns.Add("Name", GetType(String))
                        End If
                        cboComp.ValueMember = "Competition_Idx"
                        cboComp.DisplayMember = "Name"
                        For Each row In ct.Rows
                            row.item("Name") = row.item("Value1").padright(45) + row.item("Value2").ToString
                        Next
                        cboComp.DataSource = ct
                        dr.Close()
                    End Using 'com
                    cboComp.SelectedIndex = -1

    ct is declared thus: Private ct As New DataTable at the form level. After selecting a record from the "main" combo box cboComp I set the selectedvalues of the other combo boxes with this code

                cboChair.SelectedValue = CInt(cboComp.Items(cboComp.SelectedIndex).item("Chairperson"))
                cboCoChair.SelectedValue = CInt(cboComp.Items(cboComp.SelectedIndex).item("CoChair"))
                cboCompere.SelectedValue = CInt(cboComp.Items(cboComp.SelectedIndex).item("Compere"))
                cboScrutineer.SelectedValue = CInt(cboComp.Items(cboComp.SelectedIndex).item("Scrutineer"))
                cboMusic.SelectedValue = CInt(cboComp.Items(cboComp.SelectedIndex).item("Music"))
                cboAssoc.SelectedValue = CInt(cboComp.Items(cboComp.SelectedIndex).item("ascAssoc"))

    After I make any changes I have a stored procedure which updates the data on the server. Using Management Console I can verify the data is updated. My dilemma is refreshing/updating/reloading, whatever we want to call it, the datatable & then the combobox datasource. Currently after saving I can go to another record then come back to this one & the changed combo box still reflects the old data.

    I've tried ct.dispose, cboComp.datasource = nothing then re-running the procedure to load the datatable after setting the cboComp.items.clear but I have to be missing a step or incorrect sequence or something.


    Can someone help point me in the right direction please
    Tuesday, January 22, 2019 6:48 PM

Answers

  • Hello,

    In this example a DataTable loaded from a table in a database. Now the code in Button click is a working conceptual example where in your case you would need to iterate saved data against loaded data in the ComboBox by primary key.

    Here the primary key is Identifier as per the following SELECT.

    SELECT 
        C.Identifier, 
        C.CompanyName, 
        CT.ContactTitleId, 
        CT.Title, C.Address, 
        C.City, C.PostalCode, 
        C.Country
    FROM 
        ContactTitle AS CT 
    INNER JOIN 
        Customers AS C ON CT.ContactTitleId = C.ContactTitleId
    ORDER BY 
        CompanyName;

    Here I update one row, one field.

    Public Class Form1
        Private ops As New DatabaseOperations
        Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles Me.Shown
            Dim dataTable = ops.LoadCustomers()
            ComboBox1.DisplayMember = "CompanyName"
            ComboBox1.DataSource = dataTable
    
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            CType(ComboBox1.DataSource, DataTable).
                AsEnumerable().
                FirstOrDefault(Function(row) row.Field(Of Integer)("Identifier") = 2).
                SetField(Of String)("CompanyName", "Test")
    
        End Sub
    End Class
    

    You need to iterate the saved data, find it in the ComboBox and update whatever fields you need too.

    Stored procedures will not do any good, reloading should via setting the DataSource to nothing then re-read the data but I've always use a reload as an absolute last resort, possibly once or twice in over 15 years.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, January 22, 2019 9:24 PM
    Moderator

All replies

  • Hello,

    In this example a DataTable loaded from a table in a database. Now the code in Button click is a working conceptual example where in your case you would need to iterate saved data against loaded data in the ComboBox by primary key.

    Here the primary key is Identifier as per the following SELECT.

    SELECT 
        C.Identifier, 
        C.CompanyName, 
        CT.ContactTitleId, 
        CT.Title, C.Address, 
        C.City, C.PostalCode, 
        C.Country
    FROM 
        ContactTitle AS CT 
    INNER JOIN 
        Customers AS C ON CT.ContactTitleId = C.ContactTitleId
    ORDER BY 
        CompanyName;

    Here I update one row, one field.

    Public Class Form1
        Private ops As New DatabaseOperations
        Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles Me.Shown
            Dim dataTable = ops.LoadCustomers()
            ComboBox1.DisplayMember = "CompanyName"
            ComboBox1.DataSource = dataTable
    
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            CType(ComboBox1.DataSource, DataTable).
                AsEnumerable().
                FirstOrDefault(Function(row) row.Field(Of Integer)("Identifier") = 2).
                SetField(Of String)("CompanyName", "Test")
    
        End Sub
    End Class
    

    You need to iterate the saved data, find it in the ComboBox and update whatever fields you need too.

    Stored procedures will not do any good, reloading should via setting the DataSource to nothing then re-read the data but I've always use a reload as an absolute last resort, possibly once or twice in over 15 years.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, January 22, 2019 9:24 PM
    Moderator
  • Hi,

    You can use SqlDependency to detect changes in the database 

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/5963447d-c956-49cc-8106-9bdf22ca6e55/sql-dependencies-in-vbnet?forum=vbgeneral

    Best Regards,

    Alex




    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 24, 2019 7:07 AM
  • Notes on SqlDependency

    There are a good deal of restrictions and there are steps to setup also.

    Actual coding is very simple but you need to understand the restrictions and follow the steps to configure otherwise the code will not work/throw exceptions.

    Simple code sample

    Sub Initialization()  
        ' Create a dependency connection.  
        SqlDependency.Start(connectionString, queueName)  
    End Sub  
      
    Sub SomeMethod()   
        ' Assume connection is an open SqlConnection.  
        ' Create a new SqlCommand object.  
        Using command As New SqlCommand( _  
          "SELECT ShipperID, CompanyName, Phone FROM dbo.Shippers", _  
          connection)  
      
            ' Create a dependency and associate it with the SqlCommand.  
            Dim dependency As New SqlDependency(command)  
            ' Maintain the refence in a class member.  
            ' Subscribe to the SqlDependency event.  
            AddHandler dependency.OnChange, AddressOf OnDependencyChange  
      
            ' Execute the command.  
            Using reader = command.ExecuteReader()  
                ' Process the DataReader.  
            End Using  
        End Using  
    End Sub   
      
    ' Handler method  
    Sub OnDependencyChange(ByVal sender As Object, _  
        ByVal e As SqlNotificationEventArgs)   
        ' Handle the event (for example, invalidate this cache entry).  
    End Sub  
      
    Sub Termination()  
        ' Release the dependency  
        SqlDependency.Stop(connectionString, queueName)  
    End Sub  


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, January 24, 2019 12:54 PM
    Moderator
  • Hi Karen, Sorry it takes so long to reply. My real job has had me busy. I've used your code for "Button_1" in a sub "SaveData" in my app which I use to immediately save/write changes back to the server data & it works fine. Here's one of the lines which updates my combobox cboComp's datasource datatable with the selectedvalue of a combobox whose selection was changed & saved to the server's table data.
    CType(cboComp.DataSource, DataTable).
    AsEnumerable().
    FirstOrDefault(Function(row) row.Field(Of Integer)("Competition_Idx") = Index).
    SetField("ascAssoc", cboAssoc.SelectedValue)
    Thanks for the help. Sometimes we look deeper than we should when the solution is a lot more simple

    Sunday, January 27, 2019 10:25 AM
  • Hi Alex, Thanks for this. I had a look & for now it's a lot more investigating & learning for me but I see advantages in using this so will definitely learn how
    Sunday, January 27, 2019 10:27 AM