Results 1 to 21 of 21

Thread: Creating rows in table and save them in table

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    136

    Creating rows in table and save them in table

    i have a table and the ammount of rows depend on the month.
    so i get the amount of days form a month
    how can i get it, to be inserted as the ammount of rows in the table and also there is a column days which for every row has to be filled
    Thanks in advance

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Creating rows in table and save them in table

    What "table"? Do you mean a DataTable? A database table? Are you referring to a grid control? Something else? Please provide a FULL and CLEAR explanation of the problem.

    That said, if you want to perform an action a specific number of times then the obvious solution is a For loop. If you want to add N items to a list then loop from 1 to N and add one item per iteration.

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    136

    Re: Creating rows in table and save them in table

    Yes a database tabel

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Creating rows in table and save them in table

    So add the appropriate number of rows to a DataTable and then save the data to the database with a data adapter. You can find an example of that by following the CodeBank link in my signature below and checking out my thread on Retrieving & Saving Data.

    That said, I'm concerned that you may be creating a separate table for every month, which would be a very bad idea. Is that the case?

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    136

    Re: Creating rows in table and save them in table

    Yes and to make things more worse for at least 6 persons every month i am still looking how to tackle that

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Creating rows in table and save them in table

    You're going about it all wrong then. You should do some reading to learn how relational databases. For instance, if you want to store data relating to six people then you start with a Person table that will have six records. You then have one other table with a PersonId column in it. You add the data for all six people to the same table and use the PersonId to specify which Person record it relates to. If you want data for one person then you simply filter by PersonId using a WHERE clause.

    The same goes for this data that you're trying to separate by month. It should all go into just one table that has a date column. To get the data for one month, you use a WHERE clause to filter by date range.

    If you ever find yourself inclined to add a new table simply to accommodate more of the same data then your database design is broken.

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    136

    Re: Creating rows in table and save them in table

    maybe i should do the row count only in a datagridview and with an id for every row linked to the persons id and then the datagridview for every person loaded on their ID.
    edit. You just beat me to it

  8. #8

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    136

    Re: Creating rows in table and save them in table

    Also later on there are some complicated calculation involving the hours and i thought it was better to do that in the server by month as on the pc. but indeed all by personID

  9. #9
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Creating rows in table and save them in table

    Quote Originally Posted by zubenubie View Post
    i thought it was better to do that in the server by month as on the pc
    That's fine but that doesn't require multiple tables for what is basically the same data. As I said, simply use a WHERE clause in your query to filter to a subset.

  10. #10

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    136

    Re: Creating rows in table and save them in table

    anyway now rises the question how to add the rows to the datagridview couldn't find a satisfying answer on google

  11. #11
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,042

    Re: Creating rows in table and save them in table

    Hi,

    since you will need the Days in a Year
    here a sample

    Code:
    'Controls you need:
    '1x ComboBox = cboYear
    '1x Datagridview = DataGridView1
    '1x Button = Button1
    Option Strict On
    
    
    Public Class Form3
        Dim ActivYear As Long
    
        Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            ActivYear = If(ActivYear = 0, Year(Now), ActivYear)
            IniCombos()
        End Sub
    
        Sub IniCombos()
            Dim formatInfo = System.Globalization.DateTimeFormatInfo.CurrentInfo
            For i As Integer = 2010 To 2099
                cboYear.Items.Add(i)
            Next
            cboYear.SelectedIndex = CInt(ActivYear - 2010)
        End Sub
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            BindData()
            FormatDataTable()
            GetData()
            DGVColumsAutoSize(True)
        End Sub
    
        Private Sub BindData()
            DataGridView1.DataSource = GetData()
        End Sub
        Protected Function FormatDataTable() As DataTable
            Dim dt As DataTable = New DataTable()
            ' Create Columns
            dt.Columns.Add("Date", System.Type.GetType("System.String"))
            dt.Columns.Add("PersonId", System.Type.GetType("System.String"))
            dt.Columns.Add("Time in", System.Type.GetType("System.String"))
            dt.Columns.Add("Time out", System.Type.GetType("System.String"))
            Return dt
        End Function
    
        Protected Function GetData() As DataTable
            Dim dt As DataTable = FormatDataTable()
            Dim dr As DataRow
            Dim d As Date = New Date(CInt(cboYear.Text), 1, 1)
            Do While d.Year = CDbl(cboYear.Text)
                Dim dateValue As Date = d
                dr = dt.NewRow
                dr("Date") = d.ToString("dd.MM.yyyy") 'change to you Date format
                dr("PersonID") = ("1") ' just a sample ID
                dr("Time in") = ""
                dr("Time out") = ""
               
                dt.Rows.Add(dr)
                d = d.AddDays(1)
            Loop
    
            Return dt
        End Function
    
        Public Sub DGVColumsAutoSize(ByVal AutoSize As Boolean)
    
            If DataGridView1 Is Nothing Then
                Exit Sub
            End If
    
            Dim ColumnMode As DataGridViewAutoSizeColumnMode
            If AutoSize Then
                ColumnMode = DataGridViewAutoSizeColumnMode.AllCells
            Else
                ColumnMode = DataGridViewAutoSizeColumnMode.None
            End If
    
            With DataGridView1
                For i As Integer = 0 To .Columns.Count - 1
                    .Columns(i).AutoSizeMode = ColumnMode
                Next
            End With
        End Sub
    End Class
    now save the Year dates to your Table

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  12. #12

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    136

    Re: Creating rows in table and save them in table

    Like said the datagridview is allready bound to a databasetable and i would like to know a setup to create the rows in the datagridview to match the monthday. how to get the month days i allready have but with the datagrid view i am strugling.

    Edit...Sorry i just now saw the complete code at home and will see if i can do something with the code as it indeed does fill the datagrid with all the days of the year
    Last edited by zubenubie; Oct 6th, 2017 at 03:28 PM.

  13. #13
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Creating rows in table and save them in table

    If the grid is bound then you don't add anything to the grid. The grids is for the user to interact with. You work with the data source. You can either work with the DataTable or else bind that to a BindingSource and bind that to the grid and then work with the BindingSource. In the former case you'll call NewRow, populate the DataRow and call Rows.Add. In the latter case you'll call AddNew, populate the DataRowView and call EndEdit.

  14. #14

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    136

    Re: Creating rows in table and save them in table

    Well that is really annoying then because i don't want people to addnew row for only 1 day.
    my goal was/would people hit the create button and then the people involved for the month be on tabbed pages so person 1 on tab1 and so on, then on every tabpage the datagridview would be created for that whole particular month in july 31 days (31 rows) February 28days(28 rows) etc.
    All these rows should then also get the uniqueID for passin it to the database table.

    I think i am a little over my head here as i really still trying to understand programming

  15. #15
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Creating rows in table and save them in table

    Quote Originally Posted by zubenubie View Post
    Well that is really annoying then because i don't want people to addnew row for only 1 day.
    Why is that a problem? If you want to add more than one row then add more than one row. Have you forgotten what I wrote in post #2?
    That said, if you want to perform an action a specific number of times then the obvious solution is a For loop. If you want to add N items to a list then loop from 1 to N and add one item per iteration.
    If you have one table containing all the data for every person and you want each person's data to be on a separate TabPage then you can simply retrieve all the data into one DataTable and then create multiple DataViews, each with its RowFilter set so as to expose only data for one PersonId. You can then bind each DataView on a different TabPage. If you're making any changes, you only have to save once for the whole DataTable to save changes from every page.

  16. #16

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    136

    Re: Creating rows in table and save them in table

    Allright got it indeed forgot the for next loop like you said.
    and then now for the rest it is qeuring the data to be filled as daynumbering and adding the personID that is something i can better do after the creating the rows, or do this in the for loop?

  17. #17
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Creating rows in table and save them in table

    The For loop will create the rows and add them to the DataTable. If those rows are supposed to contain data and you know what it is at that time, it's better to add it then rather than going back through the table a second time to add the data.

  18. #18
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,042

    Re: Creating rows in table and save them in table

    Quote Originally Posted by zubenubie View Post
    Well that is really annoying then because i don't want people to addnew row for only 1 day.
    my goal was/would people hit the create button and then the people involved for the month be on tabbed pages so person 1 on tab1 and so on, then on every tabpage the datagridview would be created for that whole particular month in july 31 days (31 rows) February 28days(28 rows) etc.
    All these rows should then also get the uniqueID for passin it to the database table.

    I think i am a little over my head here as i really still trying to understand programming
    Hi,
    with this you will recive the number of rows you need to add.
    Code:
     Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
            Dim myYear As Integer
            Dim myMonth As Integer
    
            myYear = 2017
            myMonth = 10
    
            Dim days As Integer = Date.DaysInMonth(myYear, myMonth)
           
            MsgBox(days) '<-- the amount of rows to add
        End Sub
    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  19. #19

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    136

    Re: Creating rows in table and save them in table

    So what should have been add rows to a databasetable became this:
    A database tables with all hours of the day in half hour periods filled with checkboxes
    then with button create it fills it with all days of the current month with the mousebutton_click you can select multiple cells and change the value to true
    The rowheaders has to contain 2 half hour periods.

    Only thing left now is to make alerts for if there has been enough rest in a 24 hour period and in a 7 day period
    the 24 hour has to be calculated again for every half hour checked
    Name:  1.jpg
