Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
HomeAnnouncementsFree MagazinesWhite PapersSubmit Content
Discussion GroupsASP.NETWindows FormsLanguages.NET FrameworkVisual Studio.NET
Articles.NET FrameworkASP.NETToolsWindows Forms
.NET DirectoryOpen Source ProjectsUser GroupsWeb Resources
Related Topics
Visual Basic 6SQL ServerMS AccessOther DB ProductsMS Server ProductsMore Topics ...

.NET Forum / .NET Framework / New Users / December 2007

Tip: Looking for answers? Try searching our database.

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

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Quimbly - 03 Dec 2007 19:38 GMT
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


Rate this thread:







Free Magazines

Get these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...

Oracle MagazineNetwork ComputingComputer WorldBio-IT WorldeWeekInformation WeekInfosecurity
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.