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!

I cant understand your problem clearly , as per my understanding the table is populated with a empty row.

Its because of the code

dgvmodstud.Rows(i).Cells(0).Value = String.Empty

If dgvmodstud.Rows(i).Cells(0).Value = currentModuleCode Then

in the above code, its already comparing the currentModuleCode which is declared as String.Empty, and the problem is it has not been assigned a value to check.

Try to assign the value for the variable currentModuleCode and then check it out..

Hope this helps you...

Have a happie coding...:-D

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.