FK error when trying to update nullable FK field through ADO.NET

[Quimbly]

Preamble:

Users table:
UserID int, PK, not null
....
CultureID int, FK, null

Cultures table:
CultureID int, OK, not null
....

When updating the DB directly, I can set the CultureID column to NULL in an
SQL update:
e.g. UPDATE Users SET CultureID=NULL WHERE UserID=11

However, when I try to update using a strongly typed dataset over
webservices, I get this error:

The UPDATE statement conflicted with the FOREIGN KEY constraint
"FK_Users_Cultures". The conflict occurred in database "CentralV1_2Dev",
table "dbo.Cultures", column 'CultureID'.

Looking at the DataSet in the VS designer, the CultureID of the Users table
has the following properties:
AllowDBNull: True
DefaultValue: <DBNULL>
NullValue: (Null)
ReadOnly: False
Unique: False


Can anyone tell me why I'm getting this error and/or have any suggestions on
how I can get around it?

 

[Erland Sommarskog]

However, when I try to update using a strongly typed dataset over
webservices, I get this error:

The UPDATE statement conflicted with the FOREIGN KEY constraint
"FK_Users_Cultures". The conflict occurred in database "CentralV1_2Dev",
table "dbo.Cultures", column 'CultureID'.

Looking at the DataSet in the VS designer, the CultureID of the Users
table has the following properties:
AllowDBNull: True
DefaultValue: <DBNULL>
NullValue: (Null)
ReadOnly: False
Unique: False


Can anyone tell me why I'm getting this error and/or have any
suggestions on how I can get around it?

I would guess that you somehow manage to send 0 instead of NULL. Really
how you manage to achieve this, I don't know. But you could verify this
by using to Profiler to see what your application actually emits.


--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[Quimbly]

Wow, you must be psychic! How did you know?

Anyway, thanks very much. Yes, indeed, I was sending 0 instead of NULL. I
was originally doing this and attempting to change zeros into NULLs, but I
thought I removed that!

 

[Erland Sommarskog]

Wow, you must be psychic! How did you know?

Some years ago, one of our developers at an office a bit north up the
country called me or mailled me, and said that one of my FK constraints
were incorrect, because they kept firing, when added data and he did
not fill in that column.

I only told him to stop sending zeroes, when he should be sending NULL.

It's a fairly common mistake, not the least in traditional programming
languages, where normal data types never can have a NULL value, or anything
similar.


--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx