asked on

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

ChetOS82

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_deepak

ASKER

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?

ChetOS82

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?

pawar_deepak

ASKER

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.

YZlat

how do you bind the value from the database to your textboxes? can you post the code or html?

YZlat

check this out;

http://forums.asp.net/t/1125337.aspx

ChetOS82

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

ASKER CERTIFIED SOLUTION

ChetOS82

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gfedz

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_deepak

ASKER

Solved the problem

ASP.NET &amp; SQL Server DATE problem

ASP.NET & SQL Server DATE problem