David David - 3 months ago 17
SQL Question

Selecting distinct DataTable rows

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

enter image description here

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

Answer

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.)