StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Problems with NULL

Problems with NULL

Howard Bennett — Fri, 01 Feb 2008 14:29:15 GMT

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!

RE: Problems with NULL

Trent L. Taylor — Fri, 01 Feb 2008 14:29:15 GMT

No problem :)

RE: Problems with NULL

Howard Bennett — Fri, 01 Feb 2008 13:55:41 GMT

Thanks for the help...again...

RE: Problems with NULL

Trent L. Taylor — Fri, 01 Feb 2008 13:52:13 GMT

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. :)

RE: Problems with NULL

Howard Bennett — Fri, 01 Feb 2008 13:44:52 GMT

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?

RE: Problems with NULL

Howard Bennett — Fri, 01 Feb 2008 13:42:10 GMT

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!

RE: Problems with NULL

Howard Bennett — Fri, 01 Feb 2008 13:41:08 GMT

Okay - that did the trick - thanks!

RE: Problems with NULL

Trent L. Taylor — Fri, 01 Feb 2008 13:16:13 GMT

Wow...you guys are getting good :D 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.

RE: Problems with NULL

Greg McGuffey — Fri, 01 Feb 2008 13:10:14 GMT

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 [u][b]must[/b][/u] 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!

RE: Problems with NULL

Ivan George Borges — Fri, 01 Feb 2008 12:50:21 GMT

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?