looping two datatables

Question

3.00/5 (1 vote)

See more:

i have dt1 from a query

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

another query dt2

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

dt1 looks like:

AdminNo      ModuleCode
111411H      EG1001
111411H      Eg1003
111380Y      EG2011

dt2 looks like:

PaperNo    Module1    Module2   Module3 ....
1          EG1001     
2          EG1003     EG1001
3          EG2011

How do i loop these 2 tables to get:

AdminNo PaperNo
111411H 1
111411H 2
111380Y 3

Anybody help me with the codes, thanks!

Posted 22-Jul-13 17:43pm

12345_abcde

Add a Solution

2 solutions

Solution 2

i edited it to:

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

and got it working! thanks!

Posted 24-Jul-13 19:47pm

12345_abcde

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Winston Madiano · Accepted Answer · 2013-07-22T18:52:00

Try a nested loop

VB

Sub loopAndFind()
       '/*Create datatable(m_DtOutput) for the output */
       Dim m_DtOutput As New DataTable
       m_DtOutput.Columns.Add("AdminNo", GetType(String)) '/*Add column AdminNo
       m_DtOutput.Columns.Add("PaperNo", GetType(Integer)) '/*Add column PaperNo

       Dim m_Module1 As String = "" '/*declare variable to for the module1
       For Each dr2 As DataRow In dt2.Rows '/* Loop on datatable 2 (dt2)
           m_Module1 = dr2("Module1").ToString '/* get Module1 value on the dt2
           For Each dr1 As DataRow In dt1.Rows '/*Loop on the datatable 1 (dt1)
               If m_Module1 = dr1("ModuleCode").ToString Then '/*if module1 on dt2 is equal to ModuleCode on dt1
                   Dim m_DrOutput As DataRow '/*Create a datarow(m_DrOutput) for the output
                   m_DrOutput = m_DtOutput.NewRow '/*Set m_DrOutput as new row for m_DtOutput
                   m_DrOutput("AdminNo") = dr1("AdminNo") '/* Get dt1 AdminNo value
                   m_DrOutput("PaperNo") = dr2("PaperNo") '/*Get dt2 PaperNo value
                   m_DtOutput.Rows.Add(m_DrOutput) '/* Add the new values to m_DtOutput
               End If
           Next '/*Go to the next dt1 row
       Next '/*Go to the next dt1 row

       Me.DataGridView1.DataSource = m_DtOutput '/* m_DtOutput as datagridview1 datasource
   End Sub