.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Velemoochi
Steve Jackman
Imran Ghani
Santhakumar Munuswamy

Home >> Forum >> VB.Net >> Post New QuestionBookmark and Share Subscribe to Forum

rank calculation for marksheet

Posted By: vidhya     Posted Date: November 03, 2010    Points:2   Category :VB.Net
i preparing a student marklist in vb.net,how to calculate rank (total,res(pass))wise,plz anybody send rank calculation coding



Responses
Author: Asha.MP             
Posted Date: November 03, 2010     Points: 5   

Hi..

Are you saving data in database or you need to retrive from database?
if you r retriving data from database table, please send the table structure,

Thanks and Regards,
Asha.mp
Author: vidhya             
Posted Date: November 03, 2010     Points: 5   

stuname nvarchar,
class nvarchar,
subject1 nvarchar,
subject2 nvarchar,
subject3 nvarchar,
subject4 nvarchar,
subject5 nvarchar ,
total nvarchar,
result nvarchar,
rank nvarchar,

Author: Suresh             
Posted Date: November 03, 2010     Points: 5   

hello Vidhya,
Kindly try the below example, will u get some idea. Let me know that, do u need any further clarification.


declare @t table
(

stuname nvarchar(100),
class nvarchar(100),
subject1 nvarchar(100),
subject2 nvarchar(100),
subject3 nvarchar(100),
subject4 nvarchar(100),
subject5 nvarchar(100),
total nvarchar(100),
result nvarchar(100),
rank nvarchar(100)
)

Insert into @t
Select 'Vidhya','MCA','50','50','50','50','50',null,null,null Union all
Select 'Devi','MCA','50','25','75','50','50',null,null,null Union all
Select 'Geethu','MCA','50','25','75','10','60',null,null,null Union all
Select 'Saranya','MCA','60','90','75','50','50',null,null,null


;with Total
AS
(
Select Stuname,Class,subject1,subject2,subject3,subject4,subject5,
(isnull(Cast(subject1 as int),0)+isnull(subject2,0)+isnull(subject3,0)+isnull(subject4,0)+isnull(subject5,0)) Totals
from @t
),Rank as
(
Select Stuname,Class,subject1,subject2,subject3,
subject4,subject5,Totals,
case when Totals >=250 and Totals <=300 then 'Grade A' else 'Grade B' end Result,
row_number() over(order by Totals desc ) Ranks from Total
)
select * from rank


Author: krishana singh             
Posted Date: December 07, 2010     Points: 5   

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using Key;
using HelperClass;


public partial class admin_Examination_ReportListPreparation : System.Web.UI.Page
{
static DataTable dt = new DataTable();
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
PageBase.FillDropDownList(ddlExam, "select * from Exam_ExamMaster where sessionid='"+Session["session"].ToString()+"'", "Examid", "ExamName");
PageBase.FillDropDownList(ddlClass, "select classname, classid from tbl_class where isdelete='False' and sessionid=" + Session["session"].ToString() + "", "classid", "classname");
//PageBase.FillDropDownList(ddlSession, "select sessionname, sessionid from SessionMaster where isdelete='False'", "sessionid", "sessionname");
}
}
protected void ddlSession_SelectedIndexChanged(object sender, EventArgs e)
{
//PageBase.FillDropDownList(ddlClass, "select classname, classid from tbl_class where isdelete='False' and sessionid="+ddlSession.SelectedValue+"", "classid", "classname");
}
protected void ddlClass_SelectedIndexChanged(object sender, EventArgs e)
{
PageBase.FillDropDownList(ddlSection, "select sectionname, sectionid from tbl_Section where isdelete='False' and classid=" + ddlClass.SelectedValue + "", "sectionid", "sectionname");
}

