Solved

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

Posted on 2016-11-21
4
30 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
  • 3
4 Comments
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Have you tried some code? If yes please post?
0
 

Author Comment

by:printmedia
Comment Utility
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
Comment Utility
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
Comment Utility
I solved it myself.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now