column does not belong to table

[Fangling]

Hi, i have this query:

cmd.CommandText = "SELECT DISTINCT PaperNo,ModuleCode1,ModuleCode2,ModuleCode3, ModuleCode4, ModuleCode5, ModuleCode6, ModuleCode7, ModuleCode8, ModuleCode9 FROM(PapersList)ORDER BY PaperNo ASC"

and i have this code to loop:

 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 dr2 As DataRow In dt2.Rows

            curmodule = dr2("ModuleCode1").ToString


            For Each dr1 As DataRow In dt1.Rows
                If curmodule = dr1("ModuleCode").ToString Then

                    Dim dt3row As DataRow
                    dt3row = dt3.NewRow
                    dt3row("AdminNo") = dr1("AdminNo")
                    dt3row("PaperNo") = dr2("PaperNo")
                    dt3.Rows.Add(dt3row)
                    Me.DataGridView3.DataSource = dt3
                End If
            Next
        Next

in this line:

  curmodule = dr2("ModuleCode1").ToString

its only checking the first column in my datatable 2 i supposed.
However, i have got 9 Columns of modulecode.

i want to check for 1 modulecode in dt1, whether it is present in any of the columns in each row of dt2.
How can i edit this? Thanks!

my dt1 looks like:

AdminNo    ModuleCode
111411H    EG1001
183728V    EG3920
111411H    EG1919

dt2 which looks like:

paperNo     ModuleCode1    ModuleCode2   ModuleCode3    ModuleCode 4 .....        ModuleCode9
1           EG1001         EG1010
2           EG1919         EG1001        EG1293         EG1032
3           EG3920
4           EG3891

so on and so forth.

[Begginnerdev]

You can check per column basis:

For example:

  Dim Found As Boolean
  For Each r As DataRow In dt.Rows
      Found = False
      For i = 0 To dt.Columns.Count
          If r(i) = "ThisValue" Then
              Found = True
              Exit For
          End If
      Next
      If Found Then Exit For
  Next

Also, I have posted another solution on your other post.

[Fangling]

Thanks so much for your help.

i edited to:

   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 = 0 To dt2.Columns.Count - 1

                    If curmodule = dr1(i) Then
                        found = True
                        Dim dt3row As DataRow
                        dt3row = dt3.NewRow
                        dt3row("AdminNo") = dr1("AdminNo")
                        dt3row("PaperNo") = dr2("PaperNo")
                        dt3.Rows.Add(dt3row)
                        Me.DataGridView3.DataSource = dt3
                    End If
                Next
            Next
        Next

it prompts me "can't find column 2"
Where is the error?

[Fangling]

i got it working. there's an logic error in this line:

 If curmodule = dr1(i) Then

i changed it to:

 If curmodule = dr2(i) Then

and it worked.

Thanks for the solution!