Views: 202
Size:  31.7 KB
    Thanks for the help and the pointers

  20. #20
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,042

    Re: Creating rows in table and save them in table

    Hi ,
    if you want to calculate Times use Timespan

    here a small example..
    Code:
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim ts As TimeSpan
            Dim d1, d2 As Date
            Dim d3, d4 As String
    
            d1 = Now
            ts = TimeSpan.Parse("0:05:12")   'minus 5 Min  and 12 sec
            d2 = d1.Subtract(ts)             'use Substract or Add
            d3 = (d2.ToString.Remove(0, 11))
            d4 = (d3.ToString.Remove(5, 3))
    
            TextBox1.Text = d1.ToString
            TextBox2.Text = d2.ToString
            TextBox3.Text = d3.ToString
            TextBox4.Text = d4.ToString
        End Sub

    Edit:

    did you get the Days in Month working ?

    regards
    Chris
    Last edited by ChrisE; Oct 7th, 2017 at 10:54 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  21. #21

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    136

    Re: Creating rows in table and save them in table

    Yes, i did took some time but like said i am a beginner.
    the fact of these time calculations is that it is not working via timespan but now with calculating the columns in a row which are represented in half hour time units
    but these calculations i have to do in the database i think with queries

    for every half hour period it has to go back 24 hrs to check if there are 2 periods of rest with 1 period of minimum of 6 hrs enclosed hrs
    but like i said i think that is more a query

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width