how to make this code work .. i want to show leftdays

Question

SqlConnection myconnection = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=c:\documents and settings\muhammad\my documents\visual studio 2010\Projects\ProjectClock\ProjectClock\ClockDB.mdf;Integrated Security=True;User Instance=True"); private void button1_Click(object sender, EventArgs e) { myconnection.Open(); SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "insert into employTable(employname,ismarred,salarydate,expiredate) values('" + textBox1.Text + "','" + checkBox1.Checked + "','" + dateTimePicker1.Value.ToString("M/d/yyyy") + "','" + dateTimePicker1.Value.AddDays(365).ToString("M/d/yyyy") + "')"; cmd.Connection = myconnection; cmd.ExecuteNonQuery(); string selectSql = "select * from employTable"; SqlCommand com = new SqlCommand(selectSql, myconnection); string first; string Second; DateTime salaryDate; DateTime expireDate; int daysleft; ////////////////this block of code is not showing the messagebox it dont't gives error /////////////////////////// using (SqlDataReader read = cmd.ExecuteReader()) { while (read.Read()) { first = (read["salarydate"].ToString()); Second = (read["expiredate"].ToString()); salaryDate = DateTime.ParseExact(first, "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture); expireDate = DateTime.ParseExact(Second, "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture); TimeSpan t1 = expireDate - salaryDate; daysleft = t1.Days; MessageBox.Show(daysleft.ToString(), "Days Left"); } /===================/ } myconnection.Close(); } }

Rafnas T P · Answer

try updating this TimeSpan t1 using (SqlDataReader read = com.ExecuteReader()) { while (read.Read()) { string first1 = read["salarydate"].ToString(); string Second1 = read["expiredate"].ToString(); t1 = Convert.ToDateTime(Second1) - DateTime.Now; int daysleft = t1.Days; /*========================Updating the daysleft Slot in DataBase ====================*/ } myconnection.Open(); SqlCommand cmd1 = new SqlCommand("UPDATE employTable2 SET '" + t1.Days + "'", myconnection); cmd.ExecuteNonQuery(); MessageBox.Show(t1.Days + " " + i.ToString(), "Days Left"); myconnection.Close(); } }

Muhammad Ali · Answer

