confusion over datagrid erasing entries and general datagrid access

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

From stackoverflow

• 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

  MostlyLucid : Thanks for the nudge! - for the code to work properly it does need properties etc... I have posted the complete working code in my previous post for future reference.

• 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

  MostlyLucid : Solved: add another property for the generated columns as well in the classes. remove the code that creates the column It now sorts correctly Noet: the above code is correct - I removed the errors...