CODEWITHSUNDEEP
c#sql-server
amr kamal
amr kamal

Reputation: 58

How to update last row with the time now using C# & SQL Server

My problem is when I update my database record with the time now the time is stopped in specific value and doesn't change.

This is my code 

conn.Open();

SqlCommand sq = new SqlCommand("update Hodor_data set leaving_time = ('" + dateTimePicker1.Value.ToString() + "') where mil_no = '" +textBox1.Text+ "' and times = (select max(times) from Hodor_data )", conn);

sq.ExecuteNonQuery();

conn.Close();

and I tried to change the time by add timer refreshed the datetimepicker every one second but nothing changes and this is my result

img

Upvotes: 0

Views: 259

Answers (1)

Soner Gönül
Soner Gönül

Reputation: 98848

Let me clear a few things. You are doing so many things wrong..

First, you have a bad habits to kick as choosing the wrong data type for your data. I saw AM and PM time designators in your leaving_time and arrival_time columns. That means they are character typed columns.

Stop that!

If you save DateTime values to your database, insert them directly to your database to datetime or preferable datetime2 typed columns. Do not insert them as a string using like ToString method.

Second, you should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks. Even if it's a home project, parameterized queries are always is the best way to do it.

Third, use using statement to dispose your connection and command automatically instead of calling Close or Dispose methods anually.

using(var conn = new SqlConnection(conString))
using(var sq = conn.CreateCommand())
{
   sq.CommandText = @"update Hodor_data 
                      set leaving_time = @leaving
                      where mil_no = @milno 
                      and times = (select max(times) from Hodor_data )";

   sq.Parameters.Add("@leaving", SqlDbType.DateTime).Value = dateTimePicker1.Value;
   sq.Parameters.Add("@milno", SqlDbType.Int).Value = int.Parse(textBox1.Text);

   conn.Open();
   sq.ExecuteNonQuery();
}

Upvotes: 1

Related Questions