07-05-2011, 03:00 PM
Student Marks Report.cs
Code:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
namespace SchoolManagementSystem
{
public partial class Student_MarksReport : Form
{
OleDbConnection cn = new OleDbConnection(ConnectionStringForSMS.MyConnectionString());
string s1;
string s2;
string s3;
string s4;
int classid, sectionid,exam;
public Student_MarksReport()
{
InitializeComponent();
}
private void Student_MarksReport_Load(object sender, EventArgs e)
{
try
{
OleDbDataAdapter da23 = new OleDbDataAdapter("select schooltitle,Address from schooltitle", cn);
DataSet ds23 = new DataSet();
da23.Fill(ds23);
string schooltitle = ds23.Tables[0].Rows[0].ItemArray[0].ToString();
string address = ds23.Tables[0].Rows[0].ItemArray[1].ToString();
this.Text = schooltitle + "-Student Marks Report";
label4.Text = "";
label4.Text = schooltitle;
cn.Open();
cmbMedium.Focus();
cmbMedium.SelectedIndex = 0;
cmbClass.Text ="--Select--";
cmbSection.Text ="--Select--";
cmbExam.Text = "--Select--";
dataGridView1.Visible = false;
btnPrint.Visible = false;
}
catch (Exception e2)
{
MessageBox.Show("Try Again", "School Management System", MessageBoxButtons.OK, MessageBoxIcon.Error);
dataGridView1.Visible = false;
}
finally
{
cn.Close();
}
}
private void btnGo_Click(object sender, EventArgs e)
{
cmbMedium.Focus();
if (cmbMedium.SelectedIndex==0)
{
MessageBox.Show("Select Medium", "School Management System", MessageBoxButtons.OK, MessageBoxIcon.Information);
cmbMedium.Focus();
}
else if (cmbClass.SelectedIndex == -1)
{
MessageBox.Show("Select Class", "School Management System", MessageBoxButtons.OK, MessageBoxIcon.Information);
cmbClass.Focus();
}
else if (cmbSection.SelectedIndex == -1)
{
MessageBox.Show("Select Section", "School Management System", MessageBoxButtons.OK, MessageBoxIcon.Information);
cmbSection.Focus();
}
else if (cmbExam.SelectedIndex ==-1)
{
MessageBox.Show("Select Examname", "School Management System", MessageBoxButtons.OK, MessageBoxIcon.Information);
cmbExam.Focus();
}
else
{
s1 = cmbClass.SelectedItem.ToString();
s2 = cmbSection.SelectedItem.ToString();
s3 = cmbMedium.SelectedItem.ToString();
s4 = cmbExam.SelectedItem.ToString();
OleDbDataAdapter adpt1 = new OleDbDataAdapter("SELECT Total,ranks,grade FROM StudentMarks WHERE SectionID=" + sectionid + " and ClassID=" + classid + " and ExamID=" + exam + " ORDER BY cdbl(total) DESC", cn);
DataSet dsranks1 = new DataSet();
adpt1.Fill(dsranks1);
int j = 0;
for (int i = 0; i < dsranks1.Tables[0].Rows.Count; i++)
{
if (dsranks1.Tables[0].Rows[i]["grade"].ToString() != "F")
{
j++;
cn.Open();
OleDbCommand cmdranks = new OleDbCommand("update studentmarks set ranks=" + j + " where total='" + dsranks1.Tables[0].Rows[i]["total"].ToString() + "' and SectionID=" + sectionid + " and ClassID=" + classid + " and ExamID=" + exam + " ", cn);
cmdranks.ExecuteNonQuery();
cn.Close();
}
}
OleDbDataAdapter adpt12 = new OleDbDataAdapter("SELECT Total,ranks,grade FROM StudentMarks WHERE ClassID=" + classid + " and ExamID=" + exam + " ORDER BY cdbl(total) DESC", cn);
DataSet dsranks12 = new DataSet();
adpt12.Fill(dsranks12);
int q = 0;
for (int i = 0; i < dsranks12.Tables[0].Rows.Count; i++)
{
if (dsranks12.Tables[0].Rows[i]["grade"].ToString() != "F")
{
q++;
cn.Open();
OleDbCommand cmdranks = new OleDbCommand("update studentmarks set ClassRank=" + q + " where total='" + dsranks12.Tables[0].Rows[i]["total"].ToString() + "' and SectionID=" + sectionid + " and ClassID=" + classid + " and ExamID=" + exam + " ", cn);
cmdranks.ExecuteNonQuery();
cn.Close();
}
}
OleDbDataAdapter adpt3 = new OleDbDataAdapter("SELECT DISTINCT StudentMarks.RollNo,StudentMarks.Name, Class.ClassName,[Section].[Section], Subject.Medium, Exam.ExamName, StudentMarks.Total, StudentMarks.Grade, StudentMarks.PassOrFail,StudentMarks.Ranks,StudentMarks.ClassRank FROM (((Exam INNER JOIN (Class INNER JOIN (StudentMarks INNER JOIN [Section] ON StudentMarks.SectionID = [Section].SectionID) ON Class.ClassID = StudentMarks.ClassID) ON Exam.ClassID = StudentMarks.ClassID AND Exam.ExamID = StudentMarks.ExamID) INNER JOIN Subject ON StudentMarks.ClassID = Subject.ClassID) INNER JOIN Admission ON StudentMarks.AdmissionNo = Admission.AdmissionNo) WHERE (Class.ClassName = '" + cmbClass.SelectedItem.ToString() + "') AND ([Section].[Section] = '" + cmbSection.SelectedItem.ToString() + "') AND (Subject.Medium = '" + cmbMedium.SelectedItem.ToString() + "') AND (Exam.ExamName = '" + cmbExam.SelectedItem.ToString() + "') and Active='Yes'", cn);
DataSet ds3 = new DataSet();
adpt3.Fill(ds3);
if (ds3.Tables[0].Rows.Count != 0)
{
dataGridView1.Visible = true;
btnPrint.Visible = true;
dataGridView1.DataSource = ds3.Tables[0];
}
else
{
dataGridView1.Visible = false;
btnPrint.Visible = false;
MessageBox.Show("No Records Found", "School Management System", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
private void btnPrint_Click(object sender, EventArgs e)
{
PrintStudent_MarksReport psmr = new PrintStudent_MarksReport(s1, s2, s3, s4);
psmr.Show();
}
private void btnClear_Click(object sender, EventArgs e)
{
cmbClass.DataSource = null;
cmbClass.Items.Clear();
cmbSection.DataSource = null;
cmbSection.Items.Clear();
dataGridView1.Visible = false;
btnPrint.Visible = false;
cmbClass.Focus();
cmbMedium.SelectedIndex=0;
cmbExam.Text = "--Select--";
cmbClass.SelectedIndex =-1;
cmbClass.Text = "--Select--";
cmbSection.SelectedIndex=-1;
cmbSection.Text = "--Select--";
}
private void cmbMedium_SelectedIndexChanged(object sender, EventArgs e)
{
cmbClass.Items.Clear();
cmbSection.Items.Clear();
cmbExam.Items.Clear();
OleDbDataAdapter da = new OleDbDataAdapter("SELECT ClassName FROM Class WHERE (Medium = '" + cmbMedium.SelectedItem.ToString() + "')", cn);
DataSet ds = new DataSet();
da.Fill(ds);
cmbClass.DisplayMember = "ClassName";
if (ds.Tables[0].Rows.Count != 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
cmbClass.Items.Add(ds.Tables[0].Rows[i].ItemArray[0].ToString());
}
}
}
private void cmbClass_SelectedIndexChanged(object sender, EventArgs e)
{
cmbSection.Items.Clear();
cmbExam.Items.Clear();
cmbSection.Text = "--Select--";
OleDbDataAdapter da1 = new OleDbDataAdapter("SELECT ClassID FROM Class WHERE (ClassName = '" + cmbClass.SelectedItem.ToString() + "') and Medium='" + cmbMedium.SelectedItem.ToString() + "'", cn);
DataSet ds1 = new DataSet();
da1.Fill(ds1);
if (ds1.Tables[0].Rows.Count != 0)
{
classid = Convert.ToInt32(ds1.Tables[0].Rows[0][0].ToString());
}
OleDbDataAdapter da = new OleDbDataAdapter("SELECT [Section] FROM [Section] WHERE (ClassId = " + classid + ") and Medium='" + cmbMedium.SelectedItem.ToString() + "'", cn);
DataSet ds = new DataSet();
da.Fill(ds);
cmbSection.DisplayMember = "Section";
if (ds.Tables[0].Rows.Count != 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
cmbSection.Items.Add(ds.Tables[0].Rows[i].ItemArray[0].ToString());
}
}
OleDbDataAdapter da3 = new OleDbDataAdapter("SELECT ExamName FROM Exam WHERE (ClassID = " + classid + " ) and Medium='" + cmbMedium.SelectedItem.ToString() + "'", cn);
DataSet ds3 = new DataSet();
da3.Fill(ds3);
cmbExam.DisplayMember = "ExamName";
for (int j = 0; j < ds3.Tables[0].Rows.Count; j++)
{
cmbExam.Items.Add(ds3.Tables[0].Rows[j].ItemArray[0].ToString());
}
cmbExam.ValueMember = "ExamName";
}
private void cmbSection_SelectedIndexChanged(object sender, EventArgs e)
{
OleDbDataAdapter da = new OleDbDataAdapter("SELECT SectionID FROM [Section] WHERE ([Section] = '" + cmbSection.SelectedItem.ToString() + "') and (ClassId = " + classid + ") and Medium='" + cmbMedium.SelectedItem.ToString() + "'", cn);
DataSet ds = new DataSet();
da.Fill(ds);
if (ds.Tables[0].Rows.Count != 0)
{
sectionid = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
}
}
private void cmbExam_SelectedIndexChanged(object sender, EventArgs e)
{
OleDbDataAdapter da2 = new OleDbDataAdapter("SELECT ExamID FROM Exam WHERE (ExamName = '" + cmbExam.SelectedItem.ToString() + "') AND (ClassID = " + classid + ") and Medium='" + cmbMedium.SelectedItem.ToString() + "'", cn);
DataSet ds2 = new DataSet();
da2.Fill(ds2);
if (ds2.Tables[0].Rows.Count != 0)
{
exam = Convert.ToInt32(ds2.Tables[0].Rows[0][0].ToString());
}
}
private void pictureBox1_Click(object sender, EventArgs e)
{
this.Close();
ReportsForm r = new ReportsForm();
r.Show();
}
private void pictureBox2_Click(object sender, EventArgs e)
{
this.Close();
SMSMainForm a = new SMSMainForm();
a.Show();
}
}
}