Problem w/ SQL server's default value

[Calvin]

Hi all,

I have a problem when creating a new record in SQL
database when the column is Non-Nullable w/ default value.
What I did was create SqlDataAdapter and DataSet, filled
the DataSet w/ data AND schema. However, when I try to
create a record with those default columns empty (i.e. not
assigning value to them), the Update method of Adapter
raises an exception that I am trying to creating a record
w/ the non-nullable column set to null, even tho I have
create a default value for them in Sql server.

Any help would be appreciated.

Calvin

 

[Miha Markic]

Hi Calvin,

You should modify both insert and update commands as they are trying in
insert/update value into that field.
Delete the field in question from command text and remove the adjacent
parameter from parameters.

 

[Cowboy \(Gregory A. Beamer\)]

You will have to write custom insert and update commands for this particular
DataAdapter. The default commands created take an optimistic approach (ie,
no defaults on non-nullables, no identity columns, et al). The system will
compensate for some exceptions, but not all.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************

 

[Calvin]

Hi,
So does that mean I can't use the CommandBuilder to build
the strings? Isnt' that very not generic if we were to
build custom Insert strings considering that both SQL
server and ADO.NET are both Microsoft's product? Thanks
for your help.

Calvin

 

[Calvin]

Is there any way that I can use the default CommandBuilder
to do that? The reason is same as the reply above. Thanks.

Calvin

 

[Bernie Yaeger]

Hi Calvin,

Miha's point is correct. Your select includes the cols in question. I have
run into the same problem - here's the inelegant solution: use the
commandbuilder but give the default values as they are called for in sql
server when you do an insert. I know this is 'doing it twice' but since the
commandbuilder will dynamically create an insert statement that looks for a
value for the default cols, there is no other way to do this, unless you
don't use the commandbuilder and create your own insert command, which
excludes these cols.

HTH,

Bernie Yaeger

 

[Miha Markic]

Unfortunatelly not.
Anyway, you should avoid using CommanBuilder at run time.
Rather, design your adapters at design time (using same command builder) -
you can easily change the generated code + it runs faster.

 

[Calvin Lai]

Thanks. The only reason why I am doing this is because I am writing a
generic data access class for all tables and need to use CommandBuilder. The
way I *solve* the problem is to create a SetDefaultValue() function, which,
checks when saving if the record contains a non-null column and if that
column contains no data, I set the default value to it. Kind of like the
solution proposed by Berine in the other branch of this thread. Anyhow, I
couldn't think of some other smarter and faster way...

Calvin