ASP.NET & SQL Server DATE problem

I have 4 TextBoxes on a form for entering date in ASP.NET web-application.

When I enter date in one textbox and enter the record, it looks fine. But when I come back to that page later, it displays correct date in 1st TextBox but with time (9/15/2010 12:00:00 AM) and default values in other TextBoxes in which I didn't enter date  (01/01/1900 12:00:00 AM).
These default values automatically go to Database tables. I am using Datareader to read values from DB to display on the form.
Database fields are of type DATETIME.

Can you please tell me how can I remove those default values and stop them from getting entered automatically?


Environment: Visual Studio 2008, ASP.NET, C#, SQL Server 2008
pawar_deepakAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ChetOS82Commented:
You need to use the .ToShortDateString() method when writting to the textboxes.  Also, you need to compare the datetime value to "01/01/1900 12:00:00 AM" and only write to the textbox if the date is greater.
pawar_deepakAuthor Commented:
Hello ChetOS82,

Thanks for your response. Can you please tell me how can I write code to compare datetime to "01/01/1900" and insert to DB only if its greater?
ChetOS82Commented:
There are two issues at work here.  One is that the minimum date for .NET is 01/01/0001 but SQL is 01/01/1900.  The database will always store "empty" datetimes as 01/01/1900 because that is the lowest value it can store (and in .NET Datetimes cannot be null).

By "automatically" I assume you mean that you are not handling the INSERT and UPDATE into the database using code.  In this case, I don't know what you can do.  You need to make it so the column is only updated (or inserted) if the datetime does not equal DateTime.MinValue.

Can you post some code to help me see how this is "automatically" writting to the database?
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

pawar_deepakAuthor Commented:
DateTime date1 = txt1.Calendar.SelectedDate;
DateTime date2 = txt2.Calendar.SelectedDate;
DateTime date3 = txt3.Calendar.SelectedDate;

str = insert into TABLE (date1, date2, date3) values ('"+date1.ToShortDateString()+"', '"+date2.ToShortDateString()+"', '"+date3.ToShortDateString()+"');

I'm using a Calendar control to select date. If I select date in only 1st textbox and leave other 2 textboxes empty, it inserts my selected date from 1st txtbox and it inserts 01/01/1900 for other two which I left empty.
YZlatCommented:
how do you bind the value from the database to your textboxes? can you post the code or html?
YZlatCommented:
ChetOS82Commented:
You need to change your SQL generation code so that it only inserts date2 when (date2 != DateTime.MinValue).  Something like this would work for something as small as this (I typed this off the top of my head, so there may be some syntax errors)
string columnList = "";
string valueList = "";
if (date1 != DateTime.MinValue) {
    columnList += "date1,";
    valueList += "'" + date1.ToShortDateString() + "',";
}
if (date2 != DateTime.MinValue) {
    columnList += "date2,";
    valueList += "'" + date2.ToShortDateString() + "',";
}
if (date3 != DateTime.MinValue) {
    columnList += "date3,";
    valueList += "'" + date3.ToShortDateString() + "',";
}
// Only execute SQL if at least one date was selected.
if (!String.IsNullOrEmpty(columnList)) {
     columnList = columnList.Substring(0, columnList.Length - 1); // Remove the last comma.
     valueList = valueList.Substring(, valueList.Length - 1); // Remove the last comma.

     str = String.Format("insert into TABLE ({0}) VALUES ({1})", columnList, valueList);
}

Open in new window

ChetOS82Commented:
Now, after you do this, when you retrieve the values from the server they will be NULL, which will get converted to DateTime.MinValue (01/01/0001) when loaded into a variable.  So, when you bind the variable to a textbox or calendar control, you need to check to make sure it isn't MinValue.  This is also just an example.
if (date1 != DateTime.MinValue) {
    txbDate1.Text = date1.ToShortDateString();
}

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gfedzCommented:
Replace my textbox with your own, but try using:

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        txtEndDate.Text = Request(txtEndDate.UniqueID)

    End Sub
pawar_deepakAuthor Commented:
Solved the problem
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.