How to INSERT date into SQL database date column using dateTimePicker?
Solution 1
What you really should do is use parameters to avoid SQL injection attacks - and it also frees you from string formatting dates - also a good thing!
//cmd is sql command
cmd.CommandText = "INSERT INTO dbo.Person(birthdate) VALUES(@Birthdate);";
cmd.Parameters.Add("@Birthdate", SqlDbType.Date).Value = dateTimePicker.Value.Date;
//con is sql connection
con.Open();
cmd.ExecuteNonQuery();
con.Close();
Also, it's a recommend best practice to put your SqlConnection
, SqlCommand
and SqlDataReader
into using(....) { .... }
blocks to ensure proper disposal:
string connectionString = ".......";
string query = "INSERT INTO dbo.Person(birthdate) VALUES(@Birthdate);";
using (SqlConnection con = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.Add("@Birthdate", SqlDbType.Date).Value = dateTimePicker.Value.Date;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
Solution 2
As mentioned before the best practice is to use parameters, but if you really need to use a TSQL statement from source you should use date in the format: yyyymmdd
cmd.CommandText="INSERT INTO person (birthdate) VALUES('"+dateTimePicker.Value.Date.ToString("yyyyMMdd")+"')";
Comments
-
Jad Chahine over 1 year
I have a birthdate column of type
Date
in sql databaseAnd in my application I use a
dateTimePicker
to get the birth dateBut when i am trying to insert the
date
taken from thedateTimePicker
:I get an error :
Incorrect syntax near '12'
And when I try to debug the code I find that the value taken from the
dateTimePicker
isDate =
{3/21/2015 12:00:00 AM}
The CODE:
//cmd is sql command cmd.CommandText="INSERT INTO person (birthdate) VALUES("+dateTimePicker.Value.Date+")"; //con is sql connection con.Open(); cmd.ExecuteNonQuery(); con.Close();
-
Hemal about 6 yearsYou forgot a space before
VALUES
-
0xInfection about 5 yearsAdd more info on this.