protected void btnProceed_Click(object sender, EventArgs e)
{

try
{
lblClass.Text = ddlClass.SelectedItem.Text;
lblSection.Text = ddlSection.SelectedItem.Text;
lblexam.Text = ddlExam.SelectedItem.Text;
//lblsession.Text = ddlSession.SelectedItem.Text;

DataSet dstdsession = SqlHelper.ExecuteDataset(Addsn.Conn(), CommandType.Text, "select * from sessionmaster where sessionid='" + Session["session"].ToString() + "'");
if(dstdsession.Tables[0].Rows.Count!=0)
{
lblsession.Text = dstdsession.Tables[0].Rows[0]["sessionname"].ToString();
}

for (int i = 0; i < dt.Columns.Count; i++)
{
dt.Columns.RemoveAt(i);
i--;
}
dt.Clear();



DataSet dstStudents = new DataSet();
// dstStudents = SqlHelper.ExecuteDataset(Addsn.Conn(), CommandType.Text, "select sregno, sfirstname+' '+smiddlename+' '+slastname as studentname from studentdetails where sSession=" + ddlSession.SelectedValue + " and ClassID=" + ddlClass.SelectedValue + " and SectionID=" + ddlSection.SelectedValue + " and isdelete='false' and isapproved='True'");
dstStudents = SqlHelper.ExecuteDataset(Addsn.Conn(), CommandType.Text, "select RegistrationNo, StudentName from S_StudAdmission where SessionId=" + Session["session"].ToString() + " and ClassID=" + ddlClass.SelectedValue + " and SectionID=" + ddlSection.SelectedValue + " and isdelete='false' ");


DataSet dstSubjects = new DataSet();
//dstSubjects = SqlHelper.ExecuteDataset(Addsn.Conn(), CommandType.Text, "select subjectid,subjectname from tbl_SectionSubject where sessionid="+ddlSession.SelectedValue+" and classid="+ddlClass.SelectedValue+" and sectionid="+ddlSection.SelectedValue+"");
dstSubjects = SqlHelper.ExecuteDataset(Addsn.Conn(), CommandType.Text, "select subjectid,subjectname from tbl_SectionSubject where classid=" + ddlClass.SelectedValue + " and sectionid=" + ddlSection.SelectedValue + "");
//Start Add Subject Columns in dstStudent DataSet
for (int i = 0; i < dstSubjects.Tables[0].Rows.Count; i++)
{

dstStudents = admin_Examination_ReportListPreparation.AddColumns(dstStudents, dstSubjects.Tables[0].Rows[i]["subjectname"].ToString(), dstSubjects.Tables[0].Rows[i]["subjectid"].ToString());


}
//End Add Subject Columns in dstStudent DataSet






for (int i = 0; i < dstStudents.Tables[0].Rows.Count; i++)
{


if (dstStudents.Tables[0].Columns.Count > 2)
{
for (int j = 0; j < (dstStudents.Tables[0].Columns.Count - 2); j++)
{
// string studentmarksquery = "SELECT tbl_Exam_Total_Marks.MarksObtained, tbl_Exam_Total_Marks.SubjectId FROM tbl_Exam_Total_Marks INNER JOIN tbl_Exam_Marks ON tbl_Exam_Total_Marks.MarksId = tbl_Exam_Marks.MarksId WHERE (tbl_Exam_Marks.SessionID = " + ddlSession.SelectedValue + ") AND (tbl_Exam_Marks.ClassID = " + ddlClass.SelectedValue + ") AND (tbl_Exam_Total_Marks.StudentId = '" + dstStudents.Tables[0].Rows[i][0].ToString() + "') AND (tbl_Exam_Marks.ExamId = " + ddlExam.SelectedValue + ") AND (tbl_Exam_Total_Marks.subjectid = " + dstStudents.Tables[0].Rows[i][j + 2].ToString() + ")";
string studentmarksquery = "SELECT tbl_Exam_Total_Marks.MarksObtained, tbl_Exam_Total_Marks.SubjectId FROM tbl_Exam_Total_Marks INNER JOIN tbl_Exam_Marks ON tbl_Exam_Total_Marks.MarksId = tbl_Exam_Marks.MarksId WHERE (tbl_Exam_Marks.SessionID = " + Session["session"].ToString() + ") AND (tbl_Exam_Marks.ClassID = " + ddlClass.SelectedValue + ") AND (tbl_Exam_Total_Marks.StudentId = '" + dstStudents.Tables[0].Rows[i][0].ToString() + "') AND (tbl_Exam_Marks.ExamId = " + ddlExam.SelectedValue + ") AND (tbl_Exam_Total_Marks.subjectid = " + dstStudents.Tables[0].Rows[i][j + 2].ToString() + ")";
SqlDataReader dtr = SqlHelper.ExecuteReader(Addsn.Conn(), CommandType.Text, studentmarksquery);
if (dtr.HasRows)
while (dtr.Read())
{
dstStudents.Tables[0].Rows[i][j + 2] = dtr["marksObtained"].ToString();
}
else
{
dstStudents.Tables[0].Rows[i][j + 2] = string.Empty;
}
}

}
}



dstStudents = admin_Examination_ReportListPreparation.AddColumns(dstStudents, "Total", "");

if (dstStudents.Tables[0].Columns.Count > 3)
{

for (int i = 0; i < dstStudents.Tables[0].Rows.Count; i++)
{
double total = 0;
for (int col = 0; col < dstStudents.Tables[0].Columns.Count - 3; col++)
{
if (dstStudents.Tables[0].Rows[i][col + 2].ToString().Trim() == string.Empty)
{

total += 0;
}
else
{
total += Convert.ToDouble(dstStudents.Tables[0].Rows[i][col + 2]);
}
}
dstStudents.Tables[0].Rows[i][dstStudents.Tables[0].Columns.Count - 1] = total;
}

}

if (dstStudents.Tables[0].Columns.Count > 3)
{
dstStudents = admin_Examination_ReportListPreparation.AddColumns(dstStudents, "Average", "");
int dividedby = dstStudents.Tables[0].Columns.Count - 4;

for (int i = 0; i < dstStudents.Tables[0].Rows.Count; i++)
{
dstStudents.Tables[0].Rows[i][dstStudents.Tables[0].Columns.Count - 1] = Convert.ToDouble(dstStudents.Tables[0].Rows[i][dstStudents.Tables[0].Columns.Count - 2]) / dividedby;

}
}


//DataTable dt= dstStudents.Tables[0].Select("","Average");//.DefaultView.Sort = "Total"; ;


//CopyOfStudentDataSet

//GridView1.DataSource=rows;
//GridView1.DataBind();


DataSet CopyOfStudentDataSet = new DataSet();
CopyOfStudentDataSet.Merge(dstStudents);

CopyOfStudentDataSet.Tables[0].Rows.Clear();

if (dstStudents.Tables[0].Columns.Count > 3)
{
DataRow[] rows = dstStudents.Tables[0].Select("", "Average desc");//.DefaultView.Sort = "Total"; ;




foreach (DataRow dr in rows)
{
CopyOfStudentDataSet.Tables[0].ImportRow(dr);
}





CopyOfStudentDataSet = admin_Examination_ReportListPreparation.AddColumns(CopyOfStudentDataSet, "Rank", "");
double avg = Convert.ToDouble(CopyOfStudentDataSet.Tables[0].Rows[0]["Average"].ToString());
int rank = 1;
for (int i = 0; i < CopyOfStudentDataSet.Tables[0].Rows.Count; i++)
{
if (avg == Convert.ToDouble(CopyOfStudentDataSet.Tables[0].Rows[i]["Average"].ToString()))
{
CopyOfStudentDataSet.Tables[0].Rows[i]["Rank"] = Convert.ToString(rank);

}
else if (avg > Convert.ToDouble(CopyOfStudentDataSet.Tables[0].Rows[i]["Average"].ToString()))
{
rank++;
CopyOfStudentDataSet.Tables[0].Rows[i]["Rank"] = Convert.ToString(rank);
avg = Convert.ToDouble(CopyOfStudentDataSet.Tables[0].Rows[i]["Average"].ToString());
}
}


if (dstStudents.Tables[0].Columns.Count > 3)
{
CopyOfStudentDataSet = admin_Examination_ReportListPreparation.AddColumns(CopyOfStudentDataSet, "Grade", "");

for (int i = 0; i < CopyOfStudentDataSet.Tables[0].Rows.Count; i++)
{
double totavg = Convert.ToDouble(CopyOfStudentDataSet.Tables[0].Rows[i]["Average"].ToString());

double lowsubtotal = 100;
for (int j = 0; j < CopyOfStudentDataSet.Tables[0].Columns.Count - 4; j++)
{
if (CopyOfStudentDataSet.Tables[0].Rows[i][j + 2].ToString() != string.Empty)
{

if (lowsubtotal > Convert.ToDouble(CopyOfStudentDataSet.Tables[0].Rows[i][j + 2].ToString()))
{
lowsubtotal = Convert.ToDouble(CopyOfStudentDataSet.Tables[0].Rows[i][j + 2].ToString());
}
}
else
{
lowsubtotal = 0;
}

}
if (totavg == lowsubtotal)
{
//DataSet ds = SqlHelper.ExecuteDataset(Addsn.Conn(), CommandType.Text, "select * from tbl_exam where Convert(float,totalaverage)<=" + totavg + " and classid="+ddlClass.SelectedItem.Value+" and sectionid="+ddlSection.SelectedItem.Value+" and session="+ddlSession.SelectedValue+" order by totalaverage desc ");
DataSet ds = SqlHelper.ExecuteDataset(Addsn.Conn(), CommandType.Text, "select * from tbl_exam where Convert(float,totalaverage)<=" + totavg + " and classid=" + ddlClass.SelectedItem.Value + " and sectionid=" + ddlSection.SelectedItem.Value + " and session=" + Session["session"].ToString() + " order by totalaverage desc ");
if (ds.Tables[0].Rows.Count != 0)
{
CopyOfStudentDataSet.Tables[0].Rows[i]["Grade"] = ds.Tables[0].Rows[0]["grade"].ToString();
}
}
else
{
//DataSet ds = SqlHelper.ExecuteDataset(Addsn.Conn(), CommandType.Text, "select * from tbl_exam where Convert(float,subjectaverage)<=" + lowsubtotal + " and classid=" + ddlClass.SelectedItem.Value + " and sectionid=" + ddlSection.SelectedItem.Value + " and session=" + ddlSession.SelectedValue + " order by subjectaverage desc ");
DataSet ds = SqlHelper.ExecuteDataset(Addsn.Conn(), CommandType.Text, "select * from tbl_exam where Convert(float,subjectaverage)<=" + lowsubtotal + " and classid=" + ddlClass.SelectedItem.Value + " and sectionid=" + ddlSection.SelectedItem.Value + " and session=" + Session["session"].ToString() + " order by subjectaverage desc ");
if (ds.Tables[0].Rows.Count != 0)
{
CopyOfStudentDataSet.Tables[0].Rows[i]["Grade"] = ds.Tables[0].Rows[0]["grade"].ToString();
}
}
}
}



}
//dstStudents.Tables[0].DefaultView.Sort = "Total"; ;
if (dstStudents.Tables[0].Columns.Count > 3)
{
DataSet temp = new DataSet();
dt = CopyOfStudentDataSet.Tables[0].Clone();
dt.Clear();
foreach (DataRow dr in CopyOfStudentDataSet.Tables[0].Select("", RadioButtonList1.SelectedItem.Value + " " + RadioButtonList2.SelectedItem.Value))
{
dt.ImportRow(dr);
}
}
GridView1.DataSource = dt;
GridView1.DataBind();
}
catch
{

}

}
public static DataSet AddColumns(DataSet dt, string ColumnName, string Value)
{

try
{
dt.Tables[0].Columns.Add(ColumnName, typeof(String));
foreach (DataRow drow in dt.Tables[0].Rows)
{

drow[ColumnName] = Value;
}
return dt;
}
catch
{
throw;
}
}
protected void Button1_Click(object sender, EventArgs e)
{
Export.ExportFunction.ExportExcel(dt, "Result");
}
protected void lbfilter_Click(object sender, EventArgs e)
{

if (lbfilter.CommandName == "show")
{
Panel2.Visible = true;
ddlcols.Items.Clear();
for (int i = 0; i < dt.Columns.Count; i++)
{
ddlcols.Items.Insert(i, dt.Columns[i].ToString());
}
lbfilter.CommandName = "";
lbfilter.Text="Remove Filter";
}
else
{
lbfilter.CommandName = "show";
lbfilter.Text = "Show Filter";
ddlcols.Items.Clear();
Panel2.Visible = false;
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
protected void ddlcols_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
lblfield.Text = ddlcols.SelectedItem.Text;

Panel3.Visible = true;
if (ddlcols.SelectedItem.Text == dt.Columns[1].ColumnName)
{
lblgt.Text = "Name";
lbllt.Visible = false;
txtlt.Visible = false;
}
else if (ddlcols.SelectedItem.Text == "Grade")
{
lblgt.Text = "Grade";
lbllt.Visible = false;
txtlt.Visible = false;
}
else if (ddlcols.SelectedItem.Text == dt.Columns[0].ColumnName)
{
lblgt.Text = "Registration No.";
lbllt.Visible = false;
txtlt.Visible = false;
}

else
{
lblgt.Text = ">=";
lbllt.Visible = true;
txtlt.Visible = true;
}


txtgt.Text = "";
txtlt.Text = "";
}
catch
{

}
}
protected void btngo_Click(object sender, EventArgs e)
{
try
{
if (ddlcols.SelectedItem.Text == dt.Columns[1].ColumnName)
{
DataTable dt1 = new DataTable();

dt1 = dt.Clone();
dt1.Clear();
string expr = "studentname like " + "'" + txtgt.Text + "%" + "'";
foreach (DataRow dr in dt.Select(expr))
{
dt1.ImportRow(dr);
}

GridView1.DataSource = dt1;
GridView1.DataBind();

}

else if (ddlcols.SelectedItem.Text == "Grade")
{
DataTable dt1 = new DataTable();

dt1 = dt.Clone();
dt1.Clear();
string expr = "Grade = " + "'" + txtgt.Text + "'";
foreach (DataRow dr in dt.Select(expr))
{
dt1.ImportRow(dr);
}

GridView1.DataSource = dt1;
GridView1.DataBind();

}
else if (ddlcols.SelectedItem.Text == dt.Columns[0].ColumnName)
{
DataTable dt1 = new DataTable();

dt1 = dt.Clone();
dt1.Clear();
string expr = dt.Columns[0].ColumnName + " like " + "'%" + txtgt.Text + "%" + "'";
foreach (DataRow dr in dt.Select(expr))
{
dt1.ImportRow(dr);
}

GridView1.DataSource = dt1;
GridView1.DataBind();

}
else
{

if (txtgt.Text == "")
{
txtgt.Text = "0";
}
if (txtlt.Text == "")
{
txtlt.Text = "100";
}

if (Convert.ToDouble(txtgt.Text) <= Convert.ToDouble(txtlt.Text))
{
DataTable dt1 = new DataTable();
DataTable dt2 = new DataTable();
dt1 = dt.Clone();
dt2 = dt;
dt1.Clear();


for (int i = 0; i < dt2.Rows.Count; i++)
{
for (int j = 0; j < dt2.Columns.Count - 4; j++)
{
if (dt.Rows[i][j + 2].ToString() == string.Empty)
{
dt.Rows[i][j + 2] = "0";
}
}

}
string expr = "convert(" + ddlcols.SelectedItem.Text + ",System.Double)>=" + Convert.ToDouble(txtgt.Text) + "and convert(" + ddlcols.SelectedItem.Text + ",System.Double)<=" + Convert.ToDouble(txtlt.Text);
foreach (DataRow dr in dt2.Select(expr))
{
dt1.ImportRow(dr);
}

GridView1.DataSource = dt1;
GridView1.DataBind();
}
else
{
WebMsgBox.clsWebMsgBox.Show("Lower Limit Cannot Exceed Upper Limit");
}
}
}
catch
{

}

}
}


Author: prakash             
Posted Date: March 21, 2011     Points: 20   

Hi krishana singh ,
I am in need of student counselling project, It will be so helpful if you give me the database details and the details of the class key.And its hard to find remaining code that calling PageBase.FillDropDownList function .Please help me on this and send as the project to my mail ID prakashbtech@gmail.com,really very thanks for your support.



Post Reply

You must Sign In To post reply
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend