CODEWITHSUNDEEP
c#sql-serverdate
Oak
Oak

Reputation: 1169

Update SQL database date column

i have SQL server database and table with column for date (date datatype). I would like to change the value of date column for all records to have todays date, but something is not working, please help. My code:

 con.Open();
 DateTime date = DateTime.Now.Date;
 string insStr = "UPDATE tblBusiness SET DateCreated = @Date;";
 SqlCommand updateDate = new SqlCommand(insStr, con);
 updateDate.Parameters.AddWithValue("Date", date);
 updateDate.ExecuteNonQuery();
 con.Close();

Thanks, Oak

EDIT: Thanks for the answers. The thing why it was not working was because of too many records in database and the default update timeout is 30sec or something. And thats why no record was changed. updateDate.CommandTimeout = 0; fixed the problem.

Upvotes: 1

Views: 3819

Answers (7)

Oak
Oak

Reputation: 1169

Thanks for the answers. The thing why it was not working was because of too many records in database and the default update timeout is 30sec or something. And thats why no record was changed. updateDate.CommandTimeout = 0; fixed the problem.

Upvotes: 0

Internet Engineer
Internet Engineer

Reputation: 2534

This should work. You don't need a where clause if you are updating all records.

con.Open();
 DateTime date = DateTime.Now.Date;
 string insStr = "UPDATE tblBusiness SET DateCreated = " + date;
 SqlCommand updateDate = new SqlCommand(insStr, con);
 updateDate.ExecuteNonQuery();
 con.Close();

Upvotes: -1

4b0
4b0

Reputation: 22323

Replace Date with @Date.

updateDate.Parameters.AddWithValue("@Date", date);

Upvotes: 0

Gratzy
Gratzy

Reputation: 9389

try

updateDate.Parameters.AddWithValue("@Date", date);

Upvotes: 1

Dennis Traub
Dennis Traub

Reputation: 51674

//Replace
updateDate.Parameters.AddWithValue("Date", date);

// with (watch the @-symbol)
updateDate.Parameters.AddWithValue("@Date", date);

Upvotes: 1

hwcverwe
hwcverwe

Reputation: 5367

use @Date instead of Date

updateDate.Parameters.AddWithValue("@Date", date);

Upvotes: 2

Arion
Arion

Reputation: 31249

I think this line:

updateDate.Parameters.AddWithValue("Date", date);

Should be:

updateDate.Parameters.AddWithValue("@Date", date);

Upvotes: 5

Related Questions