confusion over datagrid erasing entries and general datagrid access

Question

Hello,

I have a datagrid which I am using Linq to fill, I then add some custom columns and fill them in programmaticly - if the users clicks on the column header to re-sort, all the data in the added columns disapears. I am obviously missing something very basic here but can't seem to see the forest for the trees.

I also have a couple of other questions about how I am doing things:

1. in my code I am accessing the datagrid custom cells by name, but the cells from the linq I have to use a cell reference number (ie: (1,2) instead of (total,2) (I realize that the name is replaced by a int) - can I name the columns? How about if the end user re-orders them?

This is one of the first times I have used a datagrid like this so any pointers would be nice.

Linq code to pull data

  Dim query = From m In db.details _
                Where m.InboundDate >= CType(MonthCalendar1.SelectionStart, DateTime) _
                And m.InboundDate <= CType(MonthCalendar1.SelectionEnd, DateTime).AddHours(23).AddMinutes(59) _
                And m.ClientNo = 1 _
                  Join md In db.Manifests On md.ManifestID Equals m.MainID _
                Select m.Zone, m.MainID, m.InboundDate, m.Zip, md.LadingPkgQty, m.Weight

code to fill with data and add columns

billingDatagrid.DataSource = query

    billingDatagrid.Columns.Add("Package Rate", "Package Rate")
    billingDatagrid.Columns.Add("LTL", "LTL Rate")
    billingDatagrid.Columns.Add("Freight", "Freight")
    billingDatagrid.Columns.Add("Fuel Surcharge", "Fuel Surcharge")
    billingDatagrid.Columns.Add("Linehaul", "Linehaul")
    billingDatagrid.Columns.Add("Billed Amount", "Billed")

Code example of how I am accessing the datagrid columns:

Select Case currentZone
            Case 1
                packageRate = Val(billingDatagrid(4, currentrow).Value) * zone1PkgRate
                billingDatagrid("Package Rate", currentrow).Value = packageRate

                If Val(billingDatagrid(5, currentrow).Value) > 500 Then
                    LTLCharge = zone1_ltlBase + (Val(billingDatagrid(5, currentrow).Value) - 500) * zone1_ltlOver
                Else
                    LTLCharge = zone1_ltlBase
                End If

                billingDatagrid("LTL", currentrow).Value = LTLCharge

At the end of all this I am going to have to create a .csv file for export - it is obviously important that the correst data stay with each row!

Thanks in advance for advice,

--Joe

Answer 1

You could create a class that has all the columns that you want in the grid and in the linq query select statement create a new instance of the class for each row.

Example:

Public Class GridData
    Public Zone as (type here)
    ...all your other columns from the query
    Public PackageRate as (type here)
    ...all your other columns you add programatically
End Class

Then in the query you can do:

Select New GridData with {.Zone=m.Zone, .MainID=m.MainID, .InboundDate=m.InboundDate, .Zip=m.Zip, .LadingPkgQty=md.LadingPkgQty, .Weight=m.Weight, .PackageRate=Nothing, etc }

With that you just assign the query to the datasource like you are doing and all columns should show up and still be there upon resort. Hope it helps. I think that could solve both your problems since the column names should also be all set, but i didn't test that part.

Edit: You can assign the value for PackageRate in a loop even before you assign it to the grid datasource.

For each row in query
   'you can read and write any of the properties of the GridData Class as row.Client, etc here
Next

Then assign it to the datagrid

Answer 2

OK - This is the final answer - the code work and the data stays where it should --

Thanks for the help!

My Class:

Public Class GridData

Private _clientno
Private _manifest
Private _packagerate

Public Property PackageRate() As Double
    Get
        Return _packagerate
    End Get
    Set(ByVal value As Double)
        _packagerate = value
    End Set
End Property
Public Property manifest() As String
    Get
        Return _manifest
    End Get
    Set(ByVal value As String)
        _manifest = value
    End Set
End Property
Public Property client() As Int16
    Get
        Return _clientno
    End Get
    Set(ByVal value As Int16)
        _clientno = value
    End Set
End Property

End Class

The linq:

     Dim query = From m In db.details _
            Where m.InboundDate >= CType(MonthCalendar1.SelectionStart, DateTime) _
            And m.InboundDate <= CType(MonthCalendar1.SelectionEnd, DateTime).AddHours(23).AddMinutes(59) _
            And m.ClientNo = 1 _
              Join md In db.Manifests On md.ManifestID Equals m.MainID _
            Select New GridData With {.PackageRate = Nothing, .manifest = m.MainID, .client = m.ClientNo}

   'No No -- billingDatagrid.Columns.Add("PackageRate", "PackageRate")

and a little code to fill in the packagerate with the value from the client number:

   billingDatagrid.DataSource = query
    Dim currentrow As Int16 = 0
    For Each r In billingDatagrid.Rows
        billingDatagrid("PackageRate", currentrow).Value = billingDatagrid("client", currentrow).Value
        currentrow += 1
    Next

The above code now works...

Thanks,

Joe