how to insert current datetime in the sql command
11,397
You should use a parametized query instead of concatenating strings, what you are doing is asking for an SQL Injection. Also, you should dispose commands and connections after use them in order to release memory.
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
{
using (var command = new SqlCommand("insert into sms (col1) values(@col1"))
{
command.Parameters.AddWithValue("@col1", DateTime.Now);
con.Open();
command.ExecuteNonQuery();
}
}
Author by
dotnetcoder
Updated on June 04, 2022Comments
-
dotnetcoder almost 2 years
how to insert the current time using ado.net sql command. getting error saying The "conversion of a varchar data type to a datetime data type resulted in an out-of-range value."
code
DateTime NowTime = DateTime.Now; string usecase = "manhole"; SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString); con.Open(); SqlCommand cmdInsert = new SqlCommand("insert into sms values('" + usecase + "','" + smsbody + "','" + NowTime + "')", con); try { cmdInsert.ExecuteNonQuery(); } columns updtd_date is datetime query INSERT INTO sms (usecase, sms, updtd_date) VALUES ('manhole','level is low at : 22/01/2018 15:56:20','22/01/2018 16:18:28');
-
Crowcoder about 6 yearsIf you use parameters you don't need to worry about date formatting because you are inserting an actual DateTime, not a string that has to be parsed (not to mention the prevention of sql injections).
-
-
BugFinder about 6 years<cough> SQL Injection.. parameters
-
Nisarg Shah about 6 yearsThis is a workaround, not a solution. Also, if the SQL server is in a different timezone as compared to the application server, the timestamp would be different here.
-
Jayasurya Satheesh about 6 years@NisargShah, Yes, I agree With that. Otherwise, he can pass the System Date Time in the 2nd Solutions
-
BugFinder about 6 yearsMuch better. Safety first and all that
-
Zohar Peled about 6 yearsThat's the only answer so far that actually addresses the real problems with the code in the question. +1.