0

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!

1
Contributor
1
Reply
12
Views
4 Years
Discussion Span
Last Post by Fangling
0

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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.