Solved

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

Posted on 2016-11-21
4
42 Views
Last Modified: 2016-11-28
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.
0
Comment
Question by:printmedia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41896936
Have you tried some code? If yes please post?
0
 

Author Comment

by:printmedia
ID: 41897778
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
0
 

Accepted Solution

by:
printmedia earned 0 total points
ID: 41899218
I figured it out. Below is the code I used should anyone need to do the same thing in the future.

Dim con As New SqlConnection
        Dim cmd As New SqlCommand

        con.ConnectionString = "Data Source=myServer;Initial Catalog=myDB;Integrated Security=True"

        con.Open()

        cmd.Connection = con

        cmd.CommandText = "SELECT      InvoiceNo, InvoiceDate, InvoiceDueDate,  DiscountAmt, Balance - DiscountAmt AS AmtDue, CONVERT(VARCHAR(10),InvoiceDueWeekEnding, 101) AS InvoiceDueWeekEnding 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)

        cmd.CommandText = "SELECT DISTINCT CONVERT(VARCHAR(10),InvoiceDueWeekEnding, 101) AS InvoiceDueWeekEnding, InvoiceDueWeekEnding AS NoFormatInvoiceDueWeekEnding FROM  APPaymentSchedule WHERE     (VendorName = @VendorName1) AND (InvoiceDueDate > @InvoiceDueDate1)  ORDER BY NoFormatInvoiceDueWeekEnding"
        cmd.Parameters.AddWithValue("@VendorName1", cmbVendor.Text)
        cmd.Parameters.AddWithValue("@InvoiceDueDate1", txtDueDate.Text)

        Dim ds1 As New DataSet

        sda.Fill(ds1)

        For Each row As DataRow In ds1.Tables(0).Rows

            dt.Columns.Add(row.Item("InvoiceDueWeekEnding"), GetType(Decimal))

        Next
cmd.CommandText = "SELECT     VendorName, InvoiceNo, InvoiceDate, InvoiceDueDate,  (Balance - DiscountAmt) AS AmtDue, DiscountAmt, CONVERT(VARCHAR(10),InvoiceDueWeekEnding, 101) AS InvoiceDueWeekEnding FROM  APPaymentSchedule WHERE     (VendorName = @VendorName2) AND (InvoiceDueDate > @InvoiceDueDate2) ORDER BY InvoiceDueDate "
        cmd.Parameters.AddWithValue("@VendorName2", cmbVendor.Text)
        cmd.Parameters.AddWithValue("@InvoiceDueDate2", txtDueDate.Text)

        Dim ds As New DataSet

        sda.Fill(ds)
        Dim i As Integer
        i = 0

        For Each row2 As DataRow In ds.Tables(0).Rows

            row2 = dt.NewRow()
            row2("InvoiceNo") = ds.Tables(0).Rows(i)(1)
            row2("InvoiceDate") = ds.Tables(0).Rows(i)(2)
            row2("InvoiceDueDate") = ds.Tables(0).Rows(i)(3)
            row2("AmtDue") = 0
            row2("DiscountAmt") = ds.Tables(0).Rows(i)(5)
            row2("InvoiceDueWeekEnding") = ds.Tables(0).Rows(i)(6)

            For Each dc As DataColumn In dt.Columns

  If row2("InvoiceDueWeekEnding") = dc.ToString Then

                    row2(dc.ToString) = ds.Tables(0).Rows(i)(4)

                End If

            Next

            dt.Rows.Add(row2)
            i = i + 1
  Next

  dt.Columns.Remove("InvoiceDueWeekEnding")
        dt.Columns(0).ColumnName = "Invoice Number"
        dt.Columns(1).ColumnName = "Invoice Date"
        dt.Columns(2).ColumnName = "Invoice Due Date"
        dt.Columns(3).ColumnName = "Discount Amount"
        dt.Columns(4).ColumnName = txtDueDate.Text

For k As Integer = 0 To dt.Rows.Count - 1
            For j As Integer = 0 To dt.Columns.Count - 1
                If String.IsNullOrEmpty(dt.Rows(k)(j).ToString()) Then
                    
                    dt.Rows(k)(j) = 0
                End If
            Next
        Next

        con.Close()

        Return dt

Open in new window

0
 

Author Closing Comment

by:printmedia
ID: 41903942
I solved it myself.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question