Member Avatar for 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?

Edited by Fangling
  • 2 Contributors
  • 6 Replies
  • 275 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Fangling

Dani AI

Generated

Brief summary and two practical fixes. The goal is to show, for each ModuleCode, the total student count once (on the first row for that module) and leave the following rows blank. The count itself must be computed per ModuleCode (not per ModuleCode+AdminNo). Two good approaches:

  • Do it in SQL: produce the per-module counts and join them back to every student row (Access does not have window functions, so use a grouped subquery or JOIN). This returns a NumberOfStudents value for each row; you then format the grid to display that number only on the first row of each module.

  • Do it in code: load the detail rows into a DataTable, compute a dictionary of counts keyed by ModuleCode, add a NumberOfStudents column, fill the count for the first row of each module and set the rest to blank/NULL before binding.

Example SQL (derived-table/join approach for Access):

SELECT s.ModuleCode, s.AdminNo, grp.NumberOfStudents
FROM SEGDATA AS s
INNER JOIN (
  SELECT ModuleCode, COUNT(*) AS NumberOfStudents
  FROM SEGDATA
  GROUP BY ModuleCode
) AS grp ON s.ModuleCode = grp.ModuleCode
ORDER BY s.ModuleCode, s.AdminNo;

Client-side VB.NET pattern (operate on the loaded DataTable, then bind):

' ensure Imports System.Linq and reference to DataSetExtensions
If Not dt.Columns.Contains("NumberOfStudents") Then
  dt.Columns.Add("NumberOfStudents", GetType(Integer))
End If

Dim counts = dt.AsEnumerable() _
               .GroupBy(Function(r) r.Field(Of String)("ModuleCode")) _
               .ToDictionary(Function(g) g.Key, Function(g) g.Count())

Dim lastModule As String = Nothing
For Each row As DataRow In dt.Rows
  Dim m = If(row.IsNull("ModuleCode"), String.Empty, CStr(row("ModuleCode")))
  If m <> lastModule Then
    row("NumberOfStudents") = counts(m)
    lastModule = m
  Else
    row("NumberOfStudents") = DBNull.Value
  End If
Next

dataGridView1.DataSource = dt
dataGridView1.Columns("NumberOfStudents").DisplayIndex = 1

Notes and troubleshooting: keep the rows ordered by ModuleCode so "first occurrence" is contiguous; doing the count in SQL is more efficient for large tables, but the client-side method is easier to tweak for display (blanking, formatting). As suggested, COUNT/GROUP BY is the right idea — the issue you ran into was grouping the wrong columns; group only by ModuleCode (or join a grouped subquery) so you get per-module totals.

Recommended Answers

All 6 Replies

Member Avatar for 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

Member Avatar for 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."

Member Avatar for 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.

Member Avatar for 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
Member Avatar for 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

Member Avatar for Fangling

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

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.