StrataFrame Forum

Problems with NULL

http://forum.strataframe.net/Topic13933.aspx

By Howard Bennett - 2/1/2008

I'm using DDT to setup a new database.  When I started I uncheked "Allow Null default value."  Don't know why - just thought that's what I was supposed to do...

I was able to setup my table and put fields on a form - no problems. 

I decided to add a couple of fields and the deployment to the server fails - b/c "Allow Null" is not checked (like the other fields.)  If I check the field - the deployment works - but the app fails with a "Conversion from type 'DBNull' to type 'String' is not valid."

Just need someone to tell me where I messed up and how to fix it - thanks!

HB

By Ivan George Borges - 2/1/2008

Hi Howard!

At the time you were adding new fields into your table at the DDT, have you tried assigning "Default Values" on the Additional Settings group?

By Greg McGuffey - 2/1/2008

Ivan's got it right. If you have existing data in the database and you need to add a field that does not allow NULLs, then you have two options (these are SQL Server restraints, not DDT restraints...or rather, the DDT just has to live with the SQL server restraints):



- You add the field and set it to not allow NULLs and you must set a default.

- You add the field and allow NULLs, then after the field is added, you go in and populate the new field with data, then go back and change the field to not allow NULLs.



The first is almost always the way to go unless there is something complicated going on with the field.



Note that if you need to change a field that was formally NULL to one that is NOT NULL (SQL terminology here), then the same issue applies with the same possibilities.



Hope that helps!
By Trent L. Taylor - 2/1/2008

Wow...you guys are getting good BigGrin  Ivan and Greg are right.  Property standards really lean away from default value support, you are better off assigning a default value.  The reason this worked initially is because you did not have any records.  But now that you have records in the table, you just need to assign a default value so that the existing records will initialize their fields with a value rather than DBNULL.
By Howard Bennett - 2/1/2008

Okay - that did the trick - thanks!
By Howard Bennett - 2/1/2008

I played around with that - but apparently not the right combination of allow nulls and default values.  It's working now with a default value and "Allow Nulls" not checked. Thanks for your help!
By Howard Bennett - 2/1/2008

So...let me be sure I've got this straight...

When I setup the table - I am correct in unchecking the "Allow Nulls" checkbox - and when adding fields, I need to be sure there is a default value and the "Allow Nulls" is not checked.  Is that right?

By Trent L. Taylor - 2/1/2008

Correct.  You really don't want to allow null values within a database if you have control over it.  When you initially create your structures, you can leave the Default blank and not select Allow Nulls and deploy without any type of error as you are deploying a new structure where there are no records...thus no field values have to be initialized.  But when you go back into that structure and add new columns to an already deployed table, you will want to be sure to supply a default value, otherwise you will run into the issue that you experienced.  But it is best to not allow nulls. Smile
By Howard Bennett - 2/1/2008

Thanks for the help...again...
By Trent L. Taylor - 2/1/2008

No problem Smile