how to remove the error ..please help .. plus how to make it update that thing perfectly ..please help : 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.Data.Sql; namespace ProjectClock { public partial class viewform : Form { public viewform() { InitializeComponent(); } private void button2_Click(object sender, EventArgs e) { settingForm ob = new settingForm(); this.Hide(); ob.Show(); } SqlConnection myconnection = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=c:\documents and settings\muhammad\my documents\visual studio 2010\Projects\ProjectClock\ProjectClock\ClockDB.mdf;Integrated Security=True;User Instance=True;"); private void button1_Click(object sender, EventArgs e) { displayData(); myconnection.Open(); SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "insert into employTable2(employname,ismarred,salarydate,expiredate) values('" + textBox1.Text + "','" + checkBox1.Checked + "','" + dateTimePicker1.Value.ToShortDateString() + "','" + dateTimePicker1.Value.AddDays(365).ToShortDateString() + "')"; cmd.Connection = myconnection; cmd.ExecuteNonQuery(); myconnection.Close(); } public void displayData() { myconnection.Open(); SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "select * from employTable2"; cmd.Connection = myconnection; cmd.ExecuteNonQuery(); myconnection.Close(); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); dataGridView1.DataSource = dt; } private void viewform_Load(object sender, EventArgs e) { displayData(); } private void button3_Click(object sender, EventArgs e) { myconnection.Open(); //displayData(); string selectSql = "select * from employTable2"; SqlCommand com = new SqlCommand(selectSql, myconnection); using (SqlDataReader read = com.ExecuteReader()) { while (read.Read()) { string first1 = read["salarydate"].ToString(); string Second1 = read["expiredate"].ToString(); TimeSpan t1 = Convert.ToDateTime(Second1) - DateTime.Now; int daysleft = t1.Days; SqlCommand cmd1 = new SqlCommand("UPDATE employTable2 SET daysleft='" + daysleft.ToString() + "'", myconnection); //OVER HERE ITS GIVES ERROR :There is already an open DataReader associated with this Command which must be closed first. cmd1.ExecuteNonQuery(); MessageBox.Show(t1.Days +"", "Days Left"); } myconnection.Close(); } } private void button4_Click(object sender, EventArgs e) { myconnection.Open(); SqlCommand cmd1 = new SqlCommand(); cmd1.CommandType = CommandType.Text; cmd1.CommandText = "update employTable2 set daysleft='hi'"; cmd1.Connection = myconnection; cmd1.ExecuteNonQuery(); myconnection.Close(); displayData(); } } }

Muhammad Ali · Answer

private void button3_Click(object sender, EventArgs e) { myconnection.Open(); // displayData(); string selectSql = "select * from employTable2"; SqlCommand com = new SqlCommand(selectSql, myconnection); // SqlDataReader read = com.ExecuteReader(); // textBox1.Text = read["salarydate"].ToString(); using (SqlDataReader read = com.ExecuteReader()) { while (read.Read()) { string first1 = read["salarydate"].ToString(); string Second1 = read["expiredate"].ToString(); TimeSpan t1 = Convert.ToDateTime(Second1) - DateTime.Now; int daysleft = t1.Days; SqlCommand cmd1 = new SqlCommand("UPDATE employTable2 SET daysleft='" + t1.Days + "'", myconnection); //OVER HERE ITS GIVES ERROR :There is already an open DataReader associated with this Command which must be closed first. cmd1.ExecuteNonQuery(); MessageBox.Show(t1.Days +"", "Days Left"); } myconnection.Close(); } }

Rafnas T P · Answer

using (SqlDataReader read = com.ExecuteReader()) { while (read.Read()) { string first1 = read["salarydate"].ToString(); string Second1 = read["expiredate"].ToString(); TimeSpan t1 = Convert.ToDateTime(Second1) - DateTime.Now; int daysleft = t1.Days; /*========================Updating the daysleft Slot in DataBase ====================*/ myconnection.Open(); SqlCommand cmd1 = new SqlCommand("UPDATE employTable2 SET '" + t1.Days + "'", myconnection); cmd.ExecuteNonQuery(); MessageBox.Show(t1.Days + " " + i.ToString(), "Days Left"); } myconnection.Close(); } } if it helps please accept this answer

Nitin Sontakke · Answer

Your update statement is not correct. In fact, the way it is, I am surprised that it is not giving an exception. The proper syntax of update statement is as follows: update table_name set column_name2 = value2 where column_name1 = value1; Modify the above query in a way that suits your requirement. Remember that it should have where clause. Otherwise all rows in the table will get updated.

Muhammad Ali · Answer

//Now i am having new Issued in the segment of code below .. the MessageBox Shows the correct Result but the Updating Query saves the all as 364 . there is not change in it ... Can you help in it .. Code is : using (SqlDataReader read = com.ExecuteReader()) { while (read.Read()) { string first1 = read["salarydate"].ToString(); string Second1 = read["expiredate"].ToString(); TimeSpan t1 = Convert.ToDateTime(Second1) - DateTime.Now; int daysleft = t1.Days; /*========================Updating the daysleft Slot in DataBase ====================*/ SqlCommand cmd1 = new SqlCommand("UPDATE employTable2 SET '" + t1.Days + "'", myconnection); MessageBox.Show(t1.Days + " " + i.ToString(), "Days Left"); } myconnection.Close(); } }

Rafnas T P · Answer

try this private void button1_Click(object sender, EventArgs e) { myconnection.Open(); SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "insert into employTable(employname,ismarred,salarydate,expiredate) values('" + textBox1.Text + "','" + checkBox1.Checked + "','" + dateTimePicker1.Value.ToString("M/d/yyyy") + "','" + dateTimePicker1.Value.AddDays(365).ToString("M/d/yyyy") + "')"; cmd.Connection = myconnection; cmd.ExecuteNonQuery(); string selectSql = "select * from employTable"; SqlCommand com = new SqlCommand(selectSql, myconnection); string first; string Second; DateTime salaryDate; DateTime expireDate; int daysleft; ////////////////this block of code is not showing the messagebox it dont't gives error /////////////////////////// using (SqlDataReader read = cmd.ExecuteReader()) { while (read.Read()) { first = (read["salarydate"].ToString()); Second = (read["expiredate"].ToString()); TimeSpan t1 = Convert.ToDateTime(Second) - Convert.ToDateTime(first) ; daysleft = t1.Days; MessageBox.Show(daysleft.ToString(), "Days Left"); } /===================/ } myconnection.Close(); } }

Nitin Sontakke · Answer

If the data type of columns is DateTime then there is no problem at all. See if you can take following steps: While inserting into database, make the format "yyyy-MM-dd". Database doesn't understand which is month and which is day. Only we understand it and only we have any meaning to it. As such the format "yyyy-MM-dd" is globally neutral. It doesn't adhere to either British or American standard. So use that. When you are getting date from database in SqlDataReader it is of DateTime time. DO NOT use .ToString() method to convert it to string. Directly store it to a local variable of DateTime data type with .GetDateTime() method. Then you should be able to do all sort of date operations on it. DO NOT convert date times to string unless you are showing them to the user. Simple.

Amit Gupta · Answer

Then you need to check the value of 'first' that you are getting. Debug your code to find the appropriate solution.

Muhammad Ali · Answer

Shows Error Over here ====> salaryDate = DateTime.ParseExact(first, "M/d/yyyy", System.Globalization.CultureInfo.InvariantCulture); error :String was not recognized as a valid DateTime. My Datatype of salarydate and expirydate is datetime ..

Bikesh Srivastava · Answer

use break point to get error and let me know.

Amit Gupta · Answer

As @Nitin sir said, it has always been a good approach to have your datatype of DateTime if you are working with dates. Debug your code, may be you have put different SqlCommand in SqlDataReader using (SqlDataReader read = cmd .ExecuteReader()) using (SqlDataReader read = com .ExecuteReader()) This might fix your issue.

Nitin Sontakke · Answer

What is the data type of salarydate and expiredate columns in database table employTable ? If it is VarChar and not DateTime then problem is starting from there. First change it DateTime (or just Date ) and then we will proceed from there.

Amit Kumar · Answer

Hi, Please add using System.Data; using System.Data.SqlClient; public partial class Contact : Page { SqlConnection myconnection = string.Empty; protected void Page_Load(object sender, EventArgs e) { myconnection = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=c:\documents and settings\muhammad\my documents\visual studio 2010\Projects\ProjectClock\ProjectClock\ClockDB.mdf;Integrated Security=True;User Instance=True"); } private void button1_Click(object sender, EventArgs e) { myconnection.Open(); SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "insert into employTable(employname,ismarred,salarydate,expiredate) values('" + textBox1.Text + "','" + checkBox1.Checked + "','" + dateTimePicker1.Value.ToString("M/d/yyyy") + "','" + dateTimePicker1.Value.AddDays(365).ToString("M/d/yyyy") + "')"; cmd.Connection = myconnection; string selectSql = "select * from employTable"; SqlCommand com = new SqlCommand(selectSql, myconnection); string first; string Second; DateTime salaryDate; DateTime expireDate; int daysleft; ////////////////this block of code is not showing the messagebox it dont't gives error /////////////////////////// using (SqlDataReader read = cmd.ExecuteReader()) { while (read.Read()) { first = (read["salarydate"].ToString()); Second = (read["expiredate"].ToString()); salaryDate = DateTime.ParseExact(first, "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture); expireDate = DateTime.ParseExact(Second, "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture); TimeSpan t1 = expireDate - salaryDate; daysleft = t1.Days; MessageBox.Show(daysleft.ToString(), "Days Left"); } } // myconnection.Close(); } }

how to make this code work .. i want to show leftdays

Answers (14)