循环两个数据表 [英] looping two datatables

问题描述

我从查询中得到了dt1

 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 

另一个查询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如下：

 AdminNo ModuleCode 
 111411H EG1001 
 111411H Eg1003 
 111380Y EG2011 

dt2看起来像：

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

我如何循环这两张表来获得：



AdminNo PaperNo

111411H 1

111411H 2

111380Y 3





任何人都帮我编码，谢谢！

解决方案

尝试嵌套循环

  Sub  loopAndFind（）
 '  / *为输出创建数据表（m_DtOutput）* /  
  Dim  m_DtOutput 作为 新 DataTable 
 m_DtOutput.Columns.Add（   AdminNo， GetType （ String ））'  / *添加列AdminNo  
 m_DtOutput.Columns.Add（  PaperNo， GetType （整数）） '  / *添加列PaperNo  
 
  Dim  m_Module1 作为 字符串 =   '  / *为module1声明变量 
 对于 每个 dr2 作为 DataRow 在 dt2.Rows '  / *数据表2上的循环（dt2） 
 m_Module1 = dr2（  Module1）。ToString '  / *获取dt2上的Module1值 
 对于 每个 dr1  As  DataRow 在 dt1.Rows '  / *循环上数据表1（dt1） 
 如果 m_Module1 = dr1（  ModuleCode）。ToString 然后 '  / *如果dt2上的module1等于dt1上的ModuleCode  
  Dim  m_DrOutput 作为 D. ataRow '  / *为输出创建数据行（m_DrOutput） 
 m_DrOutput = m_DtOutput.NewRow '  / *将m_DrOutput设置为m_DtOutput的新行 
 m_DrOutput（  AdminNo）= dr1（  AdminNo）'  / *获取dt1 AdminNo值 
 m_DrOutput（  PaperNo）= dr2（  PaperNo）'  / *获取dt2 PaperNo值 
 m_DtOutput.Rows.Add（m_DrOutput）'  / *添加新v等于m_DtOutput  
 结束 如果 
 下一步 '  / *转到下一个dt1行 
 下一步 '  / *转到下一个dt1行 
 
 我 .DataGridView1.DataSource = m_DtOutput '  / * m_DtOutput as datagridview1 datasource  
 结束  Sub  
 

我将其编辑为：

 Dim dt3 As New DataTable 
 dt3.Columns.Add（AdminNo，GetType（String））'/ *添加列AdminNo 
 dt3.Columns.Add（PaperNo，GetType（整数））
 
 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 
 
如果curmodule = dr2（i）.ToString那么
 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 
 
结束如果
下一个
下一个
下一步

并让它运转起来！谢谢！

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!

解决方案

Try a nested loop

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

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!

这篇关于循环两个数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！