Hi, i have these codes:
Dim dt3 As New DataTable
dt3.Columns.Add("AdminNo", GetType(String)) '/*Add column AdminNo
dt3.Columns.Add("PaperNo", GetType(Integer))
Dim curmodule As String = String.Empty
For Each dr1 As DataRow In dt1.Rows
curmodule = dr1("ModuleCode").ToString
For Each dr2 As DataRow In dt2.Rows
Dim found As Boolean
found = False
For i As Integer = 0 To dt2.Columns.Count - 1
If curmodule = dr2(i).ToString Then
found = True
Dim dr3 As DataRow
dr3 = dt3.NewRow
dr3("AdminNo") = dr1("AdminNo")
dr3("PaperNo") = dr2("PaperNo")
dt3.Rows.Add(dr3)
'DataGridView3.AutoGenerateColumns = True
'Me.DataGridView3.DataSource = dt3
End If
Next
Next
Next
Dim dt As New DataTable
'' Create 3 typed columns in the DataTable.
dt.Columns.Add("ConflictingPaper", GetType(String))
dt.Columns.Add("Numberofstudents", GetType(Integer))
dt.Columns.Add("AdminNo", GetType(String))
'Dim query1 = (From a In dt3 Group Convert.ToString(a.Field(Of Integer)("PaperNo")) By AdminNo = (a.Field(Of String)("AdminNo")) Into Group Select dt.LoadDataRow(New Object() {String.Join(":", Group.ToArray()), Group.Count(), AdminNo}, False)).ToList().Count()
Dim query = From r In dt3.AsEnumerable() Let adminno = r.Field(Of String)("AdminNo") Group r By adminno Into Group Select New With {.AdminNo = adminno, .numberofstudents = Group.Count(), .conflictingpaper = String.Join(":", Group.Select(Function(r) r.Field(Of Integer)("paperno").ToString()).ToArray())}
'Dim item As string
For Each Item In query
dt.LoadDataRow(New Object() {Item.conflictingpaper, Item.numberofstudents, Item.AdminNo}, True)
Next
DataGridView3.DataSource = dt
DataGridView3.AutoGenerateColumns = True
datatable, dt3 have got data :
paperno adminno
1 111411H
1 111380Y
2 182739S
3 111380Y
3 111380Y
3 111411H
i want to get the outcome:
Collapse | Copy Code
conflictingpaper numberofstudents adminno
1:3 2 111411H
blank blank 111380Y
29:32:3 3 ...
blank blank ...
blank blank ...
the linq query i did is populating:
1:3 2 127837Y
32:53 5 103928A
numberofstudents column is wrong, it seems to be counting the number of papers conflicted. and is not displaying all the students occurred in the conflicting paper in the format i wanted.
anyone please help me out with the code? Thanks!
