datagrid row counter

[Fangling]

hi, i have a datagrid which looks like:

subject        students
eg1001         182737H
               29837B
               29837C
               19282W
eg1002         192837C
               32810H

so on and so forth.

how can i do a counter and put into a column between subject and students? like:

subject         numberofstudent   students
eg1001             4              182737H
                                  29837B
                                  29837C
                                  192823W
eg1002              2             192837C
                                  32810H

my codes are as follows:

 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 FROM(SEGDATA)ORDER BY ModuleCode ASC, AdminNo ASC"

        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 i

        conn.Close()
        cmd.Dispose()

anyone help me with this?

[M.Waqas Aslam]

if you are getting these records from db then you can just use Count() to get total number of records , here is a sample query from mssql

select name , count(*)
from table1
group by name

Regards

[Fangling]

means like this:

 cmd.CommandText = "SELECT DISTINCT ModuleCode, AdminNo, count(*) FROM(SEGDATA)ORDER BY ModuleCode ASC, AdminNo ASC"

??

it gives me an error:
"You tried to execute a query that does not include the specified expression 'ModuleCode' as part of an aggregate function."

[Fangling]

i only want to count the students until the next modulecode.

subject         numberofstudent   students
eg1001             4              182737H
                                  29837B
                                  29837C
                                  192823W
eg1002              2             192837C
                                  32810H

like for this, counts from 182737H to 192823W, then is 4 students.
this count i want to put in a column and slot the column inbetween subject and students.

[Fangling]

Hi, i got this code:

cmd.CommandText = "SELECT ModuleCode, AdminNo, COUNT(AdminNo) As Numberofstudents FROM (SEGDATA) GROUP BY ModuleCode, AdminNo"

close to what i wanted, but why the count is the same throughout the whole column?

like:

subject         numberofstudent   students
eg1001             4              182737H
                   4              29837B
                   4              29837C
                   4              192823W
eg1002             4              192837C
                   4              32810H

[M.Waqas Aslam]

hmmm , then you could add row manually using datareader . i could provide you a sample code but in c# as its very long time i am untouch with vb.net . method will be same for both languages but yes little difference in syntax .
So if you are ok with it please do mention , i will try to get you out of this .

Regards

[Fangling]

Hi, thanks. i dont mind taking a look at the code.