Dynamically create datagridview columns based on data in SQL table in vb.net Visual Studio
Posted on 2016-11-21
I'm trying to create a form with a datagridview control that will be populated based on the parameters selected by the end user. The problem that I'm running into is the number of columns can fluctuate depending on what data is returned based on the parameters.
I have a table called myTable with the following fields: Vendor, InvoiceNumber, InvoiceDate, InvoiceDueDate, InvoiceAmt, InvoiceDueWeekEnding
The end user will enter 2 parameters: Vendor and InvoiceDueDate
The query will select all invoices whose vendor is the one entered by the end user and whose invoice due dates are on or before the one entered by the end user as a parameter.
The InvoiceDueWeekEnding is the Saturday of the week where the InvoiceDueDate lands (Saturday is the end of our week), so if the invoiceduedate is November 2, 2016, then the InvoiceDueWeekEnding will be November 5, 2016. I would like these InvoiceDueWeekEnding values to appear as column headers in the datagrid with the InvoiceAmt to appear under the column headers for the InvoiceDueWeekEnding.
So, the Vendor, InvoiceNumber, InvoiceDate, InvoiceDueDate columns will always be there so you'll have at least 4 columns, but as far as the "InvoiceDueWeekEnding" columns these will not be known until the end user enters the invoice due date parameter. If the end user enters 12/01/2016 and there aren't any invoices then nothing will display in the datagridview control. But if there are 4 invoices with invoice due dates of 11/9/2016, 11/16/2016, 11/18/2016 and 12/01/2016, then there will be 3 "InvoiceDueWeekEnding" columns with the following column headers: 11/12/2016, 11/19/2016 (you will have 2 invoice amounts for these week ending because of the 2 invoice due dates that are in the week: 11/16/2016 and 11/18/2016) and 12/01/2016 because although 12/01/2016 is in the middle of the week it will not display the Saturday of that week, but if we had an invoice for 11/30/2016 it would it include it under the 12/01/2016 column header.
I have attached an Excel of how I would like to see the information in the datagridview control.
Any help is greatly appreciated.