Unexpected Behavior of Default Constraints with ADO.Net

Question

Unexpected Behavior of Default Constraints with ADO.Net

Thomas Corcoran

SSC Rookie

Points: 36
More actions
November 19, 2007 at 9:37 am

#92751

A Pricing table has, among other things, five nullable columns with default constraints. In every case the column (either money or integer) is set by the default to 0. The defaults work as expected when data is added with T-SQL INSERT INTO statements. When data is added through ADO.Net, all of the values remain NULL. Making the columns NOT NULL (thinking that that would force the constraint to fire) simply results in the "column cannot be null" SqlException.
Has anyone seen this before? Thanks for any help.
Sincerely,
Tom Corcoran
Here is the scripted table:
CREATE TABLE [dbo].[Pricing](
[PricingID] [int] IDENTITY(1,1) NOT NULL,
[AvmProductTypeID] [int] NOT NULL,
[FixedPrice] [money] NULL,
[IsFixedPrice] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IsVolumeBased] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PricingName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ServiceAgreementID] [int] NOT NULL,
[SpecialProcessingFee] [money] NULL CONSTRAINT [Col_SpecialProcessingFee_Default] DEFAULT ((0)),
[ClickFee] [money] NULL CONSTRAINT [Col_ClickFee_Default] DEFAULT ((0)),
[ClickFeeReceivableCompanyID] [int] NULL,
[MinimumCharge] [money] NULL CONSTRAINT [Col_MinimumCharge_Default] DEFAULT ((0)),
[VolumeDiscount] [money] NULL CONSTRAINT [Col_VolumeDiscount_Default] DEFAULT ((0)),
[VolumeDiscountAbove] [int] NULL CONSTRAINT [Col_VolumeDiscountAbove_Default] DEFAULT ((0)),
CONSTRAINT [PK__Pricing__4FFCBE51] PRIMARY KEY CLUSTERED
(
[PricingID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

Defaults only will take affect if the columns are not included in the insert statement. When you include the columns in the insert statement that means you are also specifying that the column should be "null".

SQL = Scarcely Qualifies as a Language

Steve Jones - SSC Editor SSC Guru Points: 738693 More actions · Answer 2

I haven't. Are you not including any columns in the ADO insert?

Thomas Corcoran SSC Rookie Points: 36 More actions · Answer 3

Carl,

Thanks for your response. The INSERT INTO works, as you say because I have not included the defaulted columns. It is the ADO.Net SqlAdapter.Update that fails to fire the constraint.

See my response on that to Steve Jones below.

Tom Corcoran

Thomas Corcoran SSC Rookie Points: 36 More actions · Answer 4

Steve,

Thanks for your response. Yes, the DataSet being updated in ADO.Net includes the default columns. There's no easy way around that because under some conditions those columns need to be populated to be editable in the GUI. If the default constraint cannot do the job, I imagine that I could write a trigger. I don't have enough experience with Sql Server 2005 to know if that's a good practice or not.

Tom Corcoran

Thomas Corcoran SSC Rookie Points: 36 More actions · Answer 5

I looked at the ADO execution through SQL Profiler. All the columns are populated. Those not assigned were NULL as expected. The sp_executesql stored procedure ran, but I saw no activity from the default constraints. So I removed the constraints and wrote an Instead of Insert trigger to do the same thing. That worked. Some day I'll learn the answer.