Hi i have a datatable which looks like:
Modulecode AdminNo
EG1001 111411H
111380Y
192029B
EG1002 110970R
102938X
...
populated in a dgv.
How to i get:
Modulecode NumofStudents AdminNo
EG1001 3 111411H
111380Y
192029B
<<<<<<<<<<Blank>>>>>>>>>>>>>>>>>>>>>>
EG1002 2 110970R
102938X
<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>
.
.
.
My codes are:
''Connections
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 ModuleCode, AdminNo, count() FROM(SEGDATA)ORDER BY ModuleCode ASC, AdminNo ASC"
cmd.CommandText = "SELECT ModuleCode, COUNT(AdminNo) as numberofstudents,AdminNo FROM (SEGDATA) GROUP BY ModuleCode, AdminNo"
Dim dt As New DataTable
dt.Load(cmd.ExecuteReader)
With dgvmodstud
.AutoGenerateColumns = True
.DataSource = dt
End With
Dim currentModuleCode As String = String.Empty
For i = 0 To dgvmodstud.Rows.Count - 1
If dgvmodstud.Rows(i).Cells(0).Value = currentModuleCode Then
dgvmodstud.Rows(i).Cells(0).Value = String.Empty
Else
currentModuleCode = dgvmodstud.Rows(i).Cells(0).Value
End If
Next
However, the number of students doesnt work.
it has the same number throughout the whole column ):
someone help me out with the codes thanks!