If Textbox is empty set datetime field to NULL in SQL

Question

Trying to set a datetime field in a SQL table to NULL if the textbox is empty, I can't seem to get this to work.

        string EndDate = "";
        if (String.IsNullOrEmpty(EndDateTxtBox.Text.Trim()))
        {
            EndDate = null;
        }
        else
        {
            EndDate = EndDateTxtBox.Text;
        }

        var sql = String.Format(@"UPDATE Test SET StartDate='{0}', 
                                 EndDate='{1}' WHERE ID = '{2}'",
                                 StartDateTxtBox.Text, EndDate, id);

When I do this and put in a break point I get this for "var sql':

"UPDATE Test SET StartDate='5/23/2013', EndDate=" WHERE ID = '19'"

I tried removing the ' from the sql string but that didn't work either. Any suggestions?

Edit: I understand the importance of preventing against SQL injection but this a page on my internal web server for my use only and not projected to the public. It's to help me keep track of personal things.

Answer 1

Parameterize.

First, you should move the UI code away from the database code, so that by the time it gets anywhere near the DB we have correctly typed data. For example:

void UpdateDates(int id, DateTime startDate, DateTime? endDate) {...}

and put whatever Parse etc code you want at the caller - not near the db. Now we need to implement that:

void UpdateDates(int id, DateTime startDate, DateTime? endDate) {
    //... where-ever cmd comes from, etc
    cmd.CommandText =
        "update Test set StartDate=@start, EndDate=@end where ID = @id";
    cmd.Parameters.AddWithValue("id", id);
    cmd.Parameters.AddWithValue("start", startDate);
    cmd.Parameters.AddWithValue("end", (object)endDate ?? DBNull.Value);
    cmd.ExecuteNonQuery();
    // ... cleanup etc
}

Or with a tool like "dapper":

void UpdateDates(int id, DateTime startDate, EndDate? endDate) {
    //... where-ever connection comes from, etc
    connection.Execute(
        "update Test set StartDate=@start, EndDate=@end where ID = @id",
        new { id, start = startDate, end = endDate}); // painfully easy
    // ... cleanup etc
}

Answer 2

It sounds like the problem is the single quotes. If it is NULL then you shouldn't have them.

Also, you probably want to be using a parameterized query (for safety reasons and pass in the values). In that case the quotes shouldn't be necessary either.