CODEWITHSUNDEEP
c#sqlwinforms
Matchbox2093
Matchbox2093

Reputation: 986

Only Date is saved and not time in sql Database from C# winforms

I'm having an issue with saving the time into my SQL database.

The system should save time and date to the database when the user scans into the building. At the current moment it saves date but time is saved as 00:00.

I had problems converting the date to SQL during the save process, so maybe i've made a mistake in the process. I've looked at it for ages and cant see why it wouldnt save the time as well.

Any ideas? here is the code in question

  var currentdate = DateTime.Now;
                    var TimeAccess = currentdate.Date.ToString("yyyy-MM-dd HH:mm:ss");
                    SqlCommand com = new SqlCommand();
                    com.Connection = new SqlConnection(Properties.Settings.Default.BioEngineering);
                    com.Connection.Open();

                    com.CommandText = "INSERT INTO AccessHistory (DoorID,UserID,TimeAccess,Status) VALUES (@DoorID,@UserID,@TimeAccess,@Status)";

                    com.Parameters.Add("@DoorID", SqlDbType.Int).Value = DoorNumber;
                    com.Parameters.Add("@UserID", SqlDbType.Int).Value = cboUserID.Text;
                    com.Parameters.Add("@TimeAccess", SqlDbType.DateTime).Value = TimeAccess;
                    com.Parameters.Add("@Status", SqlDbType.VarChar).Value = "Successful";

Thank you

Upvotes: 0

Views: 1538

Answers (4)

user1364100
user1364100

Reputation: 

               SqlCommand com = new SqlCommand();
                com.Connection = new SqlConnection(Properties.Settings.Default.BioEngineering);
                com.Connection.Open();

                com.CommandText = "INSERT INTO AccessHistory (DoorID,UserID,TimeAccess,Status) VALUES (@DoorID,@UserID,getdate(),@Status)";

                com.Parameters.Add("@DoorID", SqlDbType.Int).Value = DoorNumber;
                com.Parameters.Add("@UserID", SqlDbType.Int).Value = cboUserID.Text;
               com.Parameters.Add("@Status", SqlDbType.VarChar).Value = "Successful";

Use Getdate()

Upvotes: 0

Rahul Nikate
Rahul Nikate

Reputation: 6337

You can directly pass DateTime.Now for command parameters and make sure database column is DateTime. That's It.

 com.Parameters.Add("@TimeAccess", SqlDbType.DateTime).Value = DateTime.Now;

You can remove below from your code as It is not required at all. 

  var currentdate = DateTime.Now;
  var TimeAccess = currentdate.Date.ToString("yyyy-MM-dd HH:mm:ss");

Upvotes: 1

Ehsan Sajjad
Ehsan Sajjad

Reputation: 62488

You should not to change its datatype and convert it to string, just pass it directly to the database if you have column in database of type date or datetime:

com.Parameters.Add("@TimeAccess", SqlDbType.DateTime).Value = currentDate;

Upvotes: 3

Robert
Robert

Reputation: 25753

Maybe DateTime instead of Date in 2nd line:

var TimeAccess = currentdate.DateTime.ToString("yyyy-MM-dd HH:mm:ss");

Upvotes: 1

Related Questions