data comparison, vb.net

[Fangling]

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.
How to i achieve the result expected and speed up loading time into datagridview?
anyone help me with the codes?
THANKS!

[ss125]

My suggestions based on your code:

1)All Your code processing is done on the backend iteself, I mean the filtering and joining.Instead of doing that try to populate all the records in the datatables. Then try to compare the necessary data from the datatable. and for joining (the main aspect) try to do it using the below format

ds.Tables[0].Rows[0][0].ToString();

In order to join all necessary data,

=>Use a for loop to execute till the end of the table.

=> Inside for loop use if condition to check for conflict

if(ds.Tables[0].Rows[0][0].ToString())=(ds.Tables[0].Rows[0][0].ToString())

{

//Do the following 
//here u use this on a different datatable***avoid that***

}

Hope this helps you.

Have a happy coding.

[Fangling]

i actually tried this:

dim curadminno as string = string.empty
'retrieve modulecode from datatable1
for each dr1 as datarow in dt1.rows
curadminno = dr1("AdminNo").tostring
'loop through datatable3 to look for matching adminno
for i as integer = 0 to dt3.rows.count -1 
if curadminno = dt3(I).tostring then

'Dont know how to continue

Could you help me out? :(

[ss125]

You can use the same codes depending on your concept.

You are matching the data with another datable.

If that the case, then

try the below sample

String[] admin_no = new String[];
int i=0;
for each dr1 as datarow in dt1.rows
if(curadminno = dr1("AdminNo").tostring)
{
admin_no[i]=curadminno;
}
//end the loop and necessary functionalities

then populate it as
dim dt3 as new datatable;
for each i in admin_no[i]
dt3.row.add(admin_no[i])

If this method suits your criteria,try this...Else notify me...

And Sorry for the late response.

Have a happie coding...

[Fangling]

Hi I think I would loop the datatable dt3 instead of comparing 2 datatables, it might be easier?hmm..
would you mind help me out with the code?
Thanks!

[ss125]

Ya, you can loop the dt3,So that the execution time may be decreased.

And regarding the code,you have to think wisely. try with the modifications. If the problem still exists then message me. I will help you for sure.