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

dotnet framework

    Next

  • 1. odd behaviour adding record to sql database
    i have created a form to submit data to an sql database, and it seems to work, except for one glitch. one of the fields, the "creditcardnumbertxt" text box, when entering a number with more that 7 characters, does not work. sql says it will be truncated. in the asp code, the database, and the sql procedure, the field is correctly set to allow a (sql datablase) char type of 18 characters in size. here is the error it spits back ---------------------------------- String or binary data would be truncated.The statement has been terminated..Net SqlClient Data Provider ----------------------------------- in the code behind the page, these are the relevant lines: ------------------------------------ cmd.Parameters.Add(New SqlParameter("@credit_card_nbr", SqlDbType.Char, 18)) cmd.Parameters.Item("@credit_card_nbr").Value = RegCreditcardnumberTXT.Text ----------------------------------- the relevant lines in the procedure in the sql server is ----------------------------------- @credit_card_nbr char(18), ------------------------------------ any help would be great thanks for the help bryan
  • 2. Identifying a newly created row ?
    Hi I have a DataGrid bound to a DataView, and when adding a new row to the view I want it to be the current/selected one I've tried the followerin DataRowView row = myView.AddRow() row["Date"] = DateTime.Today
  • 3. Connection is busy with results for another command
    After migrating an existing app from VB6 to VB .Net, SQL operations on ADO started giving this error periodically: Connection is busy with results for another command I am still using the ADODB library -- I did not migrate to ADO.Net Looking at the SQL Server enterprise manager, I see that the .NET version opens 4 database connections, while the VB6 version only opens one (both programs were tested on the same computer with the same databases.) Anyone have insights on why ADODB would launch multiple connections which then collide with one another? Thanks, Lee

Re: FK error when trying to update nullable FK field through ADO.N

Postby UXVpbWJseQ » Thu, 06 Dec 2007 02:21:00 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!  

Re: FK error when trying to update nullable FK field through ADO.N

Postby Erland Sommarskog » Thu, 06 Dec 2007 08:08:11 GMT

Quimbly ( XXXX@XXXXX.COM ) writes:

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,  XXXX@XXXXX.COM 

Books Online for SQL Server 2005 at
 http://www.**--****.com/ 
Books Online for SQL Server 2000 at
 http://www.**--****.com/ 

Similar Threads:

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

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?

2.Get FK:PK relationships from a schema and from a result set

3.Getting metadata - the matchink PK for a FK

4.Linq Using Null FK

I have an issue with Linq to SQL using a Null FK. Here is the situation.

Two Tables:
TableA
PKColumn UniqueIdentifier
<Other Columns>

TableB
PKColumn int
FKColumn UnqiueIdentifer (can be Null) --This is a FK to table A PKColumn.

I create all of this in my Linq To SQL designer (using normal drag and drop 
methods) and then when I try to call:

  dataContext.TableB.InsertAllOnSubmit<TableB>(ListOfTableBs);
  dataContext.SubmitChanges();

on objects that have the FKColumn set to null I get the following exception:
  Specified cast is not valid.

I have checked and double checked that my values that allow nulls are all
correct (the FKColumn is set to allow nulls in both the DB and the designer.)

Note that the insert works fine when the FKColumn value is a valid guid.

I have also run straight SQL and Sprocs to do the insert of the data and
that works too (just to ensure that my data schema was correct.)

I have tried to override the Insert portion of my TableB object in the Linq to
SQL Designer to use a working sproc but I still get the same exception.

The lack of any real info (from the debugger) on what has gone wrong makes 
this error even more frustrating.  (The call stack says nothing useful.)

Any ideas or fixes would be greatly appreciated.

Vaccano

5.Stop FK constraints until committing

Does anyone know how to stop SQL Server from checking FK constraints
until the end of a transaction?

I am using a typed dataset, with lots of related tables, two of which
are related to each other (perhaps a silly idea, but the best solution
I've found for storing a tree).
Basically I can't insert a new tree in my tree table without the root
node existing in the node table, and I can't insert the root node in
the node table without the tree existing in the tree table :/

6. enforcing FK constraints on datatables that are not in a DS

7. Problem with NULL values and FK constraints

8. Checking FK constraints in advance from the client?



Return to dotnet framework

 

Who is online

Users browsing this forum: No registered users and 94 guest