.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Inserting null value into foreign key table with allow null enabled

Posted By:      Posted Date: October 03, 2010    Points: 0   Category :ASP.Net

Hey guys, 

I have 2 table Table A, Table B.

Table A - Parent Table

ID - Uniqueidentifier not null(PK)

Table B -Child Table              

ID - uniqueidentifier null(FK, TableA)

I have a stored procedure to insert data into Table B, but when I tried to insert a null value into column ID of table B, this error came up:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tableB_tableA". The conflict occurred in database "database name", table "table A", column 'ID'.

The statement has been terminated.

The insert statement in asp.net is like so:

sqlcommand.parameter.add(New Sqlparameter("ID", Nothing))

I am guessing adding nothing to the null value column is the problem, but i don't know how to fix it... can any1 help me?


The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tableB_tableA". The co

View Complete Post

More Related Resource Links

Problem inserting a null value into date column in SQL table


Hi all,

I am having a hard time inserting a null date into a SQL table.  Please be aware that I am using the Microsoft Application Data Blocks and not pure ADO.net.  I find a million examples of how to do this with ADO.net but cannot find anything about this using the MS Data Application Blocks.

Here is my VB.net code:

Save(Session("ref_no"),  txtdischarge_date.Text) 

Sub Save(ByVal ref_no As Integer, discharge_date As Date)

        SqlHelper.ExecuteNonQuery(connString, ref_no, discharge_date), "MyTableInsert")

End Sub

The textbox txtdischarge_date can either be a valid date or blank.  If it contains a valid date then the insert works properly.  But if the textbox  is blank, then I want to insert a null value into my date column.  I cannot get this to work.  I get this error when I run this and txtdischarge_date is blank:

Conversion from string "" to type 'Date' is not valid

Here is the code for my stored procedure, MyTableInsert:

ALTER Procedure MyTableInsert
   (@ref_no int,
    @discharge_date datetime = NULL)

How to deal with NULL values in a SQL table


 Hi all

In order to add/update/delete data from a table I build 2 classes: 1 class containing all the table fields and another class with methods to add/insert/update/delete records. I am learning how to avoid errors when your table must use SQL NULL values. One error is when I call my UPDATE method (see below):

  public void UpdateItem(OcItemConstraintDetails item)
  SqlConnection con = new SqlConnection(connectionString);
  SqlCommand cmd = new SqlCommand("ItemConstraints_Update", con);
  cmd.CommandType = CommandType.StoredProcedure;

  cmd.Parameters.Add(new SqlParameter("@itemConstraintId", SqlDbType.Int, 4));
  cmd.Parameters["@itemConstraintId"].Value = item.ItemConstraintId;

  cmd.Parameters.Add(new SqlParameter("@path", SqlDbType.NVarChar, 150));
  cmd.Parameters["@path"].Value = item.Path;

if you watch the SQL profiler trace you will see that I am passing a value of 'default' ;so that causes me problems.

exec ItemConstraints_Update @itemConstraintId=1,@path=default

With that said, what are the best practices when your SQL table accepts NULL values and when:
- you are passing an empty value to your stored procedure;

SELECT statement to return NULL by matching data from another table.

Hi,I am fairly new at SQL and I have been struggling for days now trying to find an answer to my problem and i have come to the point where i have run out of ideas and about to give up. I'm hoping someone can put me in the correct path. The problem I have 3 table Table 1 Department" has the following columns: REF, NAME Table 2  "Department_Collection" has the following columns: REF, DEPARTMENT_REF, MANAGER_REF, STORE_REF, ACTIVE Table 3 Store" has the following columns: REF, NAME, STORE_ID  What i am trying to do is to take all the rows in the Department table and get a matching row (DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF) from the Department_Collection table, if it does not match any then still display DEPARTMENT.NAME but mark DEPARTMENT_COLLECTION.REF as null. I have tried the following select statement but it seem to remove all null values when supplied with a 'storename' SELECT DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF FROM DEPARTMENT_COLLECTION right outer join DEPARTMENT on DEPARTMENT_COLLECTION.DEPARTMENT_REF = DEPARTMENT.REF left outer join STORE on DEPARTMENT_COLLECTION.STORE_REF = STORE.REF where STORE.NAME = 'storename' order by DEPARTMENT.NAME   Any help will be greatly appreciated. Thanks

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign

