Converting a Value to NULL on server side in c#

Question

I have a method that populates a grid using a stored procedure. The SP gets it data from a database table but one of the columns is displaying the date as '01/01/1754 00:00:00'. I want this value to be shown as NULL on the grid.

How do I do this?

Here is my code for calling the SP, do I need some sort of IF statement?

public static DataTable getUserList()
{
    SqlConnection con = getConnection();
    SqlCommand cmd = new SqlCommand("PrepaidUserList", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandTimeout = Convert.ToInt32(ConfigurationManager.AppSettings["SqlTimeout"]);

    cmd.Parameters.AddWithValue("@ApplicationName", Membership.ApplicationName);

    DataSet ds = new DataSet();
    SqlDataAdapter dad = new SqlDataAdapter(cmd);

    try
    {
        con.Open();
        dad.Fill(ds);
    }
    finally
    {
        con.Close();
    }

    return ds.Tables[0];
}

Thanks

Without dismissing that you have good reason for using a min-date, have you considered refactoring the thing to use NULLs proper instead of inserting bad misleading data in the first place? — Grant Thomas, Dec 03 '13 at 12:22
I cannot change the database table as it may effect other parts of the application. I am just making a change so that it is easier to read... — jeffry, Dec 03 '13 at 12:24

score 0 · Answer 1 · answered Dec 03 '13 at 12:28

0

You have 2 option 1st one is stop the this type of date entry in database and another is Please alter the procedure and use CASE WHEN DateColumn='01/01/1754 00:00:00' then NULL ELSE DateColumn END

answered Dec 03 '13 at 12:28

Arun Singh

39
1
5

I cannot change the Stored Procedure as it may effect other parts of the application. I am just making a change so that it is easier to read... – jeffry Dec 03 '13 at 12:39
Then you need alter in grid view column like <%#Convert.ToDateTime(Eval("DateColumn")).Date == Convert.ToDateTime("01/01/1754 00:00:00").Date ? "NULL" : Eval("DateColumn")%> – Arun Singh Dec 03 '13 at 12:50

Damon · Accepted Answer · 2013-12-03T13:00:20.473

0

One option is to use the RowDataBound event. As each row of data is bound to the grid you have the ability to modify the contents of each cell in the grid.

In your case, you would examine the value of the date column and check if it matches a constant value of '01/01/1754 00:00:00', if it does set the cell contents text to "Null" (or whatever you want it to be).

So using an example from the above link:

protected void CustomersGridView_RowDataBound(Object sender, GridViewRowEventArgs e)
  {
    if(e.Row.RowType == DataControlRowType.DataRow)
    {
      if (e.Row.Cells[1].Text == "01/01/1754 00:00:00")
      {
          e.Row.Cells[1].Text = "Null";
      }
    }
  }

There's better ways of doing this, for example I think you can use the columnname instead of the indexer. Also move the date into a constant value not hardcoded into the method.

N.B. Also be aware of the dangers of hard-coding a date value, if you deploy to a machine that has a different date format (locale) your constant value may not be valid as a different date format will come though.

This link should also help you.

edited Dec 03 '13 at 13:00

answered Dec 03 '13 at 12:51

Damon

3,004
7
24
28

Thank you for your help but i cannot create a new method, i need to put the code inside the method that i have posted. So it checks each row in the Dataset for '01/01/1754 00:00:00' then replace it with 'NULL' – jeffry Dec 03 '13 at 13:22
Then I'm afaid your only option (from what I can see) is to iterate over each row in dt.Tables[0], do the necessary checks and insert it into a new data table (see http://stackoverflow.com/questions/1042618/how-to-create-a-datatable-in-c-sharp-and-how-to-add-rows). You can then return the new data table. – Damon Dec 03 '13 at 13:25
It would really help if you could post that, I have tried to iterate through each row but i keep getting error. Thanks for your help again... – jeffry Dec 03 '13 at 13:27
Don't worry about creating a new datatable, just edit it before returning it, see the accepted answer here (http://stackoverflow.com/questions/17338639/c-sharp-change-value-of-all-rows-of-a-specific-colum-of-a-datatable) for the code sample. – Damon Dec 03 '13 at 13:31

Converting a Value to NULL on server side in c#

2 Answers2