locked
looping rows in a datacolumn RRS feed

  • Question

  • I have a table, where I want to organize the data in a column and if there are any blanks between rows of data, move the data.. such as ..

    row1: Name1

    row2: Name2

    row3: Name3

    row4:

    row5: Name6.........

    I have four columns like this in a table with 15 columns...    

    How can I loop through the column and modify the data?

    Sunday, August 7, 2016 1:14 AM

Answers

  • Hi

    Here is an example, in the form of a test Project. This example has been tested up 500 rows of 15 columns where it becomes a time hog. If you plan/have more than 500 rows then best ignore this method - or, put it into a BackGroundWorker to do it in the background keeping the UI live.

    This example generates a bunch of random data, including random'blanks', and filters out the blanks on button click.

    ' new Form1 with a blank DataGridView
    ' and a Button1
    Option Strict On
    Option Infer Off
    Option Explicit On
    Public Class Form1
        Dim myTable As New DataTable("Table1")
        Dim rand As New Random
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            ' random data for this example
            With myTable
                For i As Integer = 1 To 15
                    .Columns.Add(i.ToString, GetType(String))
                Next
                For i As Integer = 1 To 240
                    Dim lst As New List(Of String)
                    For j As Integer = 0 To 14
                        lst.Add("Name" & i.ToString)
                    Next
                    If rand.Next(2, 10) > 2 Then
                        lst(rand.Next(0, lst.Count)) = Nothing
                    End If
                    .Rows.Add(lst(0), lst(1), lst(2), lst(3), lst(4), lst(5), lst(6), lst(7), lst(8), lst(9), lst(10), lst(11), lst(12), lst(13), lst(14))
                Next
            End With
            DataGridView1.DataSource = myTable
            DataGridView1.AutoResizeColumns()
        End Sub
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            For pass As Integer = 0 To 1
                For c As Integer = 0 To myTable.Columns.Count - 1
                    Dim b As Integer = -1
                    For i As Integer = 0 To myTable.Rows.Count - 1
                        If myTable.Rows(i).Item(c).ToString = Nothing Then
                            b += 1
                            For j As Integer = i + 1 To myTable.Rows.Count - 1
                                myTable.Rows(j - 1).Item(c) = myTable.Rows(j).Item(c)
                            Next
                        End If
                    Next
                    For k As Integer = myTable.Rows.Count - b To myTable.Rows.Count - 1
                        myTable.Rows(k).Item(c) = Nothing
                    Next
                Next
            Next
        End Sub
    End Class
    


    Regards Les, Livingston, Scotland

    Sunday, August 7, 2016 4:00 AM