Using Visual Studio with MySQL.In my XSD dataset I created a query. It runs perfect. I can preview the data fine.In my BLL I wrote code (see below) to retrieve the query results and I'm getting...Using db As New dsDemoTableAdapters.DemoTableAdapter Dim dt As New DataTable dt = db.GetDemo(DemoId) ' ERROR HAPPENS HEREFailed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.Why would previewing the data work but in code it fails?Any ideas?

if NULL in database table - display empty string?


I have a table in an Sql Server Database from which I want to fetch data to a form. 

[Headline] [varchar](max) NULL,
[Text] [varchar](max) NULL,

[ID] [int] IDENTITY(1,1) NOT NULL,

[Headline] [varchar](max) NULL,

[Text] [varchar](max) NULL,

I want to check both Headline and Text to see if they are null, and if so display an empty string each for them. I've looked at some tutorials but don't yet understand really how to use it. DBNull? DataRow.IsNull? Something else? Can I check both Headline and Text at the same time or do I need do check them separately?

Inserting NULL in a TableAdapter


I have two columns not nullable (A and B) and one column nullable (C).  All the columns are nvarchar.

I want to insert a record and leave C null.

How do I do this with:

MyTableAdapter.Insert ( "A", "B",  X) where X is the syntax that sets column C to NULL.

Thanking you in anticipation.



How to get Default value in table to work when NULL in DataSet

Is this possible? If the parameter value is NULL in DataSet, the default value is not assigned.

trigger problem inserting null values??


I have created a trigger for insert and whenever i insert the data the trigger should fire and notify some value inserted i want to create log table to save this information,now i have created a trigger

USE [mine]
/****** Object: Trigger [dbo].[insert2]  Script Date: 10/03/2010 15:00:21 ******/
ALTER trigger [dbo].[insert2] 
  on [dbo].[emp] 
  after insert 
  declare @sno int,@result varchar(50),@empid int,@name varchar(50) 
select @empid=empid,@name=@name from emp  
 insert into triglog values(@empid,@name)  
but when

C# How to check prammatically if null value exists in database table (using stored procedure)?


How to programattically check if null value exists in database table (using stored procedure)?

I know it's possble in the Query Analyzer (see last SQL query batch statements)?

But how can I pass null value as parameter to the database stored procedure programattically using C#?

Although I can check for empty column (the following code passes string.Empty as parameter but how to pass null value?), I cannot check for null value in the following code snippet:

SQL Queries:

USE [master]

IF EXISTS (SELECT name FROM sys.databases WHERE name = 'ExampleDatabase')
DROP DATABASE [ExampleDatabase];

CREATE DATABASE [ExampleDatabase];

USE [ExampleDatabase];

DROP TABLE dbo.ExampleTable;

IsActive BIT,


Processing orphan dimension records with NULL parent foreign keys

<head> <style type="text/css"> .style1 { font-family: Arial, Helvetica, sans-serif; } .style2 { font-family: Arial, Helvetica, sans-serif; font-size: x-small; margin-bottom: 0px; } .style3 { font-size: x-small; } .style4 { font-family: Arial, Helvetica, sans-serif; font-size: x-small; margin-bottom: 0; } .style5 { color: #0000FF; } .style6 { font-family: "Courier New", Courier, monospace; } .style7 { color: #0066CC; font-weight: bold; font-style: italic; } </style> </head>

This link Handling Data Integrity Issues in Analysis Services 2005 is a classic paper on handling data integrity. The snowflaked data structure decribed in it is:

Product Class (dimension)  <--  Product (dimension)  <--  Sales (fact) where some Products do not have a Product Class

My situation is similar, but a bit more complex.   I don't actually have referential integrity problems . . . orphans are valid.

I have two fact tables TableA and TableB. They are related in a [0-or-1] to a [0-or-many] structure.

  • That is, each TableA record may have zero, one, or many TableB records associated with

How do display null rows returned from my table valued function?

DECLARE @techNumLoop VARCHAR(25)
 Tech Varchar(25) 
,Item_Count Integer NULL 
,Expenses MONEY NULL

SELECT DISTINCT t.Case_Number Tech
FROM tblTechnology t
OPEN techCursor;
FETCH NEXT FROM techCursor INTO @techNumLoop;
INSERT @myTemp
FROM ufn_Find_Some_Stuff(@techNumLoop)


Getting null when using dm_db_partition_stats with #temp table


drop table #TEMP
select * into #TEMP from tblEmp
Select SUM(row_count) from sys.dm_db_partition_stats where object_id = OBJECT_ID('dbo.#TEMP')
AND (index_id = 0 or index_id = 1)

TSQL cell balance from previous non null row table contains 20mill + rows so cursor not practical


I have a requirement to carry a running total forward in a table containing multiple periods and accounts, the table extract below shows an example

for row_num 5 the RunningTotal is -32.98, I would like to UPDATE the value of row_nums 6 through 9 to be -32.98 as they are at present NULL, the row_num 9 has a RunningTotal value of -32.69 I would like to UPDATE all preceding NULL values with -32.69 (10 through 13), the pattern being NULL value to be updated with populated value untill new populated value encountered 

I have set a field to be row_num using ROW_NUMBER () based upon TenancyNumber and WeeklyPeriod, a cursor is not practical in this instance as this table has rapid growth and presently has population of 20 mill + rows


rttdrtcw_id TenancyNumber WeeklyPeriod Tran_Year Tran_Week Receipt Debit Credit Adjustment row_num RunningTotal

18947           111                 200005        2000            5              -247.4   247.69 0     0            

Null value causing problems in variables populated from table during fetch



I have some tsql that interates through a table, pulls the column values from the table into variables and then uses these variables to construct another variable to use as the @body parameter for an email.

The code works perfectly as long as all the columns in the table have values however, if any are null then the variable is not constructed and the body of the email is blank.

How can I deal with the fact that there will always be some columns in my table that are null?


I need to null the numeric and decimal fields in a table (SQL 2008)


I have imported data from Foxpro into a Table that has about 50 columns.  Some columns are "varchar", some are "int", and some are "decimal".  Is there a way to iterate through the fields and check to see if the "int" and "decimal" fields contain Zero to make them null?

Do I have to create an update statement for each field or can it be done in a loop?


Hope I explained that correctly.

Inserting a NULL value through a TableAdapter


I have tableadapters for almost all data access, so of course records are inserted into the database with statements like: -
    Dim taColumns As New GroupTableAdapters.GroupTableColumnsTableAdapter
    taColumns.Insert(value, value, value, ......     )

One of the values has been a Guid field, defined in the database as Uniqueidentifier with the Allow Nulls option checked.  If the taColumns.Insert(....) statement has "Nothing" in the corresponding position then the row is correctly created with a NULL column.   However sometimes this column is not null, so I need to be able to write logic like this: -

    Dim Guidvalue as ????
    If condition Then
         Guidvalue = Nothing
         Guidvalue = a real guid value
    End If
    taColumns.Insert(........, Guidvalue, ....)

I can't find a way of doing this.  Whether I define Guidvalue As Nullable(of GUID) or simply as Guid, and whether I assign Nothing or Guid.Empty,  I end up with a non-NULL value of an empty guid (all zeros) on the

Children Grid (list) - null FiledValue - Foreign Key not showing display name



Based on I Stephen's blog post (http://csharpbits.notaclue.net/2008/08/dynamic-data-and-field-templates.html) I some what managed to embed Child gridview in a grid view. 

Issue I am running into is: display name doesn’t appear for Foreign Keys. After little investigation I realized that "FieldValue" is null which is causing the issue. 

Does anyone know what can cause FieldValue to be null for FieldTemplateUserControl?


I am using VS2010 and EF 


Here what the issue look like in the UI ( https://picasaweb.google.com/lh/photo/SLNXD79uek0QSq5NNK-niQ?feat=directlink )


Here is codebehind for ChildrenGrid


using System;
using System.Linq;
using System.Web.DynamicData;
using System.Web.UI.WebControls;
using System.Collections.Generic;

public partial class ChildrenGrid_Field : System.Web.DynamicData.FieldTemplateUserControl
    protected MetaTable table;

    public Boolean EnableDelete { get; set; }
    public Boolean EnableInsert { get; set; }
    public Boolean EnableUpdat
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend