How to insert current date and time on a specific column in sql using C#

Question

I can't insert the login time on my database' specific column (lastlogin).

I tried to do this but it always show error.

this.Close();
Mainform mainform = new Mainform();
mainform.Show();
MessageBox.Show("Welcome!", "Validation App", 
MessageBoxButtons.OK, MessageBoxIcon.Information);

DateTime datetime = DateTime.Now;
string format = "yyyy/MM/dd, HH:mm:ss";

command = new SqlCommand("insert into 
dbo.ValidationApp(lastlogin) values(@dtime)", cnn);
command.Parameters.Clear();
command.Parameters.Add("@dtime",datetime.ToString(format));
command.ExecuteNonQuery();

The login time should be inserted on my database but it doesn't.

hi sir, like this ? command.Parameters.Add("@dtime",datetime); sorry Im just a newbie using this language. — John Rommel Unsana, Aug 30 '19 at 09:22
If you're not worried about time zone differences or potential clock disconnects between your DB server and the client, you might consider also consider just using the DB command for the current date/time, which can vary between DBMS, but `now()` is usually a winner. — Hambone, Aug 30 '19 at 20:19
i already solved my problem thank you so much for your help guys! :) — John Rommel Unsana, Nov 23 '19 at 02:12

score 4 · Accepted Answer · edited Aug 30 '19 at 15:35

4

this.Close();
Mainform mainform = new Mainform();
mainform.Show();
MessageBox.Show("Welcome!", "Validation App", 
MessageBoxButtons.OK, MessageBoxIcon.Information);

DateTime datetime = DateTime.Now;
string format = "yyyy/MM/dd, HH:mm:ss";

command = new SqlCommand("insert into 
dbo.ValidationApp(lastlogin) values(@dtime)", cnn);
// Updated in regards to OP's question
// For adding parameter:
command.Parameters.Add("@dtime", SqlDbType.DateTime).Value = DateTime.Now;
try{
   if (cnn.State == ConnectionState.Closed)
   {
     cnn.Open();
   }
   command.ExecuteNonQuery();
}
finally{
  cnn.Close();
}

As you are setting parameter only once and not in loop, you can skip the statement command.Parameters.Clear();

Suggested changes: For checking if the connection is open or closed, a check has been performed, and then the insert statement has been executed and connection closed afterwards.

The snippet above contains both the Suggested changes and what the OP requested (line 14).

edited Aug 30 '19 at 15:35

Lucca Ferri

1,308
12
23

answered Aug 30 '19 at 09:55

Gurkiran Kaur

131
2
9

Hello Gurkiran, welcome to StackOverflow! Maybe your answer could've been added in a comment instead? Or at least shown to him what you modified in the code (I can see you added .Value set, but he might not understand why and what has been modified). – Lucca Ferri Aug 30 '19 at 14:53
1

Thank you for suggestion @LuccaFerri. As I am new contributor,I am not having comment privilege on the question.But I have edited my ans. I hope I am able to provide more understanding with the added explanation. – Gurkiran Kaur Aug 30 '19 at 15:26
Don't worry about it. I have made just a few minor modifications on your post, just to be clear that the snippet contains both the suggested edit and the modification he requested. – Lucca Ferri Aug 30 '19 at 15:35

score 1 · Answer 2 · answered Aug 30 '19 at 09:29

You can do it like this:

this.Close();
Mainform mainform = new Mainform();
mainform.Show();
MessageBox.Show("Welcome!", "Validation App", 
MessageBoxButtons.OK, MessageBoxIcon.Information);

DateTime datetime = DateTime.Now;
command = new SqlCommand("insert into 
dbo.ValidationApp(lastlogin) values(@dtime)", cnn);
command.Parameters.Clear();
command.Parameters.Add("@dtime", SqlDbType.DateTime).SourceColumn = datetime.ToString();
command.ExecuteNonQuery();

Notice the line: command.Parameters.Add("@dtime", SqlDbType.DateTime).SourceColumn = datetime.ToString();

You can refer to this question in here: InsertCommand.Parameters.Add size parameter for datetime

You can also send the DateTime directly into the parameter, like this:

command.Parameters.Add("@dtime", DateTime.Now);

or

command.Parameters.Add("@dtime", datetime);

No need to format.

score -3 · Answer 3 · edited Aug 30 '19 at 09:26

this.Close();
Mainform mainform = new Mainform();
mainform.Show();
MessageBox.Show("Welcome!", "Validation App", 
MessageBoxButtons.OK, MessageBoxIcon.Information);

DateTime datetime = DateTime.Now;
string format =  DateTime.Now.ToString();
string format2=DateTime.Now.ToString("dddd , MMM dd yyyy,hh:mm:ss");

command = new SqlCommand("insert into 
dbo.ValidationApp(lastlogin) values(@dtime)", cnn);
command.Parameters.Clear();
command.Parameters.Add("@dtime",datetime.ToString(format));
command.ExecuteNonQuery();

Note: You can use One of those format...as per you concern..

How to insert current date and time on a specific column in sql using C#

3 Answers3