C# inserting datetime failure

Question

I've got a database with a table called Fio_FinalSched which in turn has a column named FinalDate with type smalldatetime. I convert the date string to a DateTime type by:

DateTime theDate = Convert.ToDateTime("2010-01-05 23:50:00");

I then create the command string by:

string testCommand = "INSERT INTO Fio_FinalSched (FinalDate) Values ("+theDate+")";

Then, turn it into an SQL command:

SqlCommand myCommand = new SqlCommand(testCommand,conn);

where conn is the db connection. Finally, I execute the command:

myCommand.ExecuteNonQuery();

When I run this, it gets to the execution line, and then give me the error:

Incorrect syntax near '11'.

I've tried altering the format of my date string several ways, to no avail. Is it because my database is wanting a smalldatetime type and not a datetime type? C# doesn't seem to have a smalldatetime type that I can use. Any insight would be appreciated!

score 7 · Accepted Answer · answered Jul 12 '11 at 21:09

You need to wrap the date in a SQL string literal using single quotes...

string testCommand = "INSERT INTO Fio_FinalSched (FinalDate) Values ('"+theDate+"')";

Or use Parameters..

string testCommand = "INSERT INTO Fio_FinalSched (FinalDate) Values (@myDate)";
cmd.Parameters.Add(new SqlParameter("@myDate", theDate));

score 3 · Answer 2 · answered Jul 12 '11 at 21:09

3

Try putting single quotes around the date in the string...

string testCommand = "INSERT INTO Fio_FinalSched (FinalDate) Values ('"+theDate+"')";

answered Jul 12 '11 at 21:09

Jon Erickson

112,242
44
136
174

score 2 · Answer 3 · answered Jul 12 '11 at 21:10

Working with DateTime type and several others in DataBase its strongly recomended using @Parameters, and all convertions, if you need, like: timezone, formatting or whatever do on client side.

Also because, as you mantioned in your post, the column is of smalldatetime type and not string.

In short try to use @Parameters and most likely you will succeed.

Regards.

score 1 · Answer 4 · answered Jul 12 '11 at 21:09

1

Use parameters instead of string, for example SqlParameterCollection.AddWithValue method.

answered Jul 12 '11 at 21:09

Kirill Polishchuk

54,804
11
122
125

score 1 · Answer 5 · answered Jul 12 '11 at 21:10

1

start by putting single quotes around the date.

e.g.

('"+theDate+"')";

that's usually enough for ISO. You might have to look into converter functions.

Be careful of where the info comes from for theDate. If it comes from an editable field (etc.), then this is a candidate for SQL injection attacks.

However, if you convert to dateTime, and then paste into a SQL string, isn't that a layer of encoding?

answered Jul 12 '11 at 21:10

sgtz

8,849
9
51
91

thanks! no, I'm just pulling the info from another one of my databases :) – hemlocker Jul 12 '11 at 21:16

score 1 · Answer 6 · answered Jul 12 '11 at 21:14

I'd recommend using named parameters:

string testCommand = "INSERT INTO Fio_FinalSched (FinalDate) Values (@MYDATE)";
SqlCommand myCommand = new SqlCommand(testCommand,conn);
myCommand.Parameters.Add(new SqlParameter("@MYDATE", SqlDbType.DateTime)).Value = theDate;
myCommand.ExecuteNonQuery();

C# inserting datetime failure

6 Answers6