I am creating a data-entry form that allows the user to create/edit one record at a time. Basically like the Properties tab on mmMaintenanceForm. I'm inheriting from mmBusinessForm. My form has mmButtonNew, mmButtonSave, etc.

I'm connecting to SQL Server via a standard Business Object, using parameterized queries.

When I create two records in my table (the "Store" table) consecutively, the first create/save goes fine, but during the second create/save SQL Server raises this error:

Cannot insert the value NULL into column 'DeclineOffensive', table 'abc.dbo.Store'; column does not allow nulls. INSERT fails.

I have a default value defined on the DeclineOffensive column in SQL Server (default = 0). However, on the second INSERT, I can see hat MM.NET is explicitly passing a NULL value for columns that I didn't touch when editing the record in my form.

Using SQL Profiler, I can see that on the first INSERT, MM.NET is passing a statement with just a couple of parameters (one for each value I happened to set on the form for the record):

exec sp_executesql N'INSERT INTO Store( StoreNo , StoreName ) 
VALUES ( @p1 , @p2 )', N'@p1 varchar(3),@p2 varchar(8)', @p1 = 'S10', @p2 = 'Store 10'

Whereas on subsequent INSERT statements it passes a parameter for each column:

exec sp_executesql N'INSERT INTO Store( StoreNo , StoreName , Address , Address2 , City , State , 
ZipCode , Phone , Fax , DeclineOffensive ) 
VALUES ( @p1 , @p2 , @p3 , @p4 , @p5 , @p6 , @p7 , @p8 , @p9 , @p10 )', 
N'@p1 varchar(3),@p2 varchar(8),@p3 varchar(8000),@p4 varchar(8000),@p5 varchar(8000),
@p6 char(8000),@p7 varchar(8000),@p8 varchar(8000),@p9 varchar(8000),@p10 tinyint', @p1 = 'S11', 
@p2 = 'Store 11', @p3 = NULL, @p4 = NULL, @p5 = NULL, @p6 = NULL, @p7 = NULL, @p8 = NULL, @p9 = NULL, 
@p10 = NULL

That's fine; I understand that it may make sense for MM.NET to pass a value for each column, and that columns un-touched by the user on my form are are coming through as NULL. (Although it is curious that the first INSERT statement only includes columns that the user touches, and subsequent INSERT statement include all columns.)

Two questions:

1) Is there any way to take advantage of the defaults I've defined in SQL Server (e.g., the zero default for the column above), or should I be specifying a default for each column in MM.NET, even if it's just a zero for numeric columns and an empty string for text columns.

2) If I should be setting a default value in MM.NET for all columns that don't allow NULL, where is the best place to do this?

I know that I can create a StoreDefaultValues class and use it, but that strategy seems to be geared more towards setting default values that change at runtime (e.g., ParentOrderNo on an OrderLine).

I suppose that in my Store business object I could override HookPostAddNewRow, HookSetDefaultValues, or HookPreSave, and assign a value to each NULL-not-allowed column there. Seems like a lot of tedious work, though, and then the code is fragile (it has to be tweaked each time I add a new column to a table).

Thanks much,
Ric