Display Group wise Rank in Windows Form DataGridView using C# and VB.Net

Last Reply 13 days ago By dharmendr

Posted 13 days ago

I have the following Data

SessionID TermID SetExamID ClassName SectionName SubjectID AdmissionNo Max Mark
8 6 18 Ten Yellow 123 R-000401 50 24
8 6 18 Ten Yellow 123 R-000402 50 23
8 6 18 Ten Yellow 123 R-000403 50 32
8 6 18 Ten Yellow 123 R-000404 50 23
8 6 18 Ten Yellow 123 R-000405 50 45
8 6 18 Ten Yellow 123 R-000406 50 33
8 6 18 Ten Yellow 123 R-000407 50 32
                        SELECT * FROM (
                            SELECT AdmissionNo,sum(Max) 'Total',sum(Mark) 'Obtain'
                            ,dense_rank() over(partition BY SUM(Max) order by SUM(Mark) DESC) AS RANK
                            ,Round(sum(Mark) * 100/ sum(Max),1) as Percentage
                            ,(CASE WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'A+'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'A'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'B'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'C'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'D'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'E'
                            ELSE 'FAIL' END ) AS Grade
                            ,(CASE WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'OutStanding'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'Excellent'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'Very Good'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'Good'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'Satisfactory'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'Work Hard'
                            ELSE 'FAIL' END ) AS Remarks
                            ,(CASE WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'OutStanding'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'His/Her performance has been outstanding.Keep it up.'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'Keep up the good work.All the best.'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'He/She has the potential to do more.All the best.'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'He/She has been doing good in studies.'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'He/She needs to work hard in order to improve grades.'
                            ELSE 'He/She is advised to pay attention towards studies.' END ) AS TRemarks
                            FROM tblSetMarks
                            GROUP BY AdmissionNo ) t                   
                        order by AdmissionNo asc;

it is showing me result as like that

https://imgur.com/S80rB9j

i have total 20 students in a class ten and section yellow and 21 students in a class ten and section orange.

i want to display the record section wise hence i filter the record of Class Ten and Section Yellow.

but Y here i am seeing wrong RANK which is greater than 20. as there are 20 student in this section then RANK must not exceede the greater than 20.

how to fix it?

Posted 13 days ago Modified on 12 days ago

Hi smile,

Check this example. Now please take its reference and correct your code.

C#

private void btnSearch_Click(object sender, EventArgs e)
{
    string conString = "Server=.;DataBase=Test;UID=sa;PWD=123";
    string query = @"SELECT *
                    FROM (
	                    SELECT AdmissionNo,sum(Max) 'Total',sum(Mark) 'Obtain'
	                    ,dense_rank() over(partition BY SUM(Max) order by SUM(Mark) DESC) AS RANK
	                    ,Round(sum(Mark) * 100/ sum(Max),1) as Percentage
	                    ,(CASE WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'A+'
	                    WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'A'
	                    WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'B'
	                    WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'C'
	                    WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'D'
	                    WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'E'
	                    ELSE 'FAIL' END ) AS Grade
	                    ,(CASE WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'OutStanding'
	                    WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'Excellent'
	                    WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'Very Good'
	                    WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'Good'
	                    WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'Satisfactory'
	                    WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'Work Hard'
	                    ELSE 'FAIL' END ) AS Remarks
	                    ,(CASE WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'OutStanding'
	                    WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'His/Her performance has been outstanding.Keep it up.'
	                    WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'Keep up the good work.All the best.'
	                    WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'He/She has the potential to do more.All the best.'
	                    WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'He/She has been doing good in studies.'
	                    WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'He/She needs to work hard in order to improve grades.'
	                    ELSE 'He/She is advised to pay attention towards studies.' END ) AS TRemarks
	                    FROM tblSetMarks
	                    WHERE SectionName = @Section AND ClassName = @Class
	                    GROUP BY AdmissionNo ) t
                    order by AdmissionNo asc";
    DataTable dt = new DataTable();
    using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(conString))
    {
        using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query))
        {
            cmd.Parameters.AddWithValue("@Section", txtSectionName.Text);
            cmd.Parameters.AddWithValue("@Class", txtClassName.Text);
            using (System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                sda.Fill(dt);
            }
        }
    }
    dataGridView1.DataSource = dt;
}

VB.Net

Private Sub btnSearch_Click(sender As System.Object, e As System.EventArgs) Handles btnSearch.Click
    Dim conString As String .;DataBase=Test;UID=sa;PWD=123"
    Dim query As String = "SELECT * FROM (" _
                            + "SELECT AdmissionNo,sum(Max) 'Total',sum(Mark) 'Obtain'" _
                            + ",dense_rank() over(partition BY SUM(Max) order by SUM(Mark) DESC) AS RANK" _
                            + ",Round(sum(Mark) * 100/ sum(Max),1) as Percentage" _
                            + ",(CASE WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'A+'" _
                            + "WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'A'" _
                            + "WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'B'" _
                            + "WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'C'" _
                            + "WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'D'" _
                            + "WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'E'" _
                            + "ELSE 'FAIL' END ) AS Grade" _
                            + ",(CASE WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'OutStanding'" _
                            + "WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'Excellent'" _
                            + "WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'Very Good'" _
                            + "WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'Good'" _
                            + "WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'Satisfactory'" _
                            + "WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'Work Hard'" _
                            + "ELSE 'FAIL' END ) AS Remarks" _
                            + ",(CASE WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'OutStanding'" _
                            + "WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'His/Her performance has been outstanding.Keep it up.'" _
                            + "WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'Keep up the good work.All the best.'" _
                            + "WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'He/She has the potential to do more.All the best.'" _
                            + "WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'He/She has been doing good in studies.'" _
                            + "WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'He/She needs to work hard in order to improve grades.'" _
                            + "ELSE 'He/She is advised to pay attention towards studies.' END ) AS TRemarks" _
                            + " FROM tblSetMarks" _
                            + " WHERE SectionName = @Section AND ClassName = @Class" _
                            + " GROUP BY AdmissionNo ) t" _
                            + " order by AdmissionNo asc"
    Dim dt As DataTable = New DataTable()
    Using con As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(conString)
        Using cmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(query)
            cmd.Parameters.AddWithValue("@Section", txtSectionName.Text)
            cmd.Parameters.AddWithValue("@Class", txtClassName.Text)
            Using sda As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter()
                cmd.Connection = con
                sda.SelectCommand = cmd
                sda.Fill(dt)
            End Using
        End Using
    End Using
    dataGridView1.DataSource = dt
End Sub

Screenshot