asked on

Dynamically create datagridview columns based on data in SQL table in vb.net Visual Studio

Hi all.

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.

Pawan Kumar

Have you tried some code? If yes please post?

printmedia

ASKER

Yes. Below is what I have so far, I have added the first four columns and then I created a For Each block to create the columns for each distinct instant of the InvoiceDueWeekEnding column. I have also attached a screen shot of what it looks like.

Now I'm stuck on how to populate the rows for those invoices whose due date is after the entered due date parameter, which will fill in the first 4 columns and then one of the newly added columns under the correct InvoiceDueWeekEnding column header.

Private Function GetDataTable_DataGridView1() As DataTable

        Dim con As New SqlConnection
        Dim cmd As New SqlCommand

        con.ConnectionString = "Data myServerCatalog=myDB;Integrated Security=True"

        con.Open()

        cmd.Connection = con

        cmd.CommandText = "SELECT     VendorName, InvoiceNo, InvoiceDate, InvoiceDueDate, Balance, DiscountAmt, Balance - DiscountAmt AS AmtDue FROM  APPaymentSchedule WHERE     (VendorName = @VendorName) AND (InvoiceDueDate <= @InvoiceDueDate) ORDER BY InvoiceDueDate "
        cmd.Parameters.AddWithValue("@VendorName", cmbVendor.Text)
        cmd.Parameters.AddWithValue("@InvoiceDueDate", txtDueDate.Text)

        Dim sda As New SqlDataAdapter(cmd)
        Dim dt As New DataTable()

        DataGridView1.DataSource = Nothing

        sda.Fill(dt)

        DataGridView1.DefaultCellStyle.WrapMode = DataGridViewTriState.True     'Word wrap 
        DataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells  'Adjust the height of the rows
        DataGridView1.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter    'Center in the middle cell contents
        DataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter   'Center in the middle the column headers
        DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige
        DataGridView1.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableWithoutHeaderText

        DataGridView1.AutoGenerateColumns = False

        DataGridView1.ColumnCount = 5   'Set Columns Count

        'Add Columns

        DataGridView1.Columns(0).Name = "InvoiceNo"
        DataGridView1.Columns(0).HeaderText = "Invoice Number"
        DataGridView1.Columns(0).DataPropertyName = "InvoiceNo"
        DataGridView1.Columns(0).Width = 100

        DataGridView1.Columns(1).Name = "InvoiceDate"
        DataGridView1.Columns(1).HeaderText = "Invoice Date"
        DataGridView1.Columns(1).DataPropertyName = "InvoiceDate"
        DataGridView1.Columns(1).Width = 100

        DataGridView1.Columns(2).Name = "InvoiceDueDate"
        DataGridView1.Columns(2).HeaderText = "Invoice Due Date"
        DataGridView1.Columns(2).DataPropertyName = "InvoiceDueDate"
        DataGridView1.Columns(2).Width = 100

        DataGridView1.Columns(3).Name = "AmtDue"
        DataGridView1.Columns(3).HeaderText = txtDueDate.Text
        DataGridView1.Columns(3).DataPropertyName = "AmtDue"
        DataGridView1.Columns(3).Width = 200

        DataGridView1.Columns(4).Name = "DiscountAmt"
        DataGridView1.Columns(4).HeaderText = "Discounts Taken"
        DataGridView1.Columns(4).DataPropertyName = "DiscountAmt"
        DataGridView1.Columns(4).Width = 200

        ''''''''''''''''''''''''''''
        cmd.CommandText = "SELECT DISTINCT InvoiceDueWeekEnding FROM  APPaymentSchedule WHERE     (VendorName = @VendorName1) AND (InvoiceDueDate > @InvoiceDueDate1)  "
        cmd.Parameters.AddWithValue("@VendorName1", cmbVendor.Text)
        cmd.Parameters.AddWithValue("@InvoiceDueDate1", txtDueDate.Text)

        Dim sda1 As New SqlDataAdapter(cmd)
        Dim dt1 As New DataTable()
        sda1.Fill(dt1)

        For Each row As DataRow In dt1.Rows
            Dim nc As New DataGridViewTextBoxColumn
            nc.Name = row.Item("InvoiceDueWeekEnding")
            nc.Width = 100
            DataGridView1.Columns.Add(nc)

        Next

        ''''''''''''''''''''''''''


        con.Close()

        Return dt

    End Function

Open in new window

So for example (using the attached screenshot), there is another invoice number that would be inserted into the datagridview control but under the "11/22/2016" column it would show 0, then a discountamt under the "Discounts Taken" column, 0 under the "12/10/2016" column and all the other columns except for the "12/31/2016" where the AmtDue would be displayed. This is the part I'm stuck on.
DataGridView-ScreenShot.png

ASKER CERTIFIED SOLUTION

printmedia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

printmedia

ASKER

I solved it myself.