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

Muhammad Ali

9y1.4k

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();

}

14 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rafnas T P9y ago
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 Ali9y ago
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 Ali9y ago
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 P9y ago
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 Sontakke9y ago
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 Ali9y ago
//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 P9y ago
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();
}
}
Amit Kumar9y ago
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();
}
}
Nitin Sontakke9y ago
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 Gupta9y ago
Then you need to check the value of 'first' that you are getting. Debug your code to find the appropriate solution.
Muhammad Ali9y ago
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 Srivastava9y ago
use break point to get error and let me know.
Amit Gupta9y ago
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 Sontakke9y ago
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.