Selecting distinct DataTable rows

Question

In my vb.net project, I have this form, which is used for adding additional charges onto orders (delivery costs, for example).

In this example, the freight number is the same, because the freight is for the full order, rather than having freight for each line. However, there is an option to add a different freight value to each individual order line, so the numbers would be different (eg, 7 and 8), and so would the value for each line.

When the Add button is pressed, either the line or the order (depending on how freight is being added), is added to the grid labelled 'Freight'. But, the data is only stored in a DataTable, which is then passed into the Order Save function, from which the rows are copied into the database and then removed from the DataTable.

Now, notice the textbox labelled 'Total Freight'... This is currently iterating over each row of the DataTable and adding all of the totals together. This is fine for if the freights are all different, however, if it is added by order, and there are 2 lines, as above, the value should be 15, not 30, which is currently shown.

So, is there a way that I can only get it to iterate over rows with different Freight # values, so that it doesn't add the same row value twice? I don't think a SQL SELECT DISTINCT would work here, as it is only a DataTable?

This was hard to explain, but hopefully this makes some sense, but sorry if it doesn't!

EDIT

Code for calculating the total (freightTable is my DataTable)

If freightTable.Rows.Count = 0 Then
            txtTotal.Text = "0.00"
        Else
            Dim tot As Double = 0
            Dim i As Integer = 0
            For i = 0 To freightTable.Rows.Count - 1
                tot = tot + Convert.ToDouble(ugFreight.Rows(i).Cells("Freight_Val").Value)
            Next i
            txtTotal.Text = tot
        End If

You can create a generic method like this: [Get distinct values from a column of DataTable](http://stackoverflow.com/a/38627561/3110834) — Reza Aghaei, Sep 02 '16 at 12:44
The linked post is for .NET 2.0 so I didn't use linq. In fact using linq, it can be just 1 line of code. — Reza Aghaei, Sep 02 '16 at 12:54
Also to see a better way for calculating Sum of a column's values using `DataTable` take a look at this: [Show total Sum of values of a Column of a DataTable](http://stackoverflow.com/a/38416170/3110834) — Reza Aghaei, Sep 02 '16 at 13:20

score 1 · Accepted Answer · answered Sep 02 '16 at 11:18

Keep a note of which freight values you have seem in a list.

        Dim freightDone As New List(Of Integer)
        For i = 0 To freightTable.Rows.Count - 1
            Dim currentFreigth as Integer = Convert.ToInteger( ugFreight.Rows(i).Cells("Freight_#").Value )
            If Not freightDone.Contains(currentFreigth) Then
                tot = tot + Convert.ToDouble(ugFreight.Rows(i).Cells("Freight_Val").Value)
                freightDone.Add(currentFreigth)
            End If
        Next i

(You may have to change the definition of currentFreigth to fit your datatable column name, or my slightly rusty VB.)

Reza Aghaei · Answer 2 · 2016-09-02T13:01:35.833

1

You can select distinct values from a column using linq this way:

Dim Values = table.AsEnumerable().Select(Function(x) x.Field(Of int)("SomeColumn")) _
                  .Distinct().ToList()

edited Sep 02 '16 at 13:01

answered Sep 02 '16 at 12:57

Reza Aghaei

120,393
18
203
398

Yes I know, keep that answer accepted :) But don't you like to learn some other/better option? – Reza Aghaei Sep 02 '16 at 13:01
Had you ever seen `AsEnumerable()`? or `Field(of T)("ColumnName")`? – Reza Aghaei Sep 02 '16 at 13:08
1

@David a checkmark just means that answer worked for the OP, nothing else. SO is littered with QA posts where the accepted answer has the least upvotes (and in fact is not the most optimal solution). It is also never too late to add a good/better answer - I have one answer that was posted 3+ years after the Q was posted. – Ňɏssa Pøngjǣrdenlarp Sep 02 '16 at 14:22

score 0 · Answer 3 · edited Sep 02 '16 at 10:42

0

To the where clause of the query add a condition:

where freight IN (select distinct freight from 'table_name')

edited Sep 02 '16 at 10:42

jarlh

42,561
8
45
63

answered Sep 02 '16 at 10:40

Akhil Job

419
6
18

Hi, added my current code above. As you can see there is no query. Remember it uses a **DataTable**, so not sure SQL is possible? – David Sep 02 '16 at 10:51

Selecting distinct DataTable rows

3 Answers3