David David - 1 year ago 61
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

, which is then passed into the Order Save function, from which the rows are copied into the database and then removed from the

Now, notice the
labelled 'Total Freight'... This is currently iterating over each row of the
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
would work here, as it is only a

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


Code for calculating the total (freightTable is my

If freightTable.Rows.Count = 0 Then
txtTotal.Text = "0.00"
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 Source

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)
            End If
        Next i

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