Converting Rows in columns

[Richard Deeming]

A spot of LINQ makes this relatively easy:

Dim data = myDataTable.AsEnumerable().GroupBy(Function (r) New With
{
Key .ID = r.Field(Of Integer)("ID"),
Key .Code = r.Field(Of Integer)("Code")
},
Function (key, rows) New With
{
Key .ID = key.ID,
Key .Code = key.Code,
.Dates = rows.Select(Function (r) r.Field(Of DateTime?)("Effective Date")) _
.OrderByDescending(Function (d) d).Take(3).ToList()
})

Dim result As New DataTable()
result.Columns.Add("ID", GetType(Integer))
result.Columns.Add("Code", GetType(Integer))
result.Columns.Add("Effective Date 1", GetType(DateTime))
result.Columns.Add("Effective Date 2", GetType(DateTime))
result.Columns.Add("Effective Date 3", GetType(DateTime))

For Each item in data
result.Rows.Add(item.ID, item.Code,
item.Dates.ElementAtOrDefault(0),
item.Dates.ElementAtOrDefault(1),
item.Dates.ElementAtOrDefault(2))
Next

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[byka]

I have added Imports System.Linq to my declaration however Getting errors below: 'AsEnumerable' is not a member of 'System.Data.DataTable'. item' is not declared. It may be inaccessible due to its protection level

[Richard Deeming]

Make sure you have a reference to the System.Data.DataSetExtensions assembly, and an Imports System.Data declaration at the top of your file.

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[byka]

I have added all references however still the following lines won't compile. Option Strict On requires all variable declarations to have an 'As' clause.

Dim data = myDataTable.AsEnumerable().GroupBy(Function(r) New With {Key .ID = r.Field(Of Integer)("ID"), Key .Code = r.Field(Of Integer)("Code")},

and 'item' is not declared. It may be inaccessible due to its protection level.

For Each item In data

[Richard Deeming]

You're using an anonymous type, so you can't give the variables an explicit type. Try setting Option Infer On, which should allow the compiler to infer the type for you.

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[byka]

it worked. Now I am facing different issue when adding a new row into new table with non key columns

result.Rows.Add .... item.EENAML.. etc.

Could you tell me what am I doing wrong? Here my full code

Dim data = myDataTable.AsEnumerable().GroupBy(Function(r) New With {Key .EEGRP = r.Field(Of String)("EEGRP"), Key .EESSN = r.Field(Of String)("EID")},
Function(key, rows) New With
{
Key .EEGRP = key.EEGRP,
Key .ESSN = key.EID,
.Dates = rows.Select(Function(r) r.Field(Of DateTime?)("DEIBCY") & ("DIIBYR") & ("D.DIIBMT") & ("DEIBDY")) _
.OrderByDescending(Function(d) d).Take(3).ToList()
})

                 'Create new table with original table schema
                 Dim result As New DataTable
                 result = myDataTable.Clone()
                'Create new  row
                 For Each item In data
                     result.Rows.Add(item.EEGRP,item.EID, item.EENAML,item.EENAMF,item.EENAMM,item.EEADD1,item.EEADD2,,,, \_

[Richard Deeming]

What's the error message?

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[byka]

'FIELD' is not a member of ''; it does not exist in the current context.

[Richard Deeming]

Make sure you have a reference to the System.Data.DataSetExtensions assembly, and an Imports System.Data declaration at the top of your file. The Field(Of T) method is an extension method defined in that namespace and assembly: DataRowExtensions.Field<T> Method (DataRow, String)[^]

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[byka]

When I add

Imports System.Data.DataSetExtensions
Module ModuleMain...

it saying it can't be found. I do have reference added to the project.

[Richard Deeming]

The assembly is System.Data.DataSetExtensions; the namespace is System.Data.

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[byka]

When I change my project properties to .net 3.5 I was able to add Imports System.Data.DataSetExtensions however even with that I still having issues with no key fields for selecting is not a member of ''; it does not exist in the current context

For Each item In data
result.Rows.Add(item.EEGRP,item.ESSN, item.EENAML,item.EENAMF,item.EENAMM,item.EEADD1,item.EEADD2,...._