I want to update the leftdays, Row by Row .not all at once.

Muhammad Ali

I want to Update the Leftdays Column Row by Row but the Statement which i am using for Update .its updates the whole Column with same value .. as datareader loop calculates the different value while looping ... how i can make work properly ..please comment the Code ..

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;

SqlConnection myconnection1 = 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;");

myconnection1.Open();

SqlCommand cmd1 = new SqlCommand("UPDATE employTable2 SET daysleft='" + daysleft.ToString() + "'", myconnection1);

cmd1.ExecuteNonQuery();

myconnection1.Close();

MessageBox.Show(t1.Days +"", "Days Left");

}

myconnection.Close();

}

13 Replies

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

Nilesh Shah9y ago
I advise to move the code from C# to SQL Server stored procedure

Any logic to deal with SQL server should be in SP only for better performance.
0
Rafnas T P9y ago
try below update statement
SqlCommand cmd1 = new SqlCommand("UPDATE employTable2 SET daysleft='" + daysleft.ToString() + "' where empid = '"+read["empid"].ToString()+"'", myconnection1);

change red field to your employee id field in employTable2
0
Rafnas T P9y ago
you can give any unique column name in where conditiion to update
0
Bikesh Srivastava9y ago
Hi,
I think you are doing good ,but you need to change some little bit of code,
And do one thing for next time,Please try to post code in code block ,because its difficult to understand for Experts.
0
Amit Gupta9y ago
@Muhammad
You can use any column but it must be unique (either an identity column or primary key column) then only you can update your rows one by one.

If such column doesnot exists in your database, then it is highly recommended to create so.

It would be helpful if you share your database structure, so that we can assist you better to resolve your problem.

Hope you understand !!
0
Muhammad Ali9y ago
I didn't created ... the employ id
0
Nitin Sontakke9y ago
Really don't know how to explain this to you.

Everything you have done is right. It's just that your update statement is missing a where clause. That it.

You have used select * so I don't know the column name where you are holding employeeId.

SqlCommand cmd1 = new SqlCommand("UPDATE employTable2 SET daysleft = " + daysleft.ToString() + " WHERE employeeId = " + employeeId.ToString(), myconnection1);
0
Muhammad Ali9y ago
That What i am Asking that what would be Condition that will update the Column one by one Row from start to end .. .. automatically ... its like a calculating the value and put in the exact same row ...
0
Rafnas T P9y ago
can you send me the table structure
0
Rafnas T P9y ago
rewrite your sql command with where condition
0
Nitin Sontakke9y ago
This is precisely what I had posted to your identical question on another thread

http://www.c-sharpcorner.com/forums/how-to-make-this-code-work-i-want-to-show-leftdays

Your update statement is missing a WHERE clause.
0
Hanif Hefaz9y ago
Exactly, Follow @Amit Gupta .
0
Amit Gupta9y ago
Use where clause in your query
SqlCommand cmd1 = new SqlCommand("UPDATE employTable2 SET daysleft='" + daysleft.ToString() + "' where " +your condition, myconnection1);

// command for update is
Update tablename set columnname = 'xyz' where columnname =    'xyz'
+2