How to INSERT date into SQL database date column using dateTimePicker?

Question

I have a birthdate column of type Date in sql database

And in my application I use a dateTimePicker to get the birth date

But when i am trying to insert the date taken from the dateTimePicker:

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 is

Date = {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();

SQL Injection alert - you should not concatenate together your SQL statements - use parametrized queries instead to avoid SQL injection — marc_s
– marc_s, Commented Mar 21, 2015 at 11:12

marc_s · Accepted Answer · 2015-03-21 13:02:24Z

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();
}

DMVC · Accepted Answer · 2015-03-21 11:18:51Z

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")+"')";

answered Mar 21, 2015 at 11:18

DMVC

2401 gold badge2 silver badges15 bronze badges

Comments

Brian Mains · Accepted Answer · 2015-03-21 11:15:50Z

0

Try including quotes:

cmd.CommandText="INSERT INTO person (birthdate) VALUES('"+dateTimePicker.Value.Date+"')";

I'd recommend using parameters too.

answered Mar 21, 2015 at 11:15

Brian Mains

50.8k35 gold badges155 silver badges260 bronze badges

Comments

Eric Aya · Accepted Answer · 2017-08-31 12:27:25Z

0

Try this as string format:

cmd.CommandText="INSERT INTO person(birthdate)VALUES('"+dateTimePicker.Value.Date+"')";

edited Aug 31, 2017 at 12:27

Eric Aya

70.2k36 gold badges190 silver badges266 bronze badges

answered Aug 31, 2017 at 12:14

Developer

1

1 Comment

Hemal Over a year ago

You forgot a space before VALUES

0xInfection · Accepted Answer · 2019-02-08 05:56:56Z

0

dateTimePicker stores values as 1/1/1900 12:00:00 AM so you should use DATETIME if you're trying to store it since DATETIME's format is: YYYY-MM-DD HH:MI:SS.

You can print the dateTimePicker value using

MessageBox.Show(dateTimePicker.Value.ToString());

to see for yourself.

edited Feb 8, 2019 at 5:56

0xInfection

2,9901 gold badge24 silver badges36 bronze badges

answered Feb 8, 2019 at 2:51

Jonathan Acevedo

31 silver badge3 bronze badges

1 Comment

0xInfection Over a year ago

Add more info on this.

Collectives™ on Stack Overflow

How to INSERT date into SQL database date column using dateTimePicker?

5 Answers 5

Comments

Comments

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related