0

I'm using this query on a wpf application:

"INSERT INTO [Table]([fname],[lname],[cdate]) VALUES(" + @fname + "," + @lname + "," + @DateTime.Now + ")"

But it doesn't seem to work, it says: Incorrect Syntax near '12'. The problem has got to be somewhere in my DateTime since when I turn my code to this:

"INSERT INTO [Table]([fname],[lname]) VALUES(" + @fname + "," + @lname + ")";

The query executes perfectly fine.

The cdate has a datetime data type in SQL, if anyone was wondering.

Devon Mccoy
  • 3
  • 2
  • Could this be a formatting error? I've outputted the DateTime.Now and it's not formatted like the DateTime in SQL. I'll do a couple of trial and errors with the formatting. – Devon Mccoy Jun 17 '17 at 16:30
  • 1
    See https://stackoverflow.com/questions/1032495/insert-datetime-value-in-sql-database-with-c-sharp – sgmoore Jun 17 '17 at 16:35

3 Answers3

0

Try below query 

  "INSERT INTO [Table]([fname],[lname],[cdate]) VALUES('" + @fname + "','" + @lname + "','" + @DateTime.Now.ToString("d") + "')"

Or
  "INSERT INTO [Table]([fname],[lname],[cdate]) VALUES('" + @fname + 

"','" + @lname + "','" + @DateTime.Now + "')"
OR
  "INSERT INTO [Table]([fname],[lname],[cdate]) VALUES('" + @fname + "','" + @lname + "','" + @DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "')"

Please check the date format also whether you are sending mm/dd/yy or dd/mm/yy and what your database accept.

Rajat Jaiswal
  • 645
  • 4
  • 15
  • Thanks, the first one worked. However, the stored date looks off, it says "1/1/1900 12:00:00 AM". – Devon Mccoy Jun 17 '17 at 16:35
  • What is the date format in your database.? Please, check whether it is storing dd/mm/yy or mm/dd/yy . You need to pass same format to database. – Rajat Jaiswal Jun 17 '17 at 16:37
  • It has a datetime data type and an example of the format is "6/17/1905 12:00:00 AM". – Devon Mccoy Jun 17 '17 at 16:43
  • I added a new query please try that – Rajat Jaiswal Jun 17 '17 at 16:48
  • You should be using Sql Parameters. Just think what happens if you have a surname like O'Reilly. – sgmoore Jun 17 '17 at 16:52
  • Unfortunately, it brings up the Incorrect Syntax again. I tried this format: M/d/yyyy h:mm:ss tt which gives me an output of "6/18/2017 12:52:34 AM" which I believe should be the correct syntax, however, it also says Incorrect Syntax. – Devon Mccoy Jun 17 '17 at 16:54
0

Try set DateTime format before your main script:

SET dateformat ymd
INSERT INTO ...
Andrey Angerchik
  • 51
  • 11
0

sgmoore was right, the use of sql parameters did the trick, I didn't even have to format it, my whole code is:

using (SqlCommand cmd = new SqlCommand("INSERT INTO [Table]([fname],[lname],[cdate]) VALUES(" + @fname + "," + @lname + ",@mydate)", Conn))
    {
    cmd.Parameters.Add(new SqlParameter("@mydate", DateTime.Now));
    Conn.Open();
    cmd.ExecuteNonQuery();
    Conn.Close();
    }
Devon Mccoy
  • 3
  • 2