Add new column is DBNULL

[steve_rm]

Hello,

VS 2005

I am developing the database application. This is a live database and is being used by the customer.

I am to release a new version and I had to add new columns to fit the requirements DateStarted (DateTime), and TotalHours(Int) into a database table.

There are already over a 1000 rows in this table. When the customer wants to look at a record in this table and insert the value into the text boxes (Front-end), when it gets to the DateStarted or TotalHours it comes up with a error message:
"The value for column 'DateStarted' in table 'IncidentTask' is DBNull."

The method for inserting is:

  Me.dtDateStarted.Value = Me.DsIncidentsControl.IncidentTask(0).DateStarted

What are the possible solutions to this problem? Would it mean checking for a DBNULL before displaying in the textboxes? Or updating the new column rows with an date:

UPDATE IncidentTask SET DateStarted = '1/1/2005' WHERE (DateStarted IS NULL)

Many thanks for any suggestions,

Steve

[si_the_geek]

Putting fake data into a table sounds very wrong to me - it sounds as if the issue is with your application, not the database.

I'm not sure where the issue is or how to fix it, but that is what you should be trying to do.

[szlamany]

How did you add the column to the table?

Usually we do it with an ALTER TABLE command and at first allow nulls.

Then update the column to a good value - such as 0 or "empty string" (or leave it null for a date).

Then if appropriate we ALTER TABLE again and set it to not allow nulls.

[steve_rm]

Hello,

I added the table by using management studio and right clicking on the table and select new column.

I clicked allow nulls.

Many thanks for any more help,

[szlamany]

If you are doing it in management studio then don't allow nulls but instead when you add the column give it a default value - such as "" for an empty string or 0 for a numeric column.

That way the column is added to existing rows with this value.

Then remove the default - since it's only purpose was to pre-load values into existing rows.