C# inserting datetime failure

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-0开发者_如何学JAVA5 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!

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

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

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

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.

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

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?

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