none
Adding Rows in Datagridview from database in between 2 dates RRS feed

  • Question

  • How to add rows in a datagridview by computing in between 2 dates

    the computed number of months is the total number of inserted rows in my datagridview

    This is what i want to do:

    for example in my form1's DGV i have from mysql database

    Id      StartDate          endDate              Balance

    1      01-26-2019      4-26-2019          20,000.00

    and after running and clicking a button to form2

    my form2's datagridview will gonna be like this

    PaymentDate               Status           Balance

    01-26-2019                 Unpaid       5,000.00

    02-26-2019                 Unpaid       5,000.00

    03-26-2019                 Unpaid       5,000.00

    04-26-2019                 Unpaid       5,000.00

    is this possible? sorry for my explanation im not good in english

    Please help me i dont have any idea how to do it

    Wednesday, February 13, 2019 12:38 PM

Answers

  • Hello,

    I setup the columns in the IDE.

    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim date2 As Date = #1/23/2019#
            Dim date1 As Date = #5/23/2019#
    
            Dim result = ((date1.Year - date2.Year) * 12) + date1.Month - date2.Month
    
            Dim data As IEnumerable(Of Date) =
                    Enumerable.Range(1, result - 1).Select(Function(x) date2.AddMonths(x))
    
            DataGridView1.Rows.Add(New Object() {date2, "Unpaid", "5000"})
    
            For Each dateItem As Date In data
                DataGridView1.Rows.Add(New Object() {dateItem, "Unpaid", "5000"})
            Next
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by no[one] Monday, February 18, 2019 1:56 PM
    Wednesday, February 13, 2019 1:21 PM
    Moderator
  • Well it would be nice if you had asked this in your initial question. In the future please ask all aspects of your question up front.

    So without knowing the database let's go with the following and if this is not right then creae a new question and ask there with more specifics.

    In the following example each DECLARE element would be a parameter for a command object e.g. you create a connection and a command using say SqlClient provider or OleDb provider.

    Use ExecuteReader to populate a DataTable

    e,g, someDataTable.Load(cmd.ExecuteReader)

    Conceptual SQL

    DECLARE @startDate  AS date = '01-26-2019'
    DECLARE @endDate AS date = '04-26-2019'
    
    SELECT SomeDate
    FROM dbo.Table_1 WHERE dbo.Table_1.SomeDate BETWEEN @startDate AND @enddate
    

    The dates come from my first reply.

    Now if you need each month having actually (in this case the day 26) once the DataTable is loaded check each DataRow data value to determine if the day is correct, if not remove the row.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by no[one] Monday, February 18, 2019 1:56 PM
    Wednesday, February 13, 2019 4:53 PM
    Moderator

All replies

  • Hello,

    I setup the columns in the IDE.

    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim date2 As Date = #1/23/2019#
            Dim date1 As Date = #5/23/2019#
    
            Dim result = ((date1.Year - date2.Year) * 12) + date1.Month - date2.Month
    
            Dim data As IEnumerable(Of Date) =
                    Enumerable.Range(1, result - 1).Select(Function(x) date2.AddMonths(x))
    
            DataGridView1.Rows.Add(New Object() {date2, "Unpaid", "5000"})
    
            For Each dateItem As Date In data
                DataGridView1.Rows.Add(New Object() {dateItem, "Unpaid", "5000"})
            Next
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by no[one] Monday, February 18, 2019 1:56 PM
    Wednesday, February 13, 2019 1:21 PM
    Moderator
  • Thank you for your answer. but what if my 2 dates are from sql? pls help me how to do it

    Wednesday, February 13, 2019 2:16 PM
  • Well it would be nice if you had asked this in your initial question. In the future please ask all aspects of your question up front.

    So without knowing the database let's go with the following and if this is not right then creae a new question and ask there with more specifics.

    In the following example each DECLARE element would be a parameter for a command object e.g. you create a connection and a command using say SqlClient provider or OleDb provider.

    Use ExecuteReader to populate a DataTable

    e,g, someDataTable.Load(cmd.ExecuteReader)

    Conceptual SQL

    DECLARE @startDate  AS date = '01-26-2019'
    DECLARE @endDate AS date = '04-26-2019'
    
    SELECT SomeDate
    FROM dbo.Table_1 WHERE dbo.Table_1.SomeDate BETWEEN @startDate AND @enddate
    

    The dates come from my first reply.

    Now if you need each month having actually (in this case the day 26) once the DataTable is loaded check each DataRow data value to determine if the day is correct, if not remove the row.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by no[one] Monday, February 18, 2019 1:56 PM
    Wednesday, February 13, 2019 4:53 PM
    Moderator