CODEWITHSUNDEEP
vb.netdatagridviewincrement
Muhnamana
Muhnamana

Reputation: 1044

Updating A DataGridView Cell Incrementally

I'm currently having a slight issue duplicating a row and incrementing the sequence number.

So based on a button click, this is how I'm duplicating row 0, duplicated only one time per click.

    Dim dr As DataRow

    For n As Integer = 0 To 0         ' how many dupes you want
        dr = tbl.NewRow
        For c As Integer = 0 To tbl.Columns.Count - 1   ' copy data from 0 to NewRow
            dr.Item(c) = tbl.Rows(0).Item(c)
        Next
        tbl.Rows.Add(dr)            ' add NewRow to datatable
    Next n

Here's how I'm creating the sequence number, pads with leading zeros, which seems to increment, but only after I click the duplicate button, so essentially the last row added, it the duplicated row 0, but doesn't represent the new sequence number needed.

'UPDATE SEQUENCE NUMBER
    i += 1

    Dim value As Integer = i
    Dim r As Integer

    Dim decimalLength1 As Integer = value.ToString("D").Length + 7
    Dim decimalLength2 As Integer = value.ToString("D").Length + 6
    Dim decimalLength3 As Integer = value.ToString("D").Length + 5
    Dim decimalLength4 As Integer = value.ToString("D").Length + 4

    If i >= 0 And i <= 9 Then
        '1 TO 9 FORMAT
        DataGridView1.CurrentCell = DataGridView1.CurrentRow.Cells("sequence")
        DataGridView1.Item(73, r).Value = value.ToString("D" + decimalLength1.ToString())
        'Debug.Print(value.ToString("D" + decimalLength1.ToString()))
    ElseIf i >= 10 And i <= 99 Then
        '10 TO 99 FORMAT
        DataGridView1.CurrentCell = DataGridView1.CurrentRow.Cells("sequence")
        DataGridView1.Item(73, r).Value = value.ToString("D" + decimalLength2.ToString())
        'Debug.Print(value.ToString("D" + decimalLength1.ToString()))
    ElseIf i >= 100 And i <= 999 Then
        '100 TO 999 FORMAT
        DataGridView1.CurrentCell = DataGridView1.CurrentRow.Cells("sequence")
        DataGridView1.Item(73, r).Value = value.ToString("D" + decimalLength3.ToString())
        'Debug.Print(value.ToString("D" + decimalLength1.ToString()))
    ElseIf i >= 1000 And i <= 9999 Then
        '1000 TO 9999 FORMAT
        DataGridView1.CurrentCell = DataGridView1.CurrentRow.Cells("sequence")
        DataGridView1.Item(73, r).Value = value.ToString("D" + decimalLength4.ToString())
        'Debug.Print(value.ToString("D" + decimalLength1.ToString()))
    End If

Row 0 will always have a sequence number of 1, so in theory I need to start incrementing at 2.

Suggestions? Is there a better/cleaner way of doing this?

UPDATE 2

    Dim startSeq As Integer = Convert.ToInt32(tbl.Rows(0).Item(73))

    MsgBox("startSeq = " & startSeq)

    For n As Integer = 0 To NumericUpDown1.Value - 1

        MsgBox("n = " & n)

        dr = tbl.NewRow

        For c As Integer = 0 To tbl.Columns.Count - 1

            dr.Item(c) = tbl.Rows(0).Item(c)

            If c = "73" Then     ' if this is the SEQ column,
                ' add the current seq val to the seq column
                dr.Item(c) = (startSeq + n).ToString("00000000")
            End If
        Next c
        tbl.Rows.Add(dr)
    Next n

Upvotes: 1

Views: 553

Answers (1)

Ňɏssa P&#248;ngjǣrdenlarp
Ňɏssa P&#248;ngjǣrdenlarp

Reputation: 38875

It seems like you should be able to add the sequencer as you create the duplicates. Perhaps make it a method and pass the index of the column which has the sequence string. Something like:

Private Sub DuplicateRows(ColIndx As Integer,
                Dupes As Integer)

    ' start value is Row(0) + 1
    Dim startSeq As Integer = Convert.ToInt32(tbl.Rows(0).Item(ColIndx )) + 1

    For n As Integer = 0 to Dupes -1
        dr = tbl.NewRow

        For c As Integer = 0 To tbl.Columns.Count - 1
             If c = ColIndx Then     ' if this is the SEQ column,
                ' add the current seq val to the seq column
               dr.Item(c) = (startSeq + n).ToString("00000000")
           Else
               ' otherwise copy the data from Row(0)
               dr.Item(c) = tbl.Rows(0).Item(c) 
           End If
        Next c
        tbl.Rows.Add(dr)
    Next n
End Sub

This should initialize each new row with an incremented counter. Is there a better/cleaner way of doing this

a) you should be adding to the DataTable, not the DGV if it is bound

b) (startSeq + n).ToString("00000000") should work to do the padding etc instead of that ugly block of code.

c) Use Option Strict On. If c = "73" ... is nonsense which makes the compiler guess at your intentions. Its is bug food.

d) Hardcoding "73" may work this time, but previously you said it could be anywhere. The code below finds the sequence column based on the name so it can appear anywhere. Rather than a form level var, you could find it just before you make the dupes or even in the Dupes procedure.

e) Dim startSeq As Integer = Convert.ToInt32(tbl.Rows(0).Item(73)) if you examine the answer above, this should be ... + 1 to increment the first value.

Usage:

Private tbl As DataTable             ' table loaded from flat file
Private SeqColIndex As Integer       ' assigned somewhere to
                                   ' point to the "sequence" column

' method to load data
Dim connstr = "Provider=Microsoft.ACE.OLEDB.12.0;..."
Using cn As New OleDbConnection(connstr)
  ...
End Using

' FIND the sequence column for this session
For n = 0 To tbl.Columns.Count - 1
    If tbl.Columns(n).ColumnName.ToLowerInvariant = "sequence" Then
        SeqColIndex = n
        Exit For
    End If
Next

' later to add some rows
Private Sub ButtonAddRow_Click(...

    DuplicateRows(SeqColIndex, NumericUpDown1.Value)
End Sub

Upvotes: 1

Related Questions