Jarobe Bernardo Jarobe Bernardo - 1 year ago 162
Vb.net Question

Adding row in DataTable

I'm doing a project in vb and I'm trying to add all the rows in dataTable name dt to a new dataTable name dtNew but i dont want duplicate rows to be added in dtNew instead adding the count if its duplicate. someone help me please.

here is a sample dataTable name dt as you can see apple is duplicate

Sample dataTable

here is my code.

Dim dtNew As DataTable = New DataTable '---> I Created new DataTable

'---> Created 3 columns
dtNew.Columns.Add("TYPE", Type.GetType("System.String"))
dtNew.Columns.Add("NAME", Type.GetType("System.String"))
dtNew.Columns.Add("COUNT", Type.GetType("System.Int32"))

For Each dtRow As DataRow In dt.Rows ' ---> loop all the rows in dt DataTable
Dim newRow As DataRow = dtNew.NewRow
newRow("TYPE") = dtRow("TYPE")
newRow("NAME") = dtRow("NAME")
newRow("COUNT") = dtRow("COUNT")

'check if dtNew DataTable has no row
If Not dtNew Is Nothing AndAlso dtNew.Rows.Count = 0 Then
'add new row

' I want to check first all the rows in dtNew DataTable
' if its existed, and if it's not then add new row
For Each dtNewRow As DataRow In dtNew.Rows
If ((dtNewRow("TYPE") = "VEGETABLE" OrElse _
dtNewRow("TYPE") = "FRUIT") And _
dtNewRow("NAME") <> newRow("NAME")) Then

'insert row
dtNew.Rows.InsertAt(newRow, dtNew.Rows.Count)
'error: Collection was modified; enumeration operation might not be executed.

End If
End If

Answer Source
For Each row As DataRow In dt.Rows
    Dim type = CStr(row("Type"))
    Dim name = CStr(row("Name"))
    Dim existingRows = dtNew.Select(String.Format("Type = '{0}' AND Name = '{1}'",

    If existingRows.Length = 0 Then
        'No match so create a new row.
        Dim newRow = dtNew.NewRow()

        newRow("Type") = type
        newRow("Name") = name
        newRow("Count") = row("Count")

        'Match found so update existing row.
        Dim newRow = existingRows(0)

        newRow("Count") = CInt(newRow("Count")) + CInt(row("Count"))
    End If

As the tables have the same schema, you could even simplify the If block to this:


That would only be a problem if row had a RowState other than Unchanged and you didn't want to import that too.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download