Hi makumbi,
Create a procedure and return the grade as per the number by looping the DataSet. Then use the DataSet to display the record with grades.
Please refer below sample.
SQL
CREATE PROCEDURE [MarkSheet_GetMarkSheet]
@Value VARCHAR(20)
AS
BEGIN
DECLARE @Test AS TABLE(r1 INT,r2 INT,r3 INT,r4 INT,r5 INT,r6 INT,r7 INT,r8 INT,r9 INT,r10 INT,r11 INT,r12 INT,r13 INT,r14 INT,r15 INT,r16 INT,r17 INT,r18 INT)
INSERT INTO @Test VALUES(0,39,40,44,45,49,50,54,55,59,60,69,70,79,80,89,90,100)
SELECT CASE
WHEN @Value >= r1 AND @Value <= r2 THEN 'F9'
WHEN @Value >= r3 AND @Value <= r4 THEN 'P8'
WHEN @Value >= r5 AND @Value <= r6 THEN 'P7'
WHEN @Value >= r7 AND @Value <= r8 THEN 'C6'
WHEN @Value >= r9 AND @Value <= r10 THEN 'C5'
WHEN @Value >= r11 AND @Value <= r12 THEN 'C4'
WHEN @Value >= r13 AND @Value <= r14 THEN 'C3'
WHEN @Value >= r15 AND @Value <= r16 THEN 'D2'
WHEN @Value >= r17 AND @Value <= r18 THEN 'D1'
END AS 'Grade'
FROM @Test
END
HTML
<asp:GridView ID="gvDetails" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="Name" HeaderText="Name" />
<asp:BoundField DataField="Class" HeaderText="Class" />
<asp:BoundField DataField="Stream" HeaderText="Stream" />
<asp:BoundField DataField="engscore" HeaderText="engscore" />
<asp:BoundField DataField="enggrade" HeaderText="enggrade" />
<asp:BoundField DataField="sciencescore" HeaderText="sciencescore" />
<asp:BoundField DataField="sciencegrade" HeaderText="sciencegrade" />
<asp:BoundField DataField="sstscore" HeaderText="sstscore" />
<asp:BoundField DataField="sstGrade" HeaderText="sstGrade" />
<asp:BoundField DataField="Mathsscore" HeaderText="Mathsscore" />
<asp:BoundField DataField="Mathsgrade" HeaderText="Mathsgrade" />
</Columns>
</asp:GridView>
Namespace
C#
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
VB.Net
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Code
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[11] {new DataColumn("Name"),
new DataColumn("Class"),
new DataColumn("Stream"),
new DataColumn("engscore", typeof(int)),
new DataColumn("enggrade"),
new DataColumn("sciencescore", typeof(int)),
new DataColumn("sciencegrade"),
new DataColumn("sstscore", typeof(int)),
new DataColumn("sstGrade"),
new DataColumn("Mathsscore", typeof(int)),
new DataColumn("Mathsgrade") });
dt.Rows.Add("WAFOYOYRWOTH SHALOM PIUS", "P6", "Blue", 96, "", 91, "", 81, "", 90, "");
dt.Rows.Add("TCHELU FRIDAH VIRGINIA", "P6", "Blue", 87, "", 92, "", 73, "", 95, "");
dt.Rows.Add("ODONGO ANDREW", "P6", "Blue", 92, "", 95, "", 89, "", 89, "");
dt.Rows.Add("TWINOMUJJUNI BRIAN", "P6", "Blue", 85, "", 89, "", 63, "", 91, "");
dt.Rows.Add("ENACHU DORCUS", "P6", "Blue", 81, "", 84, "", 63, "", 96, "");
dt.Rows.Add("MASABA LETICIA", "P6", "Blue", 89, "", 74, "", 60, "", 72, "");
foreach (DataRow dr in dt.Rows)
{
dr["enggrade"] = GetGrade(dr["engscore"].ToString());
dr["sciencegrade"] = GetGrade(dr["sciencescore"].ToString());
dr["sstGrade"] = GetGrade(dr["sstscore"].ToString());
dr["Mathsgrade"] = GetGrade(dr["Mathsscore"].ToString());
}
gvDetails.DataSource = dt;
gvDetails.DataBind();
}
}
private string GetGrade(string number)
{
string grade = string.Empty;
string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(conString))
{
con.Open();
using (SqlCommand cmd = new SqlCommand("MarkSheet_GetMarkSheet", con))
{
cmd.Parameters.AddWithValue("@Value", number);
cmd.CommandType = CommandType.StoredProcedure;
grade = Convert.ToString(cmd.ExecuteScalar());
con.Close();
}
}
return grade;
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Dim dt As DataTable = New DataTable()
dt.Columns.AddRange(New DataColumn(10) {New DataColumn("Name"),
New DataColumn("Class"),
New DataColumn("Stream"),
New DataColumn("engscore", GetType(Integer)),
New DataColumn("enggrade"),
New DataColumn("sciencescore", GetType(Integer)),
New DataColumn("sciencegrade"),
New DataColumn("sstscore", GetType(Integer)),
New DataColumn("sstGrade"),
New DataColumn("Mathsscore", GetType(Integer)),
New DataColumn("Mathsgrade")})
dt.Rows.Add("WAFOYOYRWOTH SHALOM PIUS", "P6", "Blue", 96, "", 91, "", 81, "", 90, "")
dt.Rows.Add("TCHELU FRIDAH VIRGINIA", "P6", "Blue", 87, "", 92, "", 73, "", 95, "")
dt.Rows.Add("ODONGO ANDREW", "P6", "Blue", 92, "", 95, "", 89, "", 89, "")
dt.Rows.Add("TWINOMUJJUNI BRIAN", "P6", "Blue", 85, "", 89, "", 63, "", 91, "")
dt.Rows.Add("ENACHU DORCUS", "P6", "Blue", 81, "", 84, "", 63, "", 96, "")
dt.Rows.Add("MASABA LETICIA", "P6", "Blue", 89, "", 74, "", 60, "", 72, "")
For Each dr As DataRow In dt.Rows
dr("enggrade") = GetGrade(dr("engscore").ToString())
dr("sciencegrade") = GetGrade(dr("sciencescore").ToString())
dr("sstGrade") = GetGrade(dr("sstscore").ToString())
dr("Mathsgrade") = GetGrade(dr("Mathsscore").ToString())
Next
gvDetails.DataSource = dt
gvDetails.DataBind()
End If
End Sub
Private Function GetGrade(ByVal number As String) As String
Dim grade As String = String.Empty
Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(conString)
con.Open()
Using cmd As SqlCommand = New SqlCommand("MarkSheet_GetMarkSheet", con)
cmd.Parameters.AddWithValue("@Value", number)
cmd.CommandType = CommandType.StoredProcedure
grade = Convert.ToString(cmd.ExecuteScalar())
con.Close()
End Using
End Using
Return grade
End Function
Screenshot
