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) 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) 'store retrieved modulecode in empty string Dim curmodulecode As String = String.Empty DataGridView1.AutoGenerateColumns = True DataGridView1.DataSource = dt1 DataGridView2.AutoGenerateColumns = True DataGridView2.DataSource = dt2 'loop first query For i As Integer = 0 To dt1.Rows.Count - 1 Dim j As Integer 'check if there is modulecode exist, if not, add in If DataGridView1.Rows(i).Cells(j).Value Is Nothing Then DataGridView1.Rows(i).Cells(j).Value = curmodulecode Else i = i + 1 End If 'Dim x, y As Integer 'loop through query 2 Dim MCs As String = String.Empty For z As Integer = 0 To dt2.Rows.Count - 1 Dim y As Integer 'check if curmodule is found in each row If DataGridView2.Rows(z).Cells(y).Value Is Nothing Then DataGridView2.Rows(z).Cells(y).Value = MCs End If ' store PaperNo, Modulecodes, Students in list(of T) Dim mylist As New List(Of Object) mylist.Add(DataGridView2.Rows(z).Cells(0).Value) mylist.Add(DataGridView1.Rows(i).Cells(0).Value) mylist.Add(DataGridView1.Rows(i).Cells(1).Value)
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)