Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# 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
Smile | :)
Posted 16-Jul-11 1:23am
a1mimo1.7K
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

you should add your date using a parameter. you snipet from above should look like below
 
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[^]
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

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.
  Permalink  
Comments
a1mimo at 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?
mkgoud at 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 at 16-Jul-11 10:11am
   
yes I know and btw my vote of 4
Rate this: bad
good
Please Sign up or sign in to vote.

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.)
  Permalink  
v2
Comments
a1mimo at 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

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

  Print Answers RSS
0 OriginalGriff 304
1 Sergey Alexandrovich Kryukov 255
2 Maciej Los 250
3 Shweta N Mishra 216
4 PIEBALDconsult 174
0 OriginalGriff 7,660
1 Sergey Alexandrovich Kryukov 7,072
2 DamithSL 5,586
3 Manas Bhardwaj 4,946
4 Maciej Los 4,665


Advertise | Privacy | Mobile
Web01 | 2.8.1411023.1 | Last Updated 21 Oct 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100