looping datatable

[Fangling]

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:

AdminNo      ModuleCode
111411H      EG1001
111411H      Eg1003
111380Y      EG2011

dt2:

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!

[Begginnerdev]

It would be easier to do a SELECT with an inner join.

Something like:

"SELECT SEGDATA.AdminNo, Paperslist.PaperNo InnerJoin SEGDATA on PapersList.Module1 = SEGDATA.ModuleCode"

But my SQL is a little lacking

I hope this points you in the right direction.

[Fangling]

Hi, thanks for that but it has errors:
Syntax error (missing operator) in query expression 'Paperslist.PaperNo InnerJoin SEGDATA on PapersList.Module1 = SEGDATA.ModuleCode'.

and i have 9 column of modulecode to check.

How do i do it?

how do i change this to check 9 columns instead of 1 column in dt2?

 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)

Thanks!

[Begginnerdev]

The correct syntax would be:

"SELECT SEGDATA.AdminNo, PapersList.PaperNo FROM SEGDATA,PapersList WHERE SEGDATA.ModuleCode = PapersList.Module1"

This will return all of the records that match.

This will be in the format you want.

You can also add the DISTINCT clause for filtering duplicates.

As for the PapersList Table, you could try using one ModuleCode column with MAX length.

Then you could have more than 9 codes per paper number.

For example

|ModuleCodes|
|EG1003;EG1001;EG1201;EG1092|

Then you could parse the field for the value by splitting on ";" or just tell SQL to get the value with the LIKE Clause.

[Fangling]

But my data paperslist is given in this format:

ModuleCode1   ModuleCOde2 ...
EG0192        EG2983

1 in each column. is there any way i can select multiple columns?