none
Populate 3 Datagridview depending on Combobox selected value RRS feed

  • Question

  • Hello!

    I have a DataGridView that is populated according to Combobox1 selected value (defined in Design Mode, not in the code part). I use dataset and bindingsource, and have a ParentTableAdapter and ChildTableAdapter1, ChildTableAdapter2 and ChildTableAdapter3. The ChildTableAdapter2 and 3 have a query with conditions, and are working fine retrieving data from database.

    I can't figure out how populate the second and third DataGridView depending on the same Combobox that populates the first DGV.

    Thank you!

    Thursday, December 14, 2017 12:49 PM

Answers

  • Hi Joy,

    Hopefully what I provided makes sense even though the tables and field names I used are different than yours, the concept is the same as in the Fill method for the child DataGridView controls, the DataSet and TableAdapters.

    You need not read the following as it has nothing to do with your problem other than the idea that when looking for a solution we must sometimes look at a similar problem to get your own solution.

    I have two table that are related and want the count where the child table has a zero count on the parent. Had never done this but did research and found several examples (of course their table names and fields are different) and adapted my tables and columns to the examples and finally came up with a solution.

    Task, find states with zero cities.

    End result to get zero counts (or we can do greater than zero)

    SELECT  SC.StateIdentifier,
            SC.Name,
            SC.CityCount
    FROM    ( SELECT    S.StateIdentifier,
                        S.Name,
                        COUNT(CASE WHEN C.CityIdentifier > 0 
    					           THEN C.CityIdentifier
                                   ELSE NULL
                              END) CityCount
              FROM      US_States S
                        LEFT JOIN US_Cities C ON S.StateIdentifier = C.StateIdentifier
              GROUP BY  S.StateIdentifier ,
                        S.Name
            ) SC
    WHERE   SC.CityCount = 0;

    Results with 0 or greater than 0

    The lesson here is to adapt :-) Same goes for your task. Also to have a good database schema e.g.you don't need Year as Year can be taken from Date if the Year field is the same as Date year


    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

    • Marked as answer by joyi6388 Friday, December 15, 2017 6:13 PM
    Friday, December 15, 2017 3:08 PM
    Moderator

