private void InitialiseDB()
{
lblinit.Text = "Initialising Database. Please Wait..";
lblinit.ForeColor = Color.Red;
lblinit.Visible = true;
txtSQL.Visible = true;
try
{
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
string file = appPath + "\\SQL\\schoolguru.sql";
SQLConn.sqL = File.ReadAllText(file).ToString();
txtSQL.Text = SQLConn.sqL;
SQLConn.ConnOpen();
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
int r = SQLConn.cmd.ExecuteNonQuery();
if (r > 0)
{
MessageBox.Show("Database has been Initialised Successfully", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
SQLConn.ConnClose();
}
catch (NpgsqlException ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public StringCollection FeeTypes()
{
StringCollection SC = new StringCollection();
try
{
SQLConn.ConnOpen();
SQLConn.sqL = "Select feetypename from feetype ";
// MessageBox.Show(SQLConn.sqL);
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
while (SQLConn.dr.Read() == true)
{
SC.Add(SQLConn.dr["feetypename"].ToString());
}
SQLConn.ConnClose();
return(SC);
}
catch (NpgsqlException ex)
{
MessageBox.Show(ex.Message);
return(null);
}
finally
{
SQLConn.cmd.Dispose();
}
}
public void LoadSubjects()
{
try
{
SQLConn.ConnOpen();
SQLConn.sqL = "select id ,subjectcode, subjectname,classname from subject where 1=1 ";
if (int.Parse(cmbClass.SelectedValue.ToString()) > 0)
{
SQLConn.sqL += " AND classname= '" + cmbClass.Text + "' ";
}
SQLConn.sqL += " order by classname,subjectcode";
SQLConn.da = new NpgsqlDataAdapter(SQLConn.sqL, SQLConn.conn);
dataGridView1.DataSource = null;
DataTable dt = new DataTable();
SQLConn.da.Fill(dt);
dataGridView1.DataSource = dt;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.ConnClose();
}
}
public void FillComboExamType(ComboBox cb)
{
try
{
SQLConn.ConnOpen();
SQLConn.sqL = "select id, examtypename from examtype ";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.da = new NpgsqlDataAdapter(SQLConn.cmd);
DataTable dt = new DataTable();
dt.Columns.Add("id", typeof(string));
dt.Columns.Add("examtypename", typeof(string));
dt.Rows.Add(new Object[] { -1, "Select Exam Type" });
SQLConn.da.Fill(dt);
cb.DataSource = dt;
cb.DisplayMember = "examtypename";
cb.ValueMember = "id";
SQLConn.ConnClose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public DataTable SearchStudent(string srch)
{
DataTable dt = new DataTable();
try
{
SQLConn.ConnOpen();
SQLConn.sqL = "Select concat (firstname, ' ', othername) as fullname , admissionno from studentdetail Where admissionno ILike '%" + srch + "%' or firstname ILike '%" + srch + "%' ";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.da = new NpgsqlDataAdapter(SQLConn.cmd);
dt.Columns.Add("admissionno", typeof(string));
dt.Columns.Add("fullname", typeof(string));
SQLConn.da.Fill(dt);
SQLConn.ConnClose();
return(dt);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
return(null);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.ConnClose();
}
}
private void SaveMarks()
{
if (!ValidEntry())
{
return;
}
try {
SQLConn.sqL = "insert into exammarks(examid, admissionno , subjectcode, markobtained, totalmark) VALUES ('" + lblExamtitle.Tag + "','" + txtStudent.Text + "', '" + lblSubject.Tag + "', '" + txtMark.Text + "', '" + txtTotal.Text + "' )";
SQLConn.ConnOpen();
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
ListStudentMarks();
Reset();
// cf.LogFunc(st1, System.DateTime.Now, st2);
MessageBox.Show("Successfully saved", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
SQLConn.ConnClose();
}
}
private void UpdateFee()
{
if (!ValidEntry())
{
return;
}
try
{
SQLConn.ConnOpen();
SQLConn.sqL = "update feeschedule set term='" + cmbTerm.SelectedValue + "',feetype='" + cmbFeeType.Text + "' ,amount='" + txtAmount.Text + "' , sclass='" + cmbClass.Text + "' where id='" + txtAmount.Tag + "'";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
MessageBox.Show("Successfully updated", "Exam Details", MessageBoxButtons.OK, MessageBoxIcon.Information);
// st1 = lblUser.Text;
// st2 = "Updated Exam'" +txtExamName.Text+ "'";
// cf.LogFunc(st1, System.DateTime.Now, st2);
SQLConn.ConnClose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void delete_records()
{
try
{
int RowsAffected = 0;
SQLConn.ConnOpen();
SQLConn.sqL = "delete from examtype where id = '" + txtExamName.Tag + "'";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
if (RowsAffected > 0)
{
MessageBox.Show("Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information);
string st2 = "Deleted Exam '" + txtExamName.Text + "'";
// cf.LogFunc(st1, System.DateTime.Now, st2);
Reset();
}
else
{
MessageBox.Show("No Record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information);
Reset();
}
SQLConn.ConnClose();
GetData();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void ScholarNo_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
SQLConn.ConnOpen();
SQLConn.sqL = "select RTrim(FeePaymentID)[Fee Payment ID], RTRIM(Student.AdmissionNo)[Admission No.],RTRIM(Studentname)[Student Name],RTRIM(Student.Class)[Class],RTRIM(Section)[Section],RTRIM(Transportation.SourceLocation)[Source Location],RTRIM(BusFeePayment.BusCharges)[Bus Charges],RTRIM(DateOfPayment)[Payment Date],RTRIM(ModeOfPayment)[Mode Of Payment],RTRIM(PaymentModeDetails)[Payment Mode Details],RTRIM(TotalPaid)[Total Paid],RTRIM(Fine)[Fine],RTRIM(DueFees)[Due Fees] from BusFeePayment,Student,Transportation,BusHolders where Student.AdmissionNo=BusHolders.AdmissionNo and BusFeePayment.AdmissionNo=Student.AdmissionNo and Transportation.SourceLocation=BusHolders.SourceLocation and Busfeepayment.AdmissionNo= '" + AdmissionNo.Text + "'order by DateOfPayment";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
NpgsqlDataAdapter da = new NpgsqlDataAdapter();
DataSet myDataSet = new DataSet();
da.Fill(myDataSet, "BusFeePayment");
da.Fill(myDataSet, "Transportation");
da.Fill(myDataSet, "Student");
da.Fill(myDataSet, "BusHolders");
dataGridView2.DataSource = myDataSet.Tables["BusFeePayment"].DefaultView;
dataGridView2.DataSource = myDataSet.Tables["Transportation"].DefaultView;
dataGridView2.DataSource = myDataSet.Tables["Student"].DefaultView;
dataGridView2.DataSource = myDataSet.Tables["BusHolders"].DefaultView;
SQLConn.ConnClose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void DeleteUser()
{
try
{
SQLConn.ConnOpen();
if (txtUserId.Tag.ToString() == "1")
{
MessageBox.Show("Cannot Delete Super Admin");
return;
}
SQLConn.sqL = "delete from user_registration where id=@f1";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.Parameters.AddWithValue("@f1", txtUserId.Tag);
SQLConn.cmd.ExecuteNonQuery();
SQLConn.ConnClose();
MessageBox.Show("Deleted Successfully");
Clears();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public void LoadEvents()
{
try
{
SQLConn.ConnOpen();
DataTable dt = new DataTable();
DGV.DataSource = null;
SQLConn.sqL = "SELECT id, eventname, startdate, enddate, manager, activity FROM event order by startdate asc";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
// data adapter making request from our connection
SQLConn.da = new NpgsqlDataAdapter(SQLConn.cmd);
MessageBox.Show(dt.Rows.Count.ToString());
SQLConn.da.Fill(dt);
DGV.DataSource = dt;
SQLConn.ConnClose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void Update_record_Click(object sender, EventArgs e)
{
try
{
SQLConn.ConnOpen();
SQLConn.sqL = "update staffset staffname=@d2,department=@d3,gender=@d4,fathername=@d5,permanentaddress=@d6,temporaryaddress=@d7,phoneno=@d8,mobileno=@d9,dateofjoining=@d10,qualification=@d11,yearofexperience=@d12,designation=@d13,email=@d14,Basicsalary=@d15,picture=@d16,DOB=@d17,mothername=@d19,status=@d18 where staffid=@d1";
SQLConn.cmd.ExecuteNonQuery();
SQLConn.ConnClose();
MessageBox.Show("Successfully Updated", "staffRecord", MessageBoxButtons.OK, MessageBoxIcon.Information);
st2 = "Updated the Staff'" + txtStaffName.Text + "' having StaffID '" + txtStaffID.Text + "'";
// cf.LogFunc(st1, System.DateTime.Now, st2);
btnupdate.Enabled = false;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.ConnClose();
}
}
private void Update_record_Click(object sender, EventArgs e)
{
if (!ValidEntry())
{
return;
}
try
{
SQLConn.ConnOpen();
SQLConn.sqL = "update exam set examname='" + txtExamName.Text + "',examtype='" + cmbExamType.SelectedValue + "' , startdate='" + TM.TimeToString(dtpStart.Value) + "', schoolclass='" + cmbClass.Text + "' , schoolterm='" + cmbTerm.SelectedValue + "' , enddate='" + TM.TimeToString(dtpEnd.Value) + "' where id='" + txtExamName.Tag + "'";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
Debug.WriteLine(SQLConn.sqL);
MessageBox.Show("Successfully updated", "Exam Details", MessageBoxButtons.OK, MessageBoxIcon.Information);
// st1 = lblUser.Text;
// st2 = "Updated Exam'" +txtExamName.Text+ "'";
// cf.LogFunc(st1, System.DateTime.Now, st2);
btnUpdate.Enabled = false;
ListExams();
SQLConn.ConnClose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void DeleteTeacherSubject()
{
try
{
int t = int.Parse(cmbStaff.SelectedValue.ToString());
string s = cmbSubject.SelectedValue.ToString();
SQLConn.sqL = "delete from staffsubject where id=" + int.Parse(btnDelete.Tag.ToString()) + "";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
Console.WriteLine(SQLConn.sqL);
st2 = "Deleted '" + groupBox1.Tag + "'";
MessageBox.Show(st2, "Record", MessageBoxButtons.OK, MessageBoxIcon.Information);
// cf.LogFunc(st1, System.DateTime.Now, st2);
Reset();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.ConnClose();
}
}
private void SaveSchoolDetails()
{
try
{
SQLConn.ConnOpen();
SQLConn.sqL = "Update studentdetail set school= @school,category= @category,sclass= @sclass,section= @section,term= @term, status= @status WHERE admissionno='" + txtAdmNo.Text + "'";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.Parameters.AddWithValue("@school", cmbSchool.SelectedValue.ToString());
SQLConn.cmd.Parameters.AddWithValue("@category", cmbCategory.Text);
SQLConn.cmd.Parameters.AddWithValue("@sclass", cmbClass.Text);
SQLConn.cmd.Parameters.AddWithValue("@section", cmbSection.Text);
SQLConn.cmd.Parameters.AddWithValue("@term", cmbTerm.SelectedValue.ToString());
SQLConn.cmd.Parameters.AddWithValue("@status", cmbStatus.SelectedValue.ToString());
SQLConn.cmd.ExecuteNonQuery();
MessageBox.Show(" " + fname.Text + " " + sname.Text + " 's" + " " + "Records saved successfully", "Data Saved!", MessageBoxButtons.OK, MessageBoxIcon.Information);
SQLConn.ConnClose();
}
catch (NpgsqlException ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
}
}
private void button14_Click(object sender, EventArgs e)
{
try
{
SQLConn.ConnOpen();
SQLConn.sqL = "select RTrim(FeePaymentID)[Fee Payment ID], RTRIM(Student.AdmissionNo)[Admission No.],RTRIM(Studentname)[Student Name],RTRIM(Student.Class)[Class],RTRIM(Section)[Section],RTRIM(Transportation.SourceLocation)[Source Location],RTRIM(BusFeePayment.BusCharges)[Bus Charges],RTRIM(DateOfPayment)[Payment Date],RTRIM(ModeOfPayment)[Mode Of Payment],RTRIM(PaymentModeDetails)[Payment Mode Details],RTRIM(TotalPaid)[Total Paid],RTRIM(Fine)[Fine],RTRIM(DueFees)[Due Fees] from BusFeePayment,Student,Transportation,BusHolders where Student.AdmissionNo=BusHolders.AdmissionNo and BusFeePayment.AdmissionNo=Student.AdmissionNo and Transportation.SourceLocation=BusHolders.SourceLocation and DateofPayment between @date1 and @date2 and Fine>0 order by DateOfPayment";
SQLConn.cmd.Parameters.AddWithValue("@date1", "DateOfPayment").Value = dateTimePicker1.Value.Date;
SQLConn.cmd.Parameters.AddWithValue("@date2", "DateOfPayment").Value = dateTimePicker2.Value.Date;
NpgsqlDataAdapter myDA = new NpgsqlDataAdapter(SQLConn.cmd);
DataSet myDataSet = new DataSet();
myDA.Fill(myDataSet, "BusFeePayment");
myDA.Fill(myDataSet, "Transportation");
myDA.Fill(myDataSet, "Student");
myDA.Fill(myDataSet, "BusHolders");
dataGridView5.DataSource = myDataSet.Tables["BusFeePayment"].DefaultView;
dataGridView5.DataSource = myDataSet.Tables["Transportation"].DefaultView;
dataGridView5.DataSource = myDataSet.Tables["Student"].DefaultView;
dataGridView5.DataSource = myDataSet.Tables["BusHolders"].DefaultView;
SQLConn.ConnClose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void LoadDepartments()
{
try
{
conn = new NpgsqlConnection(SQLConn.DBcon);
conn.Open();
DataSet ds = new DataSet();
DataTable dt = new DataTable();
DataGridView1.DataSource = null;
SQLConn.sqL = "SELECT * FROM department order by id";
// data adapter making request from our connection
NpgsqlDataAdapter da = new NpgsqlDataAdapter(SQLConn.sqL, conn);
da.Fill(dt);
DataGridView1.DataSource = dt;
SQLConn.ConnClose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void Course_SelectedIndexChanged(object sender, EventArgs e)
{
txtsection.Items.Clear();
txtsection.Text = "";
txtsection.Enabled = true;
try
{
SQLConn.ConnOpen();
SQLConn.sqL = "select distinct RTRIM(section) from BusFeePayment,BusHolders,Student where BusFeePayment.AdmissionNo=Student.AdmissionNo and BusHolders.AdmissionNo=Student.AdmissionNo and Student.class= '" + txtclass.Text + "'";
SQLConn.dr = SQLConn.cmd.ExecuteReader();
while (SQLConn.dr.Read())
{
txtsection.Items.Add(SQLConn.dr[0]);
}
SQLConn.ConnClose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void Update_record_Click(object sender, EventArgs e)
{
if (!ValidEntry())
{
return;
}
try
{
SQLConn.ConnOpen();
//SQLConn.sqL = "update exam set examname='" + cmbExam.SelectedValue + "', startdate='', class='" + cmbClass.SelectedValue + "' , schoolterm='" + cmbTerm.SelectedValue + "' , enddate='' where id='" + cmbExam.SelectedValue + "'";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
MessageBox.Show("Successfully updated", "Exam Details", MessageBoxButtons.OK, MessageBoxIcon.Information);
// st1 = lblUser.Text;
// st2 = "Updated Exam'" +cmbExam.SelectedValue+ "'";
// cf.LogFunc(st1, System.DateTime.Now, st2);
ListStudentMarks();
Reset();
SQLConn.ConnClose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public DataTable GetClassSubjects(string schoolclass)
{
try
{
SQLConn.sqL = "SELECT subjectcode, subjectname from subject Where classname='" + schoolclass + "'";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.da = new NpgsqlDataAdapter(SQLConn.cmd);
DataTable dt = new DataTable();
dt.Columns.Add("subjectcode", typeof(string));
dt.Columns.Add("subjectname", typeof(string));
dt.Rows.Add(new Object[] { "-1", "Select Subject" });
SQLConn.da.Fill(dt);
return(dt);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
finally
{
SQLConn.ConnClose();
SQLConn.cmd.Dispose();
}
}
public void FillComboStudentCategory(ComboBox cb)
{
try
{
DataTable dt = new DataTable();
dt.Columns.Add("id", typeof(int));
dt.Columns.Add("category", typeof(string));
dt.Rows.Add(new Object[] { -1, "Select Category" });
dt.Rows.Add(new Object[] { 1, "Enrolled" });
dt.Rows.Add(new Object[] { 2, "Deffered" });
dt.Rows.Add(new Object[] { 3, "Suspended" });
dt.Rows.Add(new Object[] { 4, "Expelled" });
dt.Rows.Add(new Object[] { 5, "Transferred" });
dt.Rows.Add(new Object[] { 6, "Cleared" });
cb.DataSource = dt;
cb.DisplayMember = "category";
cb.ValueMember = "id";
SQLConn.ConnClose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void FillRecords()
{
try
{
string s = "doj between @date1 and @date2 ";
SQLConn.sqL = "select id, staffname, department, gender,dob,status, paddress,taddress,phone, mobile, doj,qualification,yop, designation, email, salary from staff WHERE 1=1 ";
if (txtStaffName.Text.Trim() != "")
{
SQLConn.sqL += " AND staffname ILIKE '%" + txtStaffName.Text + "%' ";
}
SQLConn.sqL += " order by staffname ";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.da = new NpgsqlDataAdapter(SQLConn.cmd);
DataTable dt = new DataTable();
SQLConn.da.Fill(dt);
DGV.DataSource = dt;
SQLConn.ConnClose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void FillComboFeeType(ComboBox cb)
{
cb.DataSource = SQLConn.dsSchool.Tables["feetype"];
cb.DisplayMember = "feetypename";
cb.ValueMember = "id";
SQLConn.ConnClose();
}
public void GetData()
{
try
{
SQLConn.ConnOpen();
SQLConn.sqL = "SELECT sclass.id, sclass.classname,section from sclass order by classname , section";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.da = new NpgsqlDataAdapter(SQLConn.cmd);
DataTable dt = new DataTable();
SQLConn.da.Fill(dt);
DataGridView1.DataSource = dt;
SQLConn.ConnClose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.ConnClose();
}
}
public void FillComboSubject(ComboBox cb, string sclass)
{
try
{
SQLConn.ConnOpen();
SQLConn.sqL = "select subjectcode, subjectname from subject ";
if (sclass != "")
{
SQLConn.sqL += " WHERE classname= '" + sclass + "'";
}
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.da = new NpgsqlDataAdapter(SQLConn.cmd);
DataTable dt = new DataTable();
dt.Columns.Add("subjectcode", typeof(string));
dt.Columns.Add("subjectname", typeof(string));
dt.Rows.Add(new Object[] { "-1", "Select Subject" });
SQLConn.da.Fill(dt);
cb.DataSource = dt;
cb.DisplayMember = "subjectname";
cb.ValueMember = "subjectcode";
SQLConn.ConnClose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnUpdate_Click(object sender, EventArgs e)
{
try
{
SQLConn.ConnOpen();
SQLConn.sqL = "Update class set classname= '" + txtClass.Text.Trim() + "', section= '" + cmbSection.Text.Trim() + "' where id='" + GroupBox1.Tag + "'";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteReader();
SQLConn.ConnClose();
GetData(); // 1 table update karna h to yha tak ka code hata de
MessageBox.Show("Successfully updated", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information);
st2 = "Updated the class='" + txtClass.Text + "'";
cf.LogFunc(st1, System.DateTime.Now, st2);
btnUpdate.Enabled = false;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.ConnClose();
}
}
private void LoadClassCombo(ComboBox cb)
{
try
{
SQLConn.ConnOpen();
SQLConn.da = new NpgsqlDataAdapter("SELECT id, classname FROM sclass order by classname", SQLConn.conn);
DataTable dt = new DataTable();
SQLConn.da.Fill(dt);
dt.Rows.Add(-1, "All Classes");
cb.DataSource = dt;
cb.DisplayMember = "classname";
cb.ValueMember = "id";
SQLConn.ConnClose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error is", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.ConnClose();
}
}
public void FillSections()
{
try
{
SQLConn.ConnOpen();
SQLConn.sqL = "SELECT distinct sectionname FROM section ";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
while (SQLConn.dr.Read())
{
cmbSection.Items.Add(SQLConn.dr[0]);
}
SQLConn.ConnClose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.ConnClose();
}
}
private void SearchStudents(bool sname)
{
try
{
SQLConn.ConnOpen();
SQLConn.sqL = "SELECT admissionno,CONCAT(firstname, ' ' ,othername) as fullname,status , sclass,section,termname,term, admissiondate,gender,category from studentdetail LEFT JOIN schoolterm on studentdetail.term=schoolterm.id WHERE 1=1";
if (int.Parse(cmbTerm.SelectedValue.ToString()) > 0)
{
SQLConn.sqL += " AND term ='" + cmbTerm.SelectedValue.ToString() + "'";
}
if (int.Parse(cmbClass.SelectedValue.ToString()) > 0)
{
SQLConn.sqL += " AND sclass ='" + cmbClass.SelectedValue.ToString() + "'";
}
if (sname)
{
if (txtAdmissionNo.Text.Trim() != "")
{
SQLConn.sqL += " AND admissionno ILike '%" + txtAdmissionNo.Text.Trim() + "%' or othername ILIKE '%" + txtAdmissionNo.Text.Trim() + "%' or firstname ILike '%" + txtAdmissionNo.Text.Trim() + "%' ";
}
}
DGV.DataSource = null;
SQLConn.sqL += " order by admissionno";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.da = new NpgsqlDataAdapter(SQLConn.cmd);
DataTable dt = new DataTable();
SQLConn.da.Fill(dt);
DGV.DataSource = dt;
SQLConn.ConnClose();
}
catch (NpgsqlException ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
SQLConn.cmd.Dispose();
}
}
private void Update_record_Click(object sender, EventArgs e)
{
try{
SQLConn.ConnOpen();
string cb = "update subject set subjectcode=@d1, subjectname=@d2,classname=@d4 where subjectcode='" + txtSubjectCode.Text + "'";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.Parameters.Add(new NpgsqlParameter("@d1", txtSubjectCode.Text.Trim()));
SQLConn.cmd.Parameters.Add(new NpgsqlParameter("@d2", txtSubjectName.Text.Trim()));
SQLConn.cmd.Parameters.Add(new NpgsqlParameter("@d4", cmbClass.Text));
SQLConn.cmd.ExecuteNonQuery();
MessageBox.Show("Successfully updated", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information);
st2 = "Updated subject whose subjectCode is'" + txtSubjectCode.Text + "'";
cf.LogFunc(st1, System.DateTime.Now, st2);
Update_record.Enabled = false;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.ConnClose();
}
}