All replies

  • Hi

    Do you mean that the data in a column with a 'blank' is moved up with no regard for the data in the other columns?


    Regards Les, Livingston, Scotland

    Sunday, August 7, 2016 1:38 AM
  • Here is the basic logic

    Dim dt As New DataTable
    dt.Columns.Add(New DataColumn With {.ColumnName = "FirstName", .DataType = GetType(String)})
    dt.Rows.Add(New Object() {"Karen"})
    dt.Rows.Add(New Object() {Nothing})
    dt.Rows.Add(New Object() {"Mary"})
    dt.Rows.Add(New Object() {"Jim"})
    
    For row As Integer = 0 To dt.Rows.Count - 1
        If Not IsDBNull(dt.Rows(row).Item("FirstName")) Then
            If dt.Rows(row).Field(Of String)("FirstName") = "Mary" Then
                dt.Rows(row).SetField(Of String)("FirstName", "Mary Jane")
            End If
        Else
            dt.Rows(row).SetField(Of String)("FirstName", "Was empty")
        End If
    Next
    

    I used one column of type string, the same works for other data types. Note I check to see if the data is not null via IsDBNull and Field(Of T) to read data, SetField(Of T)("ColumnName","New value")


    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

    Sunday, August 7, 2016 2:20 AM
  • Hi

    Here is an example, in the form of a test Project. This example has been tested up 500 rows of 15 columns where it becomes a time hog. If you plan/have more than 500 rows then best ignore this method - or, put it into a BackGroundWorker to do it in the background keeping the UI live.

    This example generates a bunch of random data, including random'blanks', and filters out the blanks on button click.

    ' new Form1 with a blank DataGridView
    ' and a Button1
    Option Strict On
    Option Infer Off
    Option Explicit On
    Public Class Form1
        Dim myTable As New DataTable("Table1")
        Dim rand As New Random
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            ' random data for this example
            With myTable
                For i As Integer = 1 To 15
                    .Columns.Add(i.ToString, GetType(String))
                Next
                For i As Integer = 1 To 240
                    Dim lst As New List(Of String)
                    For j As Integer = 0 To 14
                        lst.Add("Name" & i.ToString)
                    Next
                    If rand.Next(2, 10) > 2 Then
                        lst(rand.Next(0, lst.Count)) = Nothing
                    End If
                    .Rows.Add(lst(0), lst(1), lst(2), lst(3), lst(4), lst(5), lst(6), lst(7), lst(8), lst(9), lst(10), lst(11), lst(12), lst(13), lst(14))
                Next
            End With
            DataGridView1.DataSource = myTable
            DataGridView1.AutoResizeColumns()
        End Sub
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            For pass As Integer = 0 To 1
                For c As Integer = 0 To myTable.Columns.Count - 1
                    Dim b As Integer = -1
                    For i As Integer = 0 To myTable.Rows.Count - 1
                        If myTable.Rows(i).Item(c).ToString = Nothing Then
                            b += 1
                            For j As Integer = i + 1 To myTable.Rows.Count - 1
                                myTable.Rows(j - 1).Item(c) = myTable.Rows(j).Item(c)
                            Next
                        End If
                    Next
                    For k As Integer = myTable.Rows.Count - b To myTable.Rows.Count - 1
                        myTable.Rows(k).Item(c) = Nothing
                    Next
                Next
            Next
        End Sub
    End Class
    


    Regards Les, Livingston, Scotland

    Sunday, August 7, 2016 4:00 AM
  • no.. in the example I had given...it would like like

    row1: Name1

    row2: Name2

    row3: Name3

    row4: Name6

    row5

    I am just looking for a null or empty string in the name column... if I need to move a name to a different row.. I'll move the data in four other columns with that.

    Sunday, August 7, 2016 10:12 PM
  • Hi

    OK, then that sounds a more straightforward task. Just to get this right, any row with a blank name field is discarded and loses the data in the other columns - is that correct?

    If so, then here is amended code (much simpler and faster code too).

    The only code of interest is the Button Click event - all the other code is just to add dummy test data -you have your own data and so don't need that.

    ' new Form1 with a blank DataGridView
    ' and a Button1
    Option Strict On
    Option Infer Off
    Option Explicit On
    Public Class Form1
        Dim myTable As New DataTable("Table1")
        Dim rand As New Random
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            ' random data for this example
            With myTable
                .Columns.Add("Name", GetType(String))
                For i As Integer = 1 To 14
                    .Columns.Add(i.ToString, GetType(String))
                Next
                For i As Integer = 1 To 240
                    Dim lst As New List(Of String)
                    For j As Integer = 0 To 14
                        lst.Add("Name" & i.ToString)
                    Next
                    If rand.Next(2, 10) > 2 Then
                        lst(rand.Next(0, lst.Count)) = Nothing
                    End If
                    .Rows.Add(lst(0), lst(1), lst(2), lst(3), lst(4), lst(5), lst(6), lst(7), lst(8), lst(9), lst(10), lst(11), lst(12), lst(13), lst(14))
                Next
            End With
            DataGridView1.DataSource = myTable
            DataGridView1.AutoResizeColumns()
        End Sub
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            For i As Integer = myTable.Rows.Count - 1 To 0 Step -1
                If myTable.Rows(i).Item("Name").ToString = Nothing Then
                    myTable.Rows.RemoveAt(i)
                End If
            Next
        End Sub
    End Class


    Regards Les, Livingston, Scotland



    • Edited by leshay Monday, August 8, 2016 12:13 AM
    Sunday, August 7, 2016 11:55 PM
  •           Col1  ....              Col 7   ......

    row1: Name1                 Name1

    row2: Name2                           

    row3: Name3                 Name3

    row4:                            Name4

    row5:Name6                  Name5

    in this example... I would move col1, col2, col3 from row5 to row4.  row5 would not be blank or empty, next step would be to scan Col7 and move rows 3,4,5 up one row each. 

    Looking at Karen's example.. I suppose what I could do.. is to create a temp table with just the 3 columns.... from the original table... then sort the temp table so all no data fields are at the bottom and then loop to put the data back in the original table.

    Monday, August 8, 2016 12:09 AM
  • dug.. just saw your example.. pretty much what you did...

    Monday, August 8, 2016 12:10 AM
  • dug.. just saw your example.. pretty much what you did...

    Hi

    You just saw which example? Please direct your replies to a person or quote the post you are replying to.

    As for your other reply, which seems to suggest that my first example code is actually what you wanted?


    Regards Les, Livingston, Scotland

    Monday, August 8, 2016 12:30 AM
  • Landlord, 

    What kind of table. This looks pretty much a kind of Excel tables as this is in other tables never done. 

    However, it can also that you made a kind of Tic Tac Toe game where and have just created your own kind of table an array of 9 items. 


    Success
    Cor

    Monday, August 8, 2016 8:44 AM