Hi i have a datatable which looks like:

Modulecode      AdminNo
EG1001          111411H
EG1002          110970R

populated in a dgv.

How to i get:

Modulecode NumofStudents AdminNo
EG1001        3          111411H
EG1002         2          110970R

My codes are:

       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

       '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

       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
               currentModuleCode = dgvmodstud.Rows(i).Cells(0).Value
           End If


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 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.