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 - 03 Dec 2007 23:26 GMT
> However, when I try to update using a strongly typed dataset over
> webservices, I get this error:
[quoted text clipped - 13 lines]
> 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.

Signature
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
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 - 04 Dec 2007 17:21 GMT
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 - 04 Dec 2007 23:08 GMT
> 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.

Signature
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
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