Resolved Listbox selection of sql data items

[kwhelchel]

Hello, First off thanks for all the help I have been given from people here

The pic that I am posting is where I am at this time with things. Listboxes go from left to right in order. So what I am doing is in listbox1 is reports that will be built to run. Listbox 2 is report object/filters to select if needed. listbox 3 is the reflected data that can be selected from the object selected in listbox 2. Data that is in listbox 3 will be from a sql database. Listbox 4 will be the data that will set parameters for the report to be run/filtered by after selecting the view report button. So where I am stuck at is the flow to listbox 4 from 3 and then to run with the view report button.
Looking for some ideas and direction on this.
Thanks

 

[NoUserHere]

• Build a class instead.
• Move to WPF.
• Bindings in WPF would work so well here.
• For your view report, you can create a model view form to present your data of whatever it is meant to display.

 

[kwhelchel]

• Build a class instead.
• Move to WPF.
• Bindings in WPF would work so well here.
• For your view report, you can create a model view form to present your data of whatever it is meant to display

OK I will look at the class to see what it is. and as far as WPF I am not familiar with it either

 

[jmcilhinney]

While learning and using WPF is not a bad idea, if you're already using WinForms then there's no particular reason that you can't stick with it if it does what you need. You can fairly easily use some master/detail data-binding here.

The first problem is that this:

Listbox 2 is report object/filters to select if needed. listbox 3 is the reflected data that can be selected from the object selected in listbox 2.

doesn't make it clear what the relationship between those two lists is. I'm guessing that the date list is retrieved from a database using the previous selection as a filter. From here, I'll concentrate on the last relationship. What you can do is execute two queries to populate two DataTables in a DataSet with a relation between them. The first DataTable contains all the dates and the second DataTable contains all the child records related to all those dates. With a DataRelation between the DataTables and some appropriate data-binding, the last list will filter automatically when a selection is made in the third list. The following example requires a form containing two ListBoxes and two BindingSources named parentListBox, childListBox, parentBindingSource and childBindingSource:

private void Form1_Load(object sender, EventArgs e)
{
    BindData(GetData());
}

private DataSet GetData()
{
    // The data would normally be retrieved from a database but canned data is used for this example.

    var data = new DataSet();
    var parentTable = data.Tables.Add("Parent");
    var childTable = data.Tables.Add("Child");

    parentTable.PrimaryKey = new[] { parentTable.Columns.Add("ParentId", typeof(int)) };
    parentTable.Columns.Add("Date", typeof(DateTime));

    childTable.PrimaryKey = new[] { childTable.Columns.Add("ChildId", typeof(int)) };
    childTable.Columns.Add("ParentId", typeof(int));
    childTable.Columns.Add("Name", typeof(string));

    data.Relations.Add("ParentChild", parentTable.Columns["ParentId"], childTable.Columns["ParentId"]);

    for (var parentId = 1; parentId <= 12; parentId++)
    {
        parentTable.Rows.Add(parentId, new DateTime(2000 + parentId, parentId, parentId));

        for (var childId = 1; childId <= 5; childId++)
        {
            childTable.Rows.Add((parentId - 1) * 12 + childId, parentId, $"Child {parentId}.{childId}");
        }
    }

    return data;
}

private void BindData(DataSet data)
{
    // Bind the parent BindingSource to the parent DataTable.
    parentBindingSource.DataMember = "Parent";
    parentBindingSource.DataSource = data;

    // Bind the child BindingSource to the parent/child DataRelation.
    childBindingSource.DataMember = "ParentChild";
    childBindingSource.DataSource = parentBindingSource;

    // Bind both BindingSources to the corresponding ListBoxes.

    parentListBox.DisplayMember = "Date";
    parentListBox.ValueMember = "ParentId";
    parentListBox.DataSource = parentBindingSource;

    childListBox.DisplayMember = "Name";
    childListBox.ValueMember = "ChildId";
    childListBox.DataSource = childBindingSource;
}

That's all there is to it. Once the binding is done, you'll see all the parent records in the parent list and the child list will be filtered based on the parent selection.

 

[NoUserHere]

Maybe its just me who likes to introduce threading when dealing with data in every application, and maybe that's just the way I like to do things, but it certainly wouldn't hurt to learn how to use threads or use a thread pool when dealing with any amount of data being exchanged between worker methods and a well written data model. It would also be very good to learn these practices and never use your UI for holding data, and to always update your UI by delegating between worker threads and the data models responsible for updating your UI. You should start learning all of this now, because when Winforms dies out eventually, you will find this is the way its done whenever you do start working in WPF. As a beginner, you should also know that its bad ethic to use your UI as your data layer.

 

[kwhelchel]

there will be about 4 sql tables pulling from. one will have close info as far as closeid and start date and end date for the day. The next one which is an orders table that will have a closeid and start and end dates and times for the transaction done along with a list of other data columns to pull from. The other two will have other info and will reference the close id and start and end times also.
If that helps with any info

 

[Skydiver]

So is closeid your primary key? Or do you have a compound primary key: closeid, startDate, endDate? Or does it even get worse: closeid, startDate, startTime, endDate, endTime ?

Part of me feels like there is really wrong with the way the relationships among your tables were setup. It feels more like someone just converted a pen and paper ledger system into a database without taking time to normalize the data.

 

[kwhelchel]

this i a a legacy system started in 1992 and closeid should be my primary key and the start date and close date and times are more for how the day was done, now a closeid could have a start date of 21st and a end date of 23rd if they don't close out .
this is where the date realtionship comes into play as the report will reference start date and close date on the report

 

[Skydiver]

If closeid is truly a primary key, then your initial query should only be a date range to determine the closeid of interest. Once you have the closeid, then you should be able to just query all 4 tables with the condition WHERE closeid = @idOfInterest. You wouldn't need that set of cascading queries for each list box.