Displaying Date (c# winforms)

I have a SQL Table with the following columns:

PROFILE
----------------------------------
Name | DateOfBirth | Address | ID
----------------------------------
     |             |         |
     |             |         |

[Name] is a required field while the ID is an autoincrement column. The remaining fields are not required.

When it comes to displaying again the information of a user in my form, since the DateOfBirth field is not required I always got this value in my textBox 1900-01-01 00:00:00.000 even if it not supplied upon registration.

What I'm trying to accomplish is to display an empty string inthe textBox if the there's no date of birth.

With my naive way of thinking, I wrote a method where it checks first the DateOfBirth column, if it is null, it will return an empty string, else, return the date of birth as a string ex. txtDateOfBirth.Text = GetDateOfBirth(); but it always return the value `1900-01-01 00:00:00.000' cause the field in the database always inserts this value even the user leaves this field as blank.

Can you please help me... thanks...

I also tried this but still don't work:

SELECT BIRTHDATE = CASE BIRTHDATE
          开发者_开发技巧  WHEN CAST('1900-01-01' AS DATETIME) THEN '' 
            ELSE BIRTHDATE END
FROM USER

---

My psychic debugging skills tell me that you're mistakenly setting the birthdate when you insert the row. A good check for that is to hook up a profiler (one comes with SQL Server, if that's what you're using), and watch what's actually getting inserted when you create a new user.

Check the code that's inserting a new user. If you're using DataSets, for instance, check that you don't have a default constraint set on the Birthdate column (click on the cell in the DataSet designer, and check the Properties grid).

It's also possible that the database itself has a default constraint set on that column, but that feels less likely.

---

Any reason why you don't want to do this?

string theDate = GetDateOfBirth().ToString();
txtDateOfBirth.Text = theDate  == "1900-01-01 00:00:00.000" ? "" : theDate;  

---

I got it already! :)

Here's my original code:

protected void SaveUser()
    {
        SqlConnection conn = new SqlConnection(connectionString);
        conn.Open();

        SqlCommand cmd = new SqlCommand("INSERT_USER");
        cmd.Parameters.Add(new SqlParameter("@NAME", txtName.Text.Trim()));
        cmd.Parameters.Add(new SqlParameter("@ADDRESS", txtAddress.Text.Trim()));
        cmd.Parameters.Add(new SqlParameter("@DATEOFBIRTH", txtDateOfBirth.Text.Trim()));
        cmd.Connection = conn;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.ExecuteNonQuery();
    }

In my original code, even though txtDateofBirth is empty, it will always pass an empty string to the parameter causing the database to insert '1900-01-01 00:00:00.00', not the NULL value. Here's the modified code. I check first if txtDateOfBirth is not empty (assuming the value entered is a valid date) if true, add cmd.Parameters.Add(new SqlParameter("@DATEOFBIRTH", txtDateOfBirth.Text.Trim())); as a parameter to INSERT_USER stored procedure. Else, @DATEOFBIRTH parameter is not being created and passed at all.

protected void SaveUser()
{
    SqlConnection conn = new SqlConnection(connectionString);
    conn.Open();

    SqlCommand cmd = new SqlCommand("INSERT_USER");
    cmd.Parameters.Add(new SqlParameter("@NAME", txtName.Text.Trim()));
    cmd.Parameters.Add(new SqlParameter("@ADDRESS", txtAddress.Text.Trim()));
    if(txtDateOfBirth.Text.Trim() != String.Empty)
        {
            cmd.Parameters.Add(new SqlParameter("@DATEOFBIRTH", txtDateOfBirth.Text.Trim()));
        }
    cmd.Connection = conn;
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.ExecuteNonQuery();
}

SP:

CREATE PROCEDURE [dbo].[INSERT_USER] 
(
    @NAME AS VARCHAR(25),
    @ADDRESS AS VARCHAR(50) = NULL,
    @DATEOFBIRTH AS DATETIME = NULL
)
AS
BEGIN
    INSERT INTO [USER] ([NAME], ADDRESS, DATEOFBIRTH) VALUES (@NAME, @ADDRESS, @DATEOFBIRTH)
END