If the text field is empty, set the datetime field to NULL in SQL

Question

Trying to set the datetime field in the SQL table to NULL, if the text field is empty, I cannot 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 place a breakpoint, I get this for "var sql":

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

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

Edit: I understand the importance of preventing SQL injection, but this is a page on my internal web server for my use and is not intended for the public. This will help me track my personal items.

Answer 1

Parameterize.

First, you must transfer the user interface code from the database code, so that by the time he gets close to the DB database, we have correctly typed the data. For instance:

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

and enter any Parse etc. the code you want from the caller is not next to db. Now we need to implement this:

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

The problem seems to be with single quotes. If it is NULL, you should not have them.

In addition, you probably want to use a parameterized query (for security reasons, and pass values). In this case, quotation marks are also not needed.

Answer 3

I think the error is in string.format line . you cannot enable line break in line. Try one of the following:

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

or,

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

but, as the other answers mention, you should learn about SQL injection and consider a different approach.

Answer 4

Despite the problems in the code that are not considered to be the best SQL examples in C # code, you have several problems:

• You set EndDate to C # null. This is not the same as SQL NULL, which is referred to as DBNull.Value

• You do not take into account the fact that NULL does not need SQL quotes, so your SQL must be different in any case in order to work, even if you corrected # 1.

I suggest writing a stored procedure; if the end date text field is null, just do not pass this parameter and make it have a default value of NULL in the stored procedure.

 Create Procedure usp_TestDateRange_Update ( @ID int -- or whatever type your ID is @StartDate DateTime, @EndDate DateTime = NULL) As Update Test Set StartDate = @StartDate, EndDate = @EndDate Where ID = @ID 

Something like that. Now you need to make C # code to call the stored procedure and add parameters to the call from your text fields.

Answer 5

You can try as follows:

 string sql = String.Format(@"UPDATE Test SET StartDate={0}, EndDate={1} WHERE ID = {2}", (StartDateTxtBox.Text.Trim().Equals(string.Empty) ? StartDateTxtBox.Text:"NULL"), EndDate, id);