Search code examples
c#datetimesmalldatetime

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-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!

Solution

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