Execution Timeout Expired.

Question

hello guyz i need your help, how can I solved my problem? attach here is the screenshot

Rijwan Ansari · Answer

HI, if error same then better apply using for each query not the single one. using (SqlConnection connection = new SqlConnection(connectionString)) { conn.Open(); // use the connection here } Also add cmd.CommandTimeout = 0;

Sachin Singh · Answer

It means your query is taking longer than the default 30 seconds, you need to optimize the query like you can use the index on necessary columns. for testing use this cmd.CommandTimeout = 120; // seconds cmd.ExecuteNonQuery(); If it solves your problem then it directly means you need to optimize the query.

Varun Setia · Answer

Increase the timeout only if your no other solution works, cmd.CommandTimeout = 360;

Rijwan Ansari · Answer

Hi, Instead of doing conection open and close better use using block as shown: using (SqlConnection connection = new SqlConnection(connectionString)) { conn.Open(); // use the connection here } This might can solve your problem and is a recommended way of using connection.

Abhijit Patil · Answer

hi , please check connection is already open, before opening add if clause to check connection and then open connection.after excution close connection. for time out increase timeout to 120 or 180 sec. cmd.CommandTimeout = 180; cmd.ExecuteNonQuery(); you can add time out parameter in connection string as well.

Varun Setia · Answer

How much time is it taking to directly execute from database ? To get the results from stored procedure.

Ahmet Taha · Answer

Its doesnt work i try to input but same display problem connection timeout

Ahmet Taha · Answer

public static SqlConnection ConnectDB() { try { SqlConnection conn = new SqlConnection(@"Data Source=DESKTOP-SGR1BV6\AHMET;Initial Catalog=StudentInformationSystemDB;Integrated Security=True"); conn.Open(); //MessageBox.Show("connection established"); return conn; // conn.Close(); } catch (Exception e) { //MessageBox.Show(e.ToString()); return null; //conn.Close(); } this is my connection string how I put connection timeout there? }

Ahmet Taha · Answer

could you give the sample Sir Ansari the sample of saving data using your given connection string? thanks in advanced

Ahmet Taha · Answer

error its doesnt work sir private void saveStudData() { using (SqlConnection connection = new SqlConnection(connectionString)) { cn.Open(); if ((txtstudlrn.Text == String.Empty) || (txtlname.Text == String.Empty) || (txtfname.Text == String.Empty) || (txtmname.Text == String.Empty) || (dtpbirth.Text == String.Empty) || (txtage.Text == String.Empty) || (txtpbirth.Text == String.Empty) || (txtcontactno.Text == String.Empty) || (cbgender.Text == String.Empty) || (cbbloodtype.Text == String.Empty) || (cbreligion.Text == String.Empty) || (txtaddress.Text == String.Empty) || (txtfathername.Text == String.Empty) || (txtmothername.Text == String.Empty) || (txtfatheroccu.Text == String.Empty) || (txtmotheroccu.Text == String.Empty) || (txtpaddress.Text == String.Empty) || (txtpcontact.Text == String.Empty) || (cbgradelevel.Text == String.Empty) || (cbsection.Text == String.Empty) || (cbStrand.Text == String.Empty) || (cbacademeicyear.Text == String.Empty) || (dtpDateofEnrolled.Text == String.Empty) || (dateofCompletion.Text == String.Empty) || (txtskulname.Text == String.Empty) || (txtskuladd.Text == String.Empty)) { MessageBox.Show("Required to Fill Missing Field", _title, MessageBoxButtons.OK, MessageBoxIcon.Information); return; } else { cn.Open(); //cn = ClassDB.ConnectDB(); cmd = new SqlCommand("select *from tblStudentRegistration where lrn='" + txtstudlrn.Text + "' and academicyear='" + cbacademeicyear.Text + "' and academicterm='" + cbacademicterm.Text + "'", cn); dr = cmd.ExecuteReader(); dr.Read(); if (dr.HasRows) { MessageBox.Show("Data Already Exist"); dr.Close(); cn.Close(); } else { dr.Close(); //cn.Open(); byte[] img = null; FileStream Fs = new FileStream(this.txtimgpath.Text, System.IO.FileMode.Open, System.IO.FileAccess.Read); BinaryReader BR = new BinaryReader(Fs); img = BR.ReadBytes((int)Fs.Length); cmd = new SqlCommand("insert into tblStudentRegistration(lrn,lastname,firstname,middlename,birthdate,age,birthplace,contactno,gender,bloodtype,religion,address,father,mother,fatheroccupation,motheroccupation,parentaddress,parentcontactno,gradelevel,section,strand,academicyear,academicterm,DateOfEnrolled,jhsdateofcompletion,schoolname,schooladdress,Photo,imgloc)values(@lrn,@lastname,@firstname,@middlename,@birthdate,@age,@birthplace,@contactno,@gender,@bloodtype,@religion,@address,@father,@mother,@fatheroccupation,@motheroccupation,@parentaddress,@parentcontactno,@gradelevel,@section,@strand,@academicyear,@academicterm,@DateOfEnrolled,@jhsdateofcompletion,@schoolname,@schooladdress,@Photo,@imgloc)", cn); // cmd.Parameters.AddWithValue("@id", _id); cmd.Parameters.AddWithValue("@lrn", txtstudlrn.Text); cmd.Parameters.AddWithValue("@lastname", txtlname.Text); cmd.Parameters.AddWithValue("@firstname", txtfname.Text); cmd.Parameters.AddWithValue("@middlename", txtmname.Text); cmd.Parameters.AddWithValue("@birthdate", Convert.ToDateTime(dtpbirth.Text)); cmd.Parameters.AddWithValue("@age", Convert.ToInt32(txtage.Text)); cmd.Parameters.AddWithValue("@birthplace", txtpbirth.Text); cmd.Parameters.AddWithValue("@contactno", txtcontactno.Text); cmd.Parameters.AddWithValue("@gender", cbgender.Text); cmd.Parameters.AddWithValue("@bloodtype", cbbloodtype.Text); //cmd.Parameters.AddWithValue("@citizenship", cbcitizenship.Text); cmd.Parameters.AddWithValue("@religion", cbreligion.Text); cmd.Parameters.AddWithValue("@address", txtaddress.Text); cmd.Parameters.AddWithValue("@father", txtfathername.Text); cmd.Parameters.AddWithValue("@mother", txtmothername.Text); cmd.Parameters.AddWithValue("@fatheroccupation", txtfatheroccu.Text); cmd.Parameters.AddWithValue("@motheroccupation", txtmotheroccu.Text); cmd.Parameters.AddWithValue("@parentaddress", txtpaddress.Text); cmd.Parameters.AddWithValue("@parentcontactno", txtpcontact.Text); cmd.Parameters.AddWithValue("@gradelevel", cbgradelevel.Text); cmd.Parameters.AddWithValue("@section", cbsection.Text); cmd.Parameters.AddWithValue("@strand", cbStrand.Text); cmd.Parameters.AddWithValue("@academicyear", cbacademeicyear.Text); cmd.Parameters.AddWithValue("@academicterm", txtacademicterm.Text); cmd.Parameters.AddWithValue("@DateOfEnrolled", dtpDateofEnrolled.Text); cmd.Parameters.AddWithValue("@jhsdateofcompletion", dateofCompletion.Text); cmd.Parameters.AddWithValue("@schoolname", txtskulname.Text); cmd.Parameters.AddWithValue("@schooladdress", txtskuladd.Text); cmd.Parameters.AddWithValue("@Photo", img); cmd.Parameters.AddWithValue("@imgloc", txtimgpath.Text); cmd.ExecuteNonQuery(); cn.Close(); MessageBox.Show("RECORD SAVED"); list2(); clear(); } }

Ahmet Taha · Answer

this is my source 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.SqlClient; using System.IO; //using System.Drawing.Imaging; namespace StudentInformationSystem { public partial class frmstudentdataentry : Form { SqlConnection cn; // SqlConnection cn1; SqlCommand cmd; SqlDataAdapter da; SqlDataReader dr; //ClassDB db = new ClassDB(); string _title = "STUDENT INFORMATION SYSTEM"; String imgloc; public frmstudentdataentry() { InitializeComponent(); //cn = new SqlConnection(); //cn.ConnectionString = db.GetConnection(); cn = ClassDB.ConnectDB(); academicyear(); showacademicyearlist(); // loadrecords(); // list(); list2(); DisplayStrand(); //getgradelevel(); displaysectionthroughbygradeandstrand(); } private void cbreligion_KeyPress(object sender, KeyPressEventArgs e) { } private void dtpbirth_ValueChanged(object sender, EventArgs e) { } private void tabPage1_Click(object sender, EventArgs e) { } private void txtlname_TextChanged(object sender, EventArgs e) { } private void cbmarital_SelectedIndexChanged(object sender, EventArgs e) { } private void cbgender_SelectedIndexChanged(object sender, EventArgs e) { } private void dtpbirth_ValueChanged_1(object sender, EventArgs e) { } private void txtcontactno_TextChanged(object sender, EventArgs e) { } private void txtmname_TextChanged(object sender, EventArgs e) { } private void txtfname_TextChanged(object sender, EventArgs e) { } private void cbcitizenship_SelectedIndexChanged(object sender, EventArgs e) { } private void label13_Click(object sender, EventArgs e) { } private void label14_Click(object sender, EventArgs e) { } private void label15_Click(object sender, EventArgs e) { } private void label16_Click(object sender, EventArgs e) { } private void label8_Click(object sender, EventArgs e) { } private void label7_Click(object sender, EventArgs e) { } private void label6_Click(object sender, EventArgs e) { } private void label5_Click(object sender, EventArgs e) { } private void cbreligion_SelectedIndexChanged(object sender, EventArgs e) { } private void comboBox3_SelectedIndexChanged(object sender, EventArgs e) { //getgradelevel(); displaysectionthroughbygradeandstrand(); } private void label2_Click(object sender, EventArgs e) { this.Close(); } private void panel1_Paint(object sender, PaintEventArgs e) { } private void frmstudentdataentry_Load(object sender, EventArgs e) { // academicyear(); //showacademicyearlist(); //list2(); } private void label31_Click(object sender, EventArgs e) { } private void dtpbirth_ValueChanged_2(object sender, EventArgs e) { txtage.Text = Years(dtpbirth.Value.Date, DateTime.Now.Date).ToString(); } int Years(DateTime start, DateTime end) { return (end.Year - start.Year - 1) + (((end.Month > start.Month) || ((end.Month == start.Month) && (end.Day >= start.Day))) ? 1 : 0); } private void btnSave_Click(object sender, EventArgs e) { saveStudData(); academicyear(); } private void saveStudData() { if ((txtstudlrn.Text == String.Empty) || (txtlname.Text ==String.Empty) || (txtfname.Text ==String.Empty) || ( txtmname.Text ==String.Empty) || (dtpbirth.Text ==String.Empty) || ( txtage.Text ==String.Empty) || ( txtpbirth.Text ==String.Empty) || (txtcontactno.Text ==String.Empty) || (cbgender.Text ==String.Empty) || (cbbloodtype.Text ==String.Empty) || (cbreligion.Text ==String.Empty) || ( txtaddress.Text ==String.Empty) || (txtfathername.Text ==String.Empty) || (txtmothername.Text ==String.Empty) || ( txtfatheroccu.Text ==String.Empty) || ( txtmotheroccu.Text ==String.Empty) || (txtpaddress.Text ==String.Empty) || (txtpcontact.Text ==String.Empty) || ( cbgradelevel.Text ==String.Empty) || (cbsection.Text ==String.Empty) || (cbStrand.Text ==String.Empty) || (cbacademeicyear.Text ==String.Empty) || ( dtpDateofEnrolled.Text ==String.Empty) || (dateofCompletion.Text ==String.Empty) || ( txtskulname.Text==String.Empty) || (txtskuladd.Text ==String.Empty)) { MessageBox.Show("Required to Fill Missing Field", _title, MessageBoxButtons.OK, MessageBoxIcon.Information); return; } else { cn.Open(); //cn = ClassDB.ConnectDB(); cmd = new SqlCommand("select *from tblStudentRegistration where lrn='" + txtstudlrn.Text + "' and academicyear='" + cbacademeicyear.Text + "' and academicterm='"+cbacademicterm.Text+"'", cn); dr = cmd.ExecuteReader(); dr.Read(); if (dr.HasRows) { MessageBox.Show("Data Already Exist"); dr.Close(); cn.Close(); } else { dr.Close(); //cn.Open(); byte[] img = null; FileStream Fs = new FileStream(this.txtimgpath.Text, System.IO.FileMode.Open, System.IO.FileAccess.Read); BinaryReader BR = new BinaryReader(Fs); img = BR.ReadBytes((int)Fs.Length); cmd = new SqlCommand("insert into tblStudentRegistration(lrn,lastname,firstname,middlename,birthdate,age,birthplace,contactno,gender,bloodtype,religion,address,father,mother,fatheroccupation,motheroccupation,parentaddress,parentcontactno,gradelevel,section,strand,academicyear,academicterm,DateOfEnrolled,jhsdateofcompletion,schoolname,schooladdress,Photo,imgloc)values(@lrn,@lastname,@firstname,@middlename,@birthdate,@age,@birthplace,@contactno,@gender,@bloodtype,@religion,@address,@father,@mother,@fatheroccupation,@motheroccupation,@parentaddress,@parentcontactno,@gradelevel,@section,@strand,@academicyear,@academicterm,@DateOfEnrolled,@jhsdateofcompletion,@schoolname,@schooladdress,@Photo,@imgloc)", cn); // cmd.Parameters.AddWithValue("@id", _id); cmd.Parameters.AddWithValue("@lrn", txtstudlrn.Text); cmd.Parameters.AddWithValue("@lastname", txtlname.Text); cmd.Parameters.AddWithValue("@firstname", txtfname.Text); cmd.Parameters.AddWithValue("@middlename", txtmname.Text); cmd.Parameters.AddWithValue("@birthdate", Convert.ToDateTime(dtpbirth.Text)); cmd.Parameters.AddWithValue("@age", Convert.ToInt32(txtage.Text)); cmd.Parameters.AddWithValue("@birthplace", txtpbirth.Text); cmd.Parameters.AddWithValue("@contactno", txtcontactno.Text); cmd.Parameters.AddWithValue("@gender", cbgender.Text); cmd.Parameters.AddWithValue("@bloodtype", cbbloodtype.Text); //cmd.Parameters.AddWithValue("@citizenship", cbcitizenship.Text); cmd.Parameters.AddWithValue("@religion", cbreligion.Text); cmd.Parameters.AddWithValue("@address", txtaddress.Text); cmd.Parameters.AddWithValue("@father", txtfathername.Text); cmd.Parameters.AddWithValue("@mother", txtmothername.Text); cmd.Parameters.AddWithValue("@fatheroccupation", txtfatheroccu.Text); cmd.Parameters.AddWithValue("@motheroccupation", txtmotheroccu.Text); cmd.Parameters.AddWithValue("@parentaddress", txtpaddress.Text); cmd.Parameters.AddWithValue("@parentcontactno", txtpcontact.Text); cmd.Parameters.AddWithValue("@gradelevel", cbgradelevel.Text); cmd.Parameters.AddWithValue("@section", cbsection.Text); cmd.Parameters.AddWithValue("@strand", cbStrand.Text); cmd.Parameters.AddWithValue("@academicyear", cbacademeicyear.Text); cmd.Parameters.AddWithValue("@academicterm", txtacademicterm.Text); cmd.Parameters.AddWithValue("@DateOfEnrolled", dtpDateofEnrolled.Text); cmd.Parameters.AddWithValue("@jhsdateofcompletion", dateofCompletion.Text); cmd.Parameters.AddWithValue("@schoolname", txtskulname.Text); cmd.Parameters.AddWithValue("@schooladdress", txtskuladd.Text); cmd.Parameters.AddWithValue("@Photo", img); cmd.Parameters.AddWithValue("@imgloc", txtimgpath.Text); cmd.ExecuteNonQuery(); cn.Close(); MessageBox.Show("RECORD SAVED"); list2(); clear(); } } } //private void search() //{ // try // { // conn = Constring.connection(); // cmd = new SqlCommand("Select * from tbladdstud where studid='" + txtid.Text + "'", conn); // dr = cmd.ExecuteReader(); // if (dr.Read()) // { // txtid.Text = dr[0].ToString(); // txtfname.Text = dr[1].ToString(); // dateTimePicker1.Text = dr[2].ToString(); // textBox3.Text = dr[3].ToString(); // comboBox1.Text = dr[4].ToString(); // comboBox2.Text = dr[5].ToString(); // textBox4.Text = dr[7].ToString(); // byte[] photo_array = (byte[])dr["Photo"]; // MemoryStream ms = new MemoryStream(photo_array); // pictureBox1.Image = Image.FromStream(ms); // } // else // { // MessageBox.Show("no record has been found"); // } // } // catch // { // } //} //private void image() //{ // //try // //{ // conn = Constring.connection(); // SqlDataAdapter sda = new SqlDataAdapter("select *from tbladdstud where studid='" + txtid.Text + "'", conn); // DataTable dt = new DataTable(); // sda.Fill(dt); // byte[] MyData = new byte[0]; // MyData = (byte[])dt.Rows[0][7]; // MemoryStream str = new MemoryStream(MyData); // pictureBox1.Image = Image.FromStream(str); // //} // //catch // //{ // textBox4.Text = "photo.jpg"; // pictureBox1.Image = studreg.Properties.Resources.ninja; // //} //} private void clear() { txtstudlrn.Text = ""; txtlname.Text = ""; txtfname.Text = ""; txtmname.Text = ""; dtpbirth.Text = ""; txtage.Text = ""; txtpbirth.Text = ""; txtcontactno.Text = ""; cbgender.Text = ""; cbbloodtype.Text = ""; cbcitizenship.Text = ""; cbreligion.Text = ""; txtaddress.Text = ""; txtfathername.Text = ""; txtmothername.Text = ""; txtfatheroccu.Text = ""; txtmotheroccu.Text = ""; txtpaddress.Text = ""; txtpcontact.Text = ""; cbgradelevel.Text = "--Select--"; cbsection.Text = "--Select--"; cbStrand.Text = "--Select--"; academicyear(); dtpDateofEnrolled.Text = ""; dateofCompletion.Text = ""; txtskulname.Text = ""; txtskuladd.Text = ""; txtimgpath.Clear(); pictureBox1.Image = null; // pictureBox1.Image = enrollment_system.Properties.Resources.colandlogo; } private void btnsearch_Click(object sender, EventArgs e) { } private void searchstudent() { //cn = ClassDB.ConnectDB(); cn.Open(); var xsearch = new SqlCommand("Select* from tblNewStudentEntry where lrn like'" + txtsearchstudname.Text + "%' or lname Like '%" + txtsearchstudname.Text + "%'", cn); dr = xsearch.ExecuteReader(); if (dr.Read()) { dr.Close(); da = new SqlDataAdapter("Select* from tblStudentRegistration where lrn like'" + txtsearchstudname.Text + "%' or lastname Like '%" + txtsearchstudname.Text + "%'", cn); SqlCommandBuilder bui = new SqlCommandBuilder(da); DataTable dt = new DataTable(); da.Fill(dt); dataGridView1.Rows.Clear(); foreach (DataRow item in dt.Rows) { int n = dataGridView1.Rows.Add(); dataGridView1.Rows[n].Cells[0].Value = item["academicyear"].ToString(); dataGridView1.Rows[n].Cells[1].Value = item["academicterm"].ToString(); dataGridView1.Rows[n].Cells[2].Value = item["gradelevel"].ToString(); dataGridView1.Rows[n].Cells[3].Value = item["strand"].ToString(); dataGridView1.Rows[n].Cells[4].Value = item["section"].ToString(); dataGridView1.Rows[n].Cells[5].Value = item["lrn"].ToString(); dataGridView1.Rows[n].Cells[6].Value = item["Photo"]; dataGridView1.Rows[n].Cells[7].Value = item["lastname"].ToString(); dataGridView1.Rows[n].Cells[8].Value = item["firstname"].ToString(); dataGridView1.Rows[n].Cells[9].Value = item["middlename"].ToString(); dataGridView1.Rows[n].Cells[10].Value = DateTime.Parse(item["birthdate"].ToString()).ToShortDateString(); dataGridView1.Rows[n].Cells[11].Value = item["age"].ToString(); dataGridView1.Rows[n].Cells[12].Value = item["birthplace"].ToString(); dataGridView1.Rows[n].Cells[13].Value = item["contactno"].ToString(); dataGridView1.Rows[n].Cells[14].Value = item["gender"].ToString(); dataGridView1.Rows[n].Cells[15].Value = item["imgloc"].ToString(); dataGridView1.Rows[n].Cells[17].Value = item["bloodtype"].ToString(); // dataGridView1.Rows[n].Cells[18].Value = item["citizenship"].ToString(); dataGridView1.Rows[n].Cells[19].Value = item["religion"].ToString(); dataGridView1.Rows[n].Cells[20].Value = item["address"].ToString(); dataGridView1.Rows[n].Cells[21].Value = item["father"].ToString(); dataGridView1.Rows[n].Cells[22].Value = item["mother"].ToString(); dataGridView1.Rows[n].Cells[23].Value = item["fatheroccupation"].ToString(); dataGridView1.Rows[n].Cells[24].Value = item["motheroccupation"].ToString(); dataGridView1.Rows[n].Cells[25].Value = item["parentaddress"].ToString(); dataGridView1.Rows[n].Cells[26].Value = item["parentcontactno"].ToString(); dataGridView1.Rows[n].Cells[27].Value = item["DateOfEnrolled"].ToString(); dataGridView1.Rows[n].Cells[28].Value = item["jhsdateofcompletion"].ToString(); dataGridView1.Rows[n].Cells[29].Value = item["schoolname"].ToString(); dataGridView1.Rows[n].Cells[30].Value = item["schooladdress"].ToString(); } } } private void txtstudlrn_KeyPress(object sender, KeyPressEventArgs e) { if (e.KeyChar == 46) { //accept . character } else if (e.KeyChar == 8) { //accept backspace } else if ((e.KeyChar 57))//ascii code 48-57 between 0-9 { e.Handled = true; } } private void txtcontactno_TextChanged_1(object sender, EventArgs e) { } private void txtcontactno_KeyPress(object sender, KeyPressEventArgs e) { if (e.KeyChar == 46) { //accept . character } else if (e.KeyChar == 8) { //accept backspace } else if ((e.KeyChar 57))//ascii code 48-57 between 0-9 { e.Handled = true; } } private void txtpcontact_KeyPress(object sender, KeyPressEventArgs e) { if (e.KeyChar == 46) { //accept . character } else if (e.KeyChar == 8) { //accept backspace } else if ((e.KeyChar 57))//ascii code 48-57 between 0-9 { e.Handled = true; } } private void btnattachphoto_Click(object sender, EventArgs e) { OpenFileDialog dlg = new OpenFileDialog(); dlg.Filter = dlg.Filter = "JPG Files(*.jpg)|*.jpg|GIF files(*.gif)|*.gif|PNG Files(*.png)|*.png|All Files(*.*)|*.*"; dlg.Title = "select picture"; if (dlg.ShowDialog() == DialogResult.OK) { imgloc = dlg.FileName.ToString(); pictureBox1.ImageLocation = imgloc; txtimgpath.Text = imgloc; } } private void cbacademicyr_SelectedIndexChanged(object sender, EventArgs e) { //cbacademicyr.Items.Clear(); academicyear(); } public void academicyear() { //==================================== //================================== // cn.Open(); //cn = ClassDB.ConnectDB(); // cmd = new SqlCommand("select aycode from tblAY where status like 'OPEN'", cn); // dr = cmd.ExecuteReader(); // while (dr.Read()) // { // cbacademeicyear.Text= dr[0].ToString(); // txtacademicterm.Text=dr[3].ToString(); // } // dr.Close(); // cn.Close(); //=========================== cn = ClassDB.ConnectDB(); //cn.Open(); cmd = new SqlCommand("select academicyear,term from tblAY where status like 'OPEN'", cn); cmd.Parameters.AddWithValue("@academicyear", txtacademicyear.Text); dr = cmd.ExecuteReader(); dr.Read(); if (dr.HasRows) { txtacademicyear.Text= dr["academicyear"].ToString(); txtacademicterm.Text = dr["term"].ToString(); } else { //cbgradelevel.Text = ""; //cbsection.Text = ""; } dr.Close(); cn.Close(); } public void DisplayStrand() { cbStrand.Items.Clear(); //cn.Open(); cn = ClassDB.ConnectDB(); //cn.Open(); cmd = new SqlCommand("select *from tblstrand ", cn); dr = cmd.ExecuteReader(); cbStrand.Items.Clear(); cbStrand.Items.Add("N/A"); while (dr.Read()) { cbStrand.Items.Add(dr["strand"].ToString()); //dr.Close(); //cn.Close(); } //dr.Close(); cn.Close(); //==== //cbStrand.Items.Clear(); //cn.Open(); //cmd = new SqlCommand("select *from tblstrand", cn); //dr = cmd.ExecuteReader(); //cbStrand.Items.Clear(); //cbStrand.Items.Add("N/A"); //while (dr.Read()) //{ // cbStrand.Items.Add(dr["strand"].ToString()); //} //dr.Close(); //cn.Close(); } //public void gradelevelshowbyStrand() //{ // cn.Open(); // cm = new SqlCommand("select *from tblsection where strand=@strand ", cn); // cm.Parameters.AddWithValue("@strand", cbstrand.Text); // dr = cm.ExecuteReader(); // dr.Read(); // if (dr.HasRows) // { // cbgradelevel.Text = dr["grade"].ToString(); // cbsection.Text = dr["section"].ToString(); // } // else // { // cbgradelevel.Text = ""; // cbsection.Text = ""; // } // dr.Close(); // cn.Close(); //} private void panel2_Paint(object sender, PaintEventArgs e) { } private void cbacademicyr1_SelectedIndexChanged(object sender, EventArgs e) { } private void showacademicyearlist() { //==============================ok //cn.Open(); //string sql = "select *from tblAY"; //SqlCommand cmd = new SqlCommand(sql, cn); //===================================== cn.Open(); //cn = ClassDB.ConnectDB(); cmd = new SqlCommand("select *from tblAY", cn); //try //{ //conn.Open(); dr = cmd.ExecuteReader(); while (dr.Read()) { //================ok //string sname = dr.GetString(0); //cbacademicyr1.Items.Add(sname); //================ //cbacademicyr1.Items.Add(dr["aycode"].ToString()); } dr.Close(); cn.Close(); //} //catch (Exception ex) //{ // MessageBox.Show(ex.Message); //} } private void cbacademeicyear_SelectedIndexChanged(object sender, EventArgs e) { } private void btnupdate_Click(object sender, EventArgs e) { updatestuddata(); } public void loadrecords() { int i = 0; dataGridView1.Rows.Clear(); //================= //string address; //address = txtaddress.Text; //================= cn.Open(); //cn = ClassDB.ConnectDB(); cmd = new SqlCommand("select *from tblNewStudentEntry where status like 'Active'", cn); dr = cmd.ExecuteReader(); while (dr.Read()) { i++; dataGridView1.Rows.Add(i, dr["academicyear"].ToString(), dr["currentglevel"].ToString(), dr["strand"].ToString(), dr["section"].ToString(), dr["lrn"].ToString(), Bitmap.FromFile(dr["Photo"].ToString()), dr["lname"].ToString(), dr["fname"].ToString(), dr["mname"].ToString(), DateTime.Parse(dr["bdate"].ToString()).ToShortDateString(), Convert.ToInt32(dr["age"].ToString()), dr["studcontactno"].ToString(), dr[""].ToString(), dr["gender"].ToString(), dr["imgloc"].ToString()); //==================== // txtaddress.Text = dr["academicyear"].ToString(); //======================= dr.Close(); cn.Close(); ////cn.Open(); //SqlDataAdapter sda = new SqlDataAdapter("select Photo from tblNewStudentEntry ", cn); //DataTable dt = new DataTable(); //sda.Fill(dt); //byte[] MyData = new byte[0]; //MyData = (byte[])dt.Rows[0][14]; //MemoryStream str = new MemoryStream(MyData); //pictureBox1.Image = Image.FromStream(str); //dr.Close(); //cn.Close(); //========================= //cn.Open(); //string img = cmd.ExecuteScalar().ToString(); //pictureBox1.Image = Image.FromFile(img); //cn.Close(); //================== } dr.Close(); cn.Close(); } void list() { cn.Open(); // cn = ClassDB.ConnectDB(); //gridload SqlDataAdapter adap = new SqlDataAdapter("select *from tblStudentRegistration where status like 'ACTIVE'", cn); DataTable dt = new DataTable(); adap.Fill(dt); //lastline dataGridView1.Rows.Clear(); foreach (DataRow item in dt.Rows) { int n = dataGridView1.Rows.Add(); dataGridView1.Rows[n].Cells[0].Value = item["academicyear"].ToString(); dataGridView1.Rows[n].Cells[1].Value = item["gradelevel"].ToString(); dataGridView1.Rows[n].Cells[2].Value = item["strand"].ToString(); dataGridView1.Rows[n].Cells[3].Value = item["section"].ToString(); dataGridView1.Rows[n].Cells[4].Value = item["lrn"].ToString(); dataGridView1.Rows[n].Cells[5].Value = item["Photo"]; dataGridView1.Rows[n].Cells[6].Value = item["lastname"].ToString(); dataGridView1.Rows[n].Cells[7].Value = item["firstname"].ToString(); dataGridView1.Rows[n].Cells[8].Value = item["middlename"].ToString(); dataGridView1.Rows[n].Cells[9].Value = DateTime.Parse(item["birthdate"].ToString()).ToShortDateString(); dataGridView1.Rows[n].Cells[10].Value = item["age"].ToString(); dataGridView1.Rows[n].Cells[11].Value = item["birthplace"].ToString(); dataGridView1.Rows[n].Cells[12].Value = item["contactno"].ToString(); dataGridView1.Rows[n].Cells[13].Value = item["gender"].ToString(); dataGridView1.Rows[n].Cells[14].Value = item["imgloc"].ToString(); } } private void list2() { // cn = ClassDB.ConnectDB(); cn.Open(); //gridload SqlDataAdapter adap = new SqlDataAdapter("select *from tblStudentRegistration where status like 'ACTIVE'", cn); DataTable dt = new DataTable(); adap.Fill(dt); //lastline dataGridView1.Rows.Clear(); foreach (DataRow item in dt.Rows) { int n = dataGridView1.Rows.Add(); dataGridView1.Rows[n].Cells[0].Value = item["academicyear"].ToString(); dataGridView1.Rows[n].Cells[1].Value = item["academicterm"].ToString(); dataGridView1.Rows[n].Cells[2].Value = item["gradelevel"].ToString(); dataGridView1.Rows[n].Cells[3].Value = item["strand"].ToString(); dataGridView1.Rows[n].Cells[4].Value = item["section"].ToString(); dataGridView1.Rows[n].Cells[5].Value = item["lrn"].ToString(); dataGridView1.Rows[n].Cells[6].Value = item["Photo"]; dataGridView1.Rows[n].Cells[7].Value = item["lastname"].ToString(); dataGridView1.Rows[n].Cells[8].Value = item["firstname"].ToString(); dataGridView1.Rows[n].Cells[9].Value = item["middlename"].ToString(); dataGridView1.Rows[n].Cells[10].Value = DateTime.Parse(item["birthdate"].ToString()).ToShortDateString(); dataGridView1.Rows[n].Cells[11].Value = item["age"].ToString(); dataGridView1.Rows[n].Cells[12].Value = item["birthplace"].ToString(); dataGridView1.Rows[n].Cells[13].Value = item["contactno"].ToString(); dataGridView1.Rows[n].Cells[14].Value = item["gender"].ToString(); dataGridView1.Rows[n].Cells[15].Value = item["imgloc"].ToString(); dataGridView1.Rows[n].Cells[17].Value = item["bloodtype"].ToString(); // dataGridView1.Rows[n].Cells[18].Value = item["citizenship"].ToString(); dataGridView1.Rows[n].Cells[19].Value = item["religion"].ToString(); dataGridView1.Rows[n].Cells[20].Value = item["address"].ToString(); dataGridView1.Rows[n].Cells[21].Value = item["father"].ToString(); dataGridView1.Rows[n].Cells[22].Value = item["mother"].ToString(); dataGridView1.Rows[n].Cells[23].Value = item["fatheroccupation"].ToString(); dataGridView1.Rows[n].Cells[24].Value = item["motheroccupation"].ToString(); dataGridView1.Rows[n].Cells[25].Value = item["parentaddress"].ToString(); dataGridView1.Rows[n].Cells[26].Value = item["parentcontactno"].ToString(); dataGridView1.Rows[n].Cells[27].Value = item["DateOfEnrolled"].ToString(); dataGridView1.Rows[n].Cells[28].Value = item["jhsdateofcompletion"].ToString(); dataGridView1.Rows[n].Cells[29].Value = item["schoolname"].ToString(); dataGridView1.Rows[n].Cells[30].Value = item["schooladdress"].ToString(); cn.Close(); } cn.Close(); } private void updatestuddata() { //cn.Open(); cn = ClassDB.ConnectDB(); byte[] img = null; FileStream Fs = new FileStream(this.txtimgpath.Text, System.IO.FileMode.Open, System.IO.FileAccess.Read); BinaryReader BR = new BinaryReader(Fs); img = BR.ReadBytes((int)Fs.Length); cmd = new SqlCommand("update tblStudentRegistration set lastname=@lastname,firstname=@firstname,middlename=@middlename,birthdate=@birthdate, age=@age,birthplace=@birthplace,contactno=@contactno,gender=@gender,bloodtype=@bloodtype,religion=@religion,address=@address,father=@father,mother=@mother,fatheroccupation=@fatheroccupation,motheroccupation=@motheroccupation,parentaddress=@parentaddress,parentcontactno=@parentcontactno,gradelevel=@gradelevel,section=@section,strand=@strand,academicyear=@academicyear,jhsdateofcompletion=@jhsdateofcompletion,schoolname=@schoolname,schooladdress=@schooladdress,Photo=@Photo,imgloc=@imgloc where lrn=@lrn", cn); cmd.Parameters.AddWithValue("@lrn", txtstudlrn.Text); cmd.Parameters.AddWithValue("@lastname", txtlname.Text); cmd.Parameters.AddWithValue("@firstname", txtfname.Text); cmd.Parameters.AddWithValue("@middlename", txtmname.Text); cmd.Parameters.AddWithValue("@birthdate", Convert.ToDateTime(dtpbirth.Text)); cmd.Parameters.AddWithValue("@age", Convert.ToInt32(txtage.Text)); cmd.Parameters.AddWithValue("@birthplace", txtpbirth.Text); cmd.Parameters.AddWithValue("@contactno", txtcontactno.Text); cmd.Parameters.AddWithValue("@gender", cbgender.Text); cmd.Parameters.AddWithValue("@bloodtype", cbbloodtype.Text); //cmd.Parameters.AddWithValue("@citizenship", cbcitizenship.Text); cmd.Parameters.AddWithValue("@religion", cbreligion.Text); cmd.Parameters.AddWithValue("@address", txtaddress.Text); cmd.Parameters.AddWithValue("@father", txtfathername.Text); cmd.Parameters.AddWithValue("@mother", txtmothername.Text); cmd.Parameters.AddWithValue("@fatheroccupation", txtfatheroccu.Text); cmd.Parameters.AddWithValue("@motheroccupation", txtmotheroccu.Text); cmd.Parameters.AddWithValue("@parentaddress", txtpaddress.Text); cmd.Parameters.AddWithValue("@parentcontactno", txtpcontact.Text); cmd.Parameters.AddWithValue("@gradelevel", cbgradelevel.Text); cmd.Parameters.AddWithValue("@section", cbsection.Text); cmd.Parameters.AddWithValue("@strand", cbStrand.Text); cmd.Parameters.AddWithValue("@academicyear", cbacademeicyear.Text); cmd.Parameters.AddWithValue("@academicterm", txtacademicterm.Text); cmd.Parameters.AddWithValue("@DateOfEnrolled", dtpDateofEnrolled.Text); cmd.Parameters.AddWithValue("@jhsdateofcompletion", dateofCompletion.Text); cmd.Parameters.AddWithValue("@schoolname", txtskulname.Text); cmd.Parameters.AddWithValue("@schooladdress", txtskuladd.Text); cmd.Parameters.AddWithValue("@Photo", img); cmd.Parameters.AddWithValue("@imgloc", txtimgpath.Text); cmd.ExecuteNonQuery(); //cmd.ExecuteScalar(); // cmd.Dispose(); cn.Close(); MessageBox.Show("RECORD HAS BEEN SUCESSFULLY UPDATED", _title, MessageBoxButtons.OK, MessageBoxIcon.Information); list2(); // cmd.Dispose(); //cn.Close(); clear(); } private void cbsection1_SelectedIndexChanged(object sender, EventArgs e) { } public void getgradelevel() { cbsection.Items.Clear(); cn = ClassDB.ConnectDB(); cmd = new SqlCommand("select *from tblsection where strand=@strand and gradelevel=@gradelevel ", cn); cmd.Parameters.AddWithValue("@strand", cbStrand.Text); cmd.Parameters.AddWithValue("@gradelevel", cbgradelevel.Text); dr = cmd.ExecuteReader(); dr.Read(); if (dr.HasRows) { // cbstrand.Items.Add(dr["strand"].ToString()); //=============== cbsection.Items.Add(dr["section"].ToString()); } else { cbsection.Text = ""; } dr.Close(); cn.Close(); } private void cbgradelevel_SelectedIndexChanged(object sender, EventArgs e) { // getgradelevel(); displaysectionthroughbygradeandstrand(); } public void displaysectionthroughbygradeandstrand() { cn = ClassDB.ConnectDB(); SqlDataAdapter sda = new SqlDataAdapter("select section from tblsection where gradelevel='" + cbgradelevel.Text + "' and strand='" + cbStrand.Text + "'", cn); DataTable dt = new DataTable(); sda.Fill(dt); cbsection.Items.Clear(); foreach (DataRow ad in dt.Rows) { cbsection.Items.Add(ad["section"].ToString()); } } private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e) { string colName = dataGridView1.Columns[e.ColumnIndex].Name; if (colName == "colEdit") { if (e.RowIndex >= 0) { DataGridViewRow row = this.dataGridView1.Rows[e.RowIndex]; image2(); //================= cbacademeicyear.Text = row.Cells[0].Value.ToString(); txtacademicterm.Text = row.Cells[1].Value.ToString(); cbgradelevel.Text= row.Cells[2].Value.ToString(); cbStrand.Text= row.Cells[3].Value.ToString(); cbsection.Text = row.Cells[4].Value.ToString(); txtstudlrn.Text = row.Cells[5].Value.ToString(); txtlname.Text = row.Cells[7].Value.ToString(); txtfname.Text= row.Cells[8].Value.ToString(); txtmname.Text = row.Cells[9].Value.ToString(); dtpbirth.Text = row.Cells[10].Value.ToString(); txtage.Text = row.Cells[11].Value.ToString(); txtpbirth.Text = row.Cells[12].Value.ToString(); txtcontactno.Text = row.Cells[13].Value.ToString(); cbgender.Text = row.Cells[14].Value.ToString(); txtimgpath.Text = row.Cells[15].Value.ToString(); cbbloodtype.Text = row.Cells[17].Value.ToString(); //=============== //cbcitizenship.Text = row.Cells[18].Value.ToString(); cbreligion.Text = row.Cells[19].Value.ToString(); txtaddress.Text = row.Cells[20].Value.ToString(); txtfathername.Text = row.Cells[21].Value.ToString(); txtmothername.Text = row.Cells[22].Value.ToString(); txtfatheroccu.Text = row.Cells[23].Value.ToString(); txtmotheroccu.Text = row.Cells[24].Value.ToString(); txtpaddress.Text = row.Cells[25].Value.ToString(); txtpcontact.Text = row.Cells[26].Value.ToString(); dtpDateofEnrolled.Text = row.Cells[27].Value.ToString(); dateofCompletion.Text = row.Cells[28].Value.ToString(); txtskulname.Text = row.Cells[29].Value.ToString(); txtskuladd.Text = row.Cells[30].Value.ToString(); } } //else if (colName == "colArchive") //{ // if (MessageBox.Show("Are you sure to delete this record?", _title, MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == DialogResult.Yes) // { // cn = ClassDB.ConnectDB(); // cmd = new SqlCommand("delete from tblTeacherRegistration where teacheridno like '" + dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString() + "'", cn); // cmd.ExecuteNonQuery(); // MessageBox.Show("Succesfully deleted"); // list(); // clear(); // cn.Close(); // } //} } private void image2() { cn = ClassDB.ConnectDB(); SqlCommand cmd = new SqlCommand("select Photo from tblStudentRegistration where lrn = '" + txtstudlrn.Text + "'", cn); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); SqlDataReader reader = cmd.ExecuteReader(); if (reader.Read()) { MemoryStream ms = new MemoryStream((byte[])reader["Photo"]); //pics.Image = new Bitmap(ms); pictureBox1.Image = new Bitmap(ms); } else { //pics.Image = Properties.Resources.index; // pictureBox1.Image = Properties.Resources.ninja; } } private void txtsearchstudname_TextChanged(object sender, EventArgs e) { searchstudent(); } private void btncancel_Click(object sender, EventArgs e) { clear(); } private void panel3_Paint(object sender, PaintEventArgs e) { } } }