All replies

  • Why would populating the second and third DataGridView be any different than the first? Are you somehow binding ComboBox1 to the first DataGridView or is it unbound?

    If you have code that demonstrates how the ComboBox is being used to populate the DataGridView you may want to post it.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, December 14, 2017 1:37 PM
  • Hello,

    The following may or may not fit your question but the suggestions are working with TableAdapter/DataSet/BindingSource set in the IDE.

    First question, are all three tables related e.g. customer, orders, order details for example? When it’s time to populate the second and third DataGridView controls, are they pre-setup with DataGridViewColums that were generated via the TableAdapters or are the two child DataGridView controls pre-setup with data in them already?

    If all three have data already with relationships between (for example purpose) customer, orders and order details then we are looking at working with the BindingSource filter while if you populate the first DataGridView and no data in the other two with the relations set it should be the same, the second and third DataGridView have their relations set then loading the master table should be reflected in the two child DataGridView controls.

    Here is what I mean by setting up the master detail (taken from a C# code sample I did on TableAdapters)

    ordersTableAdapter.Fill(Me.northWindDataSet.Orders)
    Me.customersTableAdapter.Fill(Me.northWindDataSet.Customers)
    
    ' 
    '    * Manually added, next two lines so we have the master and details 
    '    * working together. 
    '     
    ordersBindingSource.DataSource = customersBindingSource
    ordersBindingSource.DataMember = northWindDataSet.Relations(0).RelationName

    It's not doing three table relationship yet it's the same pattern. The following code sample (not TableAdapter method but the concept is the same does three relations).

    So if the above is not helpful I need more details such as I asked above for clarity.


    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, December 14, 2017 1:41 PM
    Moderator
  • Hi Paul,

    Yes Combobox1 is binding to the first DataGridView trought its properties at design mode, I have no written code for the Combobox1. The Selected Value in Combobox1 is binding to ChildBindingSource1.ID field.

    Thursday, December 14, 2017 1:45 PM
  • Hello Karen,

    Honestly I don't understand very well your questions, but I'm trying to apply your example to see if its works.

    I had DataSet1 with ParentTable (id, year, code), ChildTable1 (id, year, number, name). They are related through ID and YEAR. This relationship works fine, setting the Combobox1 selected value to ParentTableChildTableBindingSource "ID" field. The DGV1 datasource is ParentTableChildTableBindingSource.

    Now I created ChildTable2 and ChildTable3, that actually are same Table from SQL, lets call it "SQLTable".

    ChildTable1, ChildTable2 and ChildTable3 are created from SQLTable, but ChildTable2 and ChildTable have a WHERE query condition. I had related the second and third childtables to ParentTable as well (ID, Year). Also created DGV2 and DGV3 for those ChildTables.

    My goal is this:

    The thing is DGV1 is showing all the bills that I have to pay. DGV2 is supposed to show the bills that will be expired in 5 days or less (that's the why of WHERE in the query) and DGV3 have to show the expired bills.

    Maybe there is a much simpler way to do it and I am just messing the things up. The final objective is show in another DGVs the next expiring bills and expired bills.

    Thanks!

    Thursday, December 14, 2017 2:15 PM
  • I have ideas for this but can't validate them without testing the ideas out as I'm at work with no data to replicate what you have. If a solution is not found by the time I'm off work I will put something together.

    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, December 14, 2017 3:28 PM
    Moderator
  • Thank you Karen,

    I kept trying without success. I thought maybe can I populate de DGV2 and DGV3 using the query I putted in the DataSet (the ones with the WHERE clause), but I couldn't make it yet.

    Thursday, December 14, 2017 7:02 PM
  • Hello,

    Here is a question, you indicated that two tables are related via id and year but you also want to get bills that will be expired in five days or less, this would mean you need to have a date field because you can get bills in a five day period with only a year unless I'm missing something.


    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, December 14, 2017 9:11 PM
    Moderator
  • Okay, didn't hear back on the day/year question, had time to slap a concept together so here we go (and I will tell you there is no ComboBox, I focused on getting the two DataGridView controls to show past and upcoming bills).

    Here is my database structure, does not match yours but perhaps it will work for you with adjustment or give you an idea how to modify your structure. Note the stored procedure! I use it to multi task e.g. pass 0 to it get five days in the past, pass 1 get five days ahead.

    Data used

    The Stored procedure

    CREATE PROCEDURE [dbo].[uspBillingsBeforeOrAfterRange] (@Days AS BIT) AS
    BEGIN
    IF @Days = 0
        SELECT  C.id ,
                C.CompanyName ,
                CONVERT(VARCHAR(10), B.DueDate, 101) AS Due
        FROM    Company AS C
                INNER JOIN Billings AS B ON C.id = B.CompanyId
        WHERE   DAY(B.DueDate) BETWEEN ( DAY(GETDATE()) - 5 ) AND ( DAY(GETDATE()) );
    ELSE
        SELECT  C.id ,
                C.CompanyName ,
                CONVERT(VARCHAR(10), B.DueDate, 101) AS Due
        FROM    Company AS C
                INNER JOIN Billings AS B ON C.id = B.CompanyId
        WHERE   DAY(B.DueDate) BETWEEN ( DAY(GETDATE()) ) AND ( DAY(GETDATE()) )
                + 5;
    END

    Form code

    Public Class Form1
    
        ''' <summary>
        ''' Here is what I did to get the results.
        ''' 1.  Dropped Companies onto the form which generated things like a BindingSource,
        '''     BindingNavigator, and adapters.
        ''' 2.  Dropped the stored procedure on the form (first indicated it should be a DataGridView).
        ''' 3.  Repeated step 2.
        ''' 4.  Copied the TableAdapter
        ''' 5.  Pasted the TableAdapter which appended 1 to the end of the name.
        ''' 6.  Removed the BindingNavigator and a TextBox and button which was added for 
        '''     the stored procedure and removed the code for that button.
        ''' 7.  Tweaked the generated code for the Fill methods which accepted input from a TextBox 
        '''     placed onto the form for the stored procedure.
        ''' 8.  Since the TextBox was removed created two string variables to hold data that the 
        '''     TextBox was holding.
        ''' 9.  Did the first fill then copies the structure and data to one of the bottom 
        '''     DataGridView controls
        ''' 10. Did the 2nd fill (which overwrite the first fill, chew on that one but 
        '''     I saw it coming hence the .Copy method)
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            CompanyTableAdapter.Fill(Me.FinancesDataSet.Company)
    
            Dim beforeValue As String = "0"
            Dim afterValue As String = "1"
    
            Me.UspBillingsBeforeOrAfterRangeTableAdapter.Fill(
                FinancesDataSet.uspBillingsBeforeOrAfterRange,
                New Boolean?(CType(beforeValue, Boolean)))
    
    
            UspBillingsBeforeOrAfterRangeDataGridView.DataSource =
                FinancesDataSet.Tables("uspBillingsBeforeOrAfterRange").Copy
    
            UspBillingsBeforeOrAfterRangeTableAdapter1.Fill(
                FinancesDataSet.uspBillingsBeforeOrAfterRange,
                New Boolean?(CType(afterValue, Boolean)))
    
        End Sub
    
    End Class
     

    NOTE: The above is a tad tricky, you may ask, is there an easier way? Sure is but that would move away from TableAdapters (generated for you) and move into the realm of coding rather than code generation. How long did this take me? About just over an hour which included creating the database. Will it be crystal clear, nope, but this is what you have to work with as you wanted to keep on the current course with generated classes.

    The look (yeah it's not pretty).

    Want to try it? The link below is the source for VS2015 or VS2017, might open in VS2013, not sure to lazy to start it up as I have other things to attend too.

    https://1drv.ms/u/s!AtGAgKKpqdWjiQxI_Etgk4oUSmIV

    Hope this helps in one way or another :-) If not I had semi-fun creating it.


    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, December 14, 2017 11:06 PM
    Moderator
  • Hi joyi6388,

    According to your description, you want to populate three datagridview by one combobox select value ID, and you have three childtable and one parenttable. Am i right? You said that you have populated into datagridview 1 by combobox select value ID, so you just get one record in the datagridview 1? Now you want to populate into datagridview2 and 3, do you consider to do this by code? You can take a look the following example.

    http://www.aspforums.net/Threads/268292/Solved-Filter-and-populate-DataGridView-based-on-ComboBox-selection-in-Windows-Forms-WinForms-Application-using-C-and-VBNet/

    Best Regards,

    Cherry


    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.

    Friday, December 15, 2017 3:10 AM
    Moderator
  • Hi joyi6388,

    According to your description, you want to populate three datagridview by one combobox select value ID, and you have three childtable and one parenttable. Am i right? You said that you have populated into datagridview 1 by combobox select value ID, so you just get one record in the datagridview 1? Now you want to populate into datagridview2 and 3, do you consider to do this by code? You can take a look the following example.

    http://www.aspforums.net/Threads/268292/Solved-Filter-and-populate-DataGridView-based-on-ComboBox-selection-in-Windows-Forms-WinForms-Application-using-C-and-VBNet/

    Best Regards,

    Cherry


    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.

    That would not work (unless they stray from the current path) as they are working solely with TableAdapters.

    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

    Friday, December 15, 2017 10:03 AM
    Moderator
  • Hello, I'm looking at your example Karen,

    Sorry, my mistake, I also have Date (the day we received the bill), ExpireDate fields on ChildTable.

    This is the query I have in dataset for ChildTableAdapter1 (showing in DGV2):

    SELECT        ID, Name, Number, Year, Date, expireDate
    FROM            ChildTable
    WHERE        (Pagado = 'False') AND (Pagado2 = 'False') AND (Credito = 'True') AND (DATEDIFF(dd, GETDATE(), ExpireDate) <= 5) AND (DATEDIFF(dd, GETDATE(), ExpireDate) >= 0)

    For ChildTableAdapter is (showing in DGV1):

    SELECT        *
    FROM           ChildTable
    
    That's why I said maybe there is a way to use the WHERE query into ComboboxSelectionChanged or something like that to populate the DGV2 and DGV3

     I will make feedback as I finish looking (and trying to understand :S haha) your code.

    Thanks!

    Friday, December 15, 2017 1:49 PM
  • Hi Joy,

    Hopefully what I provided makes sense even though the tables and field names I used are different than yours, the concept is the same as in the Fill method for the child DataGridView controls, the DataSet and TableAdapters.

    You need not read the following as it has nothing to do with your problem other than the idea that when looking for a solution we must sometimes look at a similar problem to get your own solution.

    I have two table that are related and want the count where the child table has a zero count on the parent. Had never done this but did research and found several examples (of course their table names and fields are different) and adapted my tables and columns to the examples and finally came up with a solution.

    Task, find states with zero cities.

    End result to get zero counts (or we can do greater than zero)

    SELECT  SC.StateIdentifier,
            SC.Name,
            SC.CityCount
    FROM    ( SELECT    S.StateIdentifier,
                        S.Name,
                        COUNT(CASE WHEN C.CityIdentifier > 0 
    					           THEN C.CityIdentifier
                                   ELSE NULL
                              END) CityCount
              FROM      US_States S
                        LEFT JOIN US_Cities C ON S.StateIdentifier = C.StateIdentifier
              GROUP BY  S.StateIdentifier ,
                        S.Name
            ) SC
    WHERE   SC.CityCount = 0;

    Results with 0 or greater than 0

    The lesson here is to adapt :-) Same goes for your task. Also to have a good database schema e.g.you don't need Year as Year can be taken from Date if the Year field is the same as Date year


    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

    • Marked as answer by joyi6388 Friday, December 15, 2017 6:13 PM
    Friday, December 15, 2017 3:08 PM
    Moderator
  • Thank you Karen for your help,

    yes of course I've done many parts of my app adapting existing code, because I'm not a developer or programmer. Now everything is working fine.

    Thanks again!

    Friday, December 15, 2017 6:13 PM