Hi i have a datatable, dt3 showing:
adminno paperno
111411H 4
111411H 9
182938C 2
192839A 3
111380Y 26
111380Y 36
111380Y 40
182737N 26
182737N 40
i want to populate:
conflictingpaper numberof students adminno
4:9 1 111411H
26:40 2 111380Y
182737N
... .. ....
...
i have these codes:
Dim connect As String
connect = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Application.StartupPath & "\segdata.accdb"
Dim conn As New OleDbConnection(connect)
Dim cmd As OleDbCommand = New OleDbCommand
cmd.Connection = conn
conn.Open()
cmd.CommandText = "SELECT DISTINCT AdminNo, ModuleCode FROM(SEGDATA)ORDER BY AdminNo ASC, ModuleCode ASC"
Dim dt1 As New DataTable
dt1.Load(cmd.ExecuteReader)
'DataGridView1.AutoGenerateColumns = True
'DataGridView1.DataSource = dt1
cmd.CommandText = "SELECT DISTINCT PaperNo,ModuleCode1,ModuleCode2,ModuleCode3, ModuleCode4, ModuleCode5, ModuleCode6, ModuleCode7, ModuleCode8, ModuleCode9 FROM(PapersList)ORDER BY PaperNo ASC"
Dim dt2 As New DataTable
dt2.Load(cmd.ExecuteReader)
'DataGridView2.AutoGenerateColumns = True
'DataGridView2.DataSource = dt2
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
Dim dt As New DataTable
' Create 3 typed columns in the DataTable.
dt.Columns.Add("ConflictingPaper", GetType(String))
dt.Columns.Add("Numberofstudents", GetType(String))
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()
DataGridView3.DataSource = dt
End If
Next
Next
Next
conn.Close()
the linq query is counting the number of papers conflicting than the students.
example:
conflictingpaper numberofstudents adminno
23:46:48 3 111411H
13:24 2 192839A
and it loads very slow to my datagridview. The adminno is also not in the format i want as its only showing 1 adminno per line.
THANKS!