remove duplicates from a data table

[pmorones]

Hello. I am having trouble combining records from a datatable. I combined two tables into one virtual table called dt. There are four columns called itemID, description,Inventory.quantity and Orders.quantity. What I want to do is search through the datatable and if there are duplicate itemID's I want to keep a running total of the Orders.quantity. When finished I want to display in a list box, each itemID, its description with its total Orders.quantity.

Here is what I have but cannot get working right.

For i = 0 To dt.Rows.Count - 1

If i = 0 Then
lstResult.Items.Add("&quot

ElseIf dt.Rows(i)(3) = dt.Rows(i - 1)(3) Then

Q = dt.Rows(0)(3)
Q = Q + dt.Rows(i)(3)


End If


lstResult.Items.Add(String.Format(fmtStr,dt.Rows(i)(0),dt.Rows(i)(1),dt.Rows(i)(2),dt.Rows(i)(3),Q))

Next

Any help would be greatly appreciated.

 

[jel]

Wild guess, because you don't explain what kind of thing your combined table is (ADO recordset? what sql?) but anyway: your code would only work if you sort on field no 3.

 

[penney]

'Hopefully this solves your problem

'I dont know which col your item resides so I used
'a constant called ItemCol which you can set.
If dt.Rows.Count > 0 Then
ItemId = dt.Rows(0)(ItemCol)
Q = 0
For i = 1 To dt.Rows.Count
If ItemId = dt.Rows(i-1)(ItemCol) Then
Q = Q + dt.Rows(i-1)(3)
LR = i - 1
Else
lstResult.Items.Add(String.Format(fmtStr,dt.Rows(LR)(0),dt.Rows(LR)(1),dt.Rows(LR)(2),dt.Rows(LR)(3),Q))
Q = dt.Rows(i-1)(3)
ItemId = dt.Rows(i)(ItemCol)
End if

Next
Else
lstResult.Items.Add("&quot
End if

 

[penney]

oops change the second ItemId = dt.Rows(i)(ItemCol) to
ItemId = dt.Rows(i - 1)(ItemCol)

 

[pmorones]

penney, thanks for the response. I am still having trouble. The cide that you gave me was inserted into my program but I am still getting duplicates on the itemID. ItemID is column 0 which I plugged in but still nothing.