How to INSERT date into sql db date column using dateTimePicker?

Question

5.00/5 (2 votes)

See more:

SQL-Server-2008

Hello All,

My problem is simple I have a birthdate column of type date in my sql database and in my application I use dateTimePicker to get the birthdate, but when trying to insert the date I got from the dateTimePicker I get an error Incorrect syntax near '12'

And when I try to debug the code I find that the value I get from the dateTimePicker is Date = {7/16/2011 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();

Thanks in advance
:-)

Posted 16-Jul-11 0:23am

a1mimo

Updated 25-Mar-20 5:53am

Add a Solution

4 solutions

Solution 1

As the datatype for the date you used is only Date and your datepicker value is returning Time along with date.
Either change the datatype to datetime and or pass only Date value to insert.

Posted 16-Jul-11 0:36am

m@dhu

Comments

a1mimo 16-Jul-11 6:39am

Thank You mkgoud
I just solved the problem right now and to be fair I thought it would be as you said too but it was because I forgot to put the value of the date between '' like this cmd.CommandText="INSERT INTO person (birthdate) VALUES('"+dateTimePicker.Value.Date+"')";
but still there is something I wish to know how to get only the date value from the dateTimePicker if dateTimePicker.Value.Date dont get only the date?

m@dhu 16-Jul-11 6:53am

As you said in the question it is returning '7/16/2011 12:00:00 AM'. so I came to conclusion it returns time also.

a1mimo 16-Jul-11 10:11am

yes I know and btw my vote of 4

Solution 2

I way to solve the problem would be to change the data type in the SQL DB to VARCHAR(50)

and the execute the following SQL query in your code.

con.Open();

cmd.CommandText = "INSERT INTO dbo.Person (birthdate) VALUES ('" + this.dateTimePicker.Text + "')";
cmd.Connection = con;
cmd.ExecuteNonQuery();
con.Close();

This will display the value as 16 July 2011 in your SQL DB without the time value. This can then be converted to to a short date string be using the following

string dateString = this.dateTimePicker1.Text;
DateTime Date = Convert.ToDateTime(dateString.ToString());

You can then output the date to a short date format using the following;

MessageBox.Show(Date.ToShortDateString());

Hope this helps.

EDIT
-------------------------------------------------------
Use Sql parameters to avoid SQL injection[^].

Enter Null Values for DateTime Column of SQL Server[^](Explains how to pass values using sql parameters.)

Posted 16-Jul-11 1:17am

Danny Hulmston

Updated 16-Jul-11 3:12am

v2

Comments

a1mimo 16-Jul-11 10:11am

Thank you Danny
I already discovered the problem it was the single quote I didnt put it around the date value but your solutions will work as well so my vote of 4

Solution 5

Use
DateTimePicker.value.to string("dd/MM/yyyy")
In your expression.

Posted 3-Jan-15 2:45am

Abdulrahman Obaje

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Gordon Beeming · Accepted Answer · 2011-07-16T21:43:00

you should add your date using a parameter. you snipet from above should look like below

C#

cmd.CommandText = "INSERT INTO person (birthdate) VALUES(@date)";
            cmd.Parameters.Add(new SqlParameter("@date", dateTimePicker.Value.Date));
            //con is sql connection
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();

Help With Parameters
http://g4ac.co.za/WFU4g[^]

How to INSERT date into sql db date column using dateTimePicker?

4 solutions

Solution 3

Solution 1

Solution 2

Solution 5

Add your solution here

Preview 0

How to INSERT date into sql db date column using dateTimePicker?

4 solutions

Solution 3

Solution 1

Solution 2

Solution 5

Add your solution here

Preview 0

Existing Members

...or Join us