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
...                 ..                   ....

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

        cmd.CommandText = "SELECT DISTINCT AdminNo, ModuleCode FROM(SEGDATA)ORDER BY AdminNo ASC, ModuleCode ASC"
        Dim dt1 As New DataTable
        '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
        '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")
                        '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



the linq query is counting the number of papers conflicting than the students.

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.


How to i achieve the result expected and speed up loading time into datagridview?

This article has been dead for over six months. Start a new discussion instead.