Friday, October 30, 2009

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




Please visit my new Web Site WWW.Codedisplay.com



Few days ago i found an error in RDLC report after published. The client claims that sometimes the report shows fine but sometimes shows the error. Then i test this report in my development PC but no luck. I can not generate any error. After that in my testing i found an error like below:

"An error has occurred during report processing.
Exception has been thrown by the target of an invocation.
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."


Then i start googling what was the problem. I found few solutions but these does not work for me. Then i have started to test again to produce this error. Here i want to show you how i can generate this error in my development PC. To ease the example i will show you by a simple query which caueses this problem. To generate this error add a DataSet into your project. Then right click on it to configure. Select Use SQL statements radio button then next next finish. The SQL for DataAdapter which i have used to produce this error is given below:

SELECT Title,Amount,Sort FROM
(

SELECT 'None' Title,100 Amount,1 Sort

UNION

SELECT 'Sale of subscriptions and connections',150,2

UNION

SELECT 'Sale of airtime for prepaid',200,3
UNION

SELECT 'Broadband/Internet',220,4

UNION

SELECT 'Prepaid services',200,5

UNION

SELECT 'Partner/3rd party - revenues/ Partner/3rd party - revenues',190,6

UNION

SELECT 'Other',240,7

UNION

SELECT 'Costs/revenue sharing',250,8

) tbl Order BY Sort

Ok our data adapter now configured. Now add a RDLC report. Drag a Table object on it. And drag & drop Title & Amount column in the table details row.

Ok now our RDLC report also configured. Now open the default.aspx page or add another aspx page into your project which will contain the RDLC report within ReportViewer control. Now add a ReportViewer control in your page. Add an ObjectDataSource into your page. Align DataSet with your ObjectDataSource. Now assign the DataSourceObject in the ReportViewer Control.

Now run the project you will get the below screenshort:
Fig: RDLC Report Error


Solution:
After different of tests i found that the problem is the length constraints. In DataSet i found the backend code generated length of 37 for Title column. So when the resultset try to retrieve a record more than length of 37 then report generates the error: Failed to enable constraints. Look at the auto generated code behind of DataSet:

Fig: DataSet Markup Code

So just change the length for the column will resolve the problem: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

Note:
This type of error may appeared for different type of scenarios here i just shared one of them which i did not get in GOOGLE.

9 comments:

Anonymous said...

a very good post, it solved my problem

Anonymous said...

in my case this solution works fine, thanks
jorge_log@hotmail.com

Anonymous said...

Perfect! saves me lots of time trying to trace this obtuse error message. Thanks

Anonymous said...

Thank you so much. I don't know if I ever would have found this solution.

Anonymous said...

Thanks is solved my problem

Anonymous said...

Thanks a Lot, it Saved my time..

Unknown said...

Thanks is solved my problem

Milka dinah Salido Castrejon said...

you save my life, thanks bro

Dinesh Jaiswal said...

I did the same as above but it didn't work for me , could any one suggest me how to resolve this problem....

Want to say something?
I WOULD BE DELIGHTED TO HEAR FROM YOU

Want To Search More?
Google Search on Internet
Subscribe RSS Subscribe RSS
Article Categories
  • Asp.net
  • Gridview
  • Javascript
  • AJAX
  • Sql server
  • XML
  • CSS
  • Free Web Site Templates
  • Free Desktop Wallpapers
  • TopOfBlogs
     
    Free ASP.NET articles,C#.NET,VB.NET tutorials and Examples,Ajax,SQL Server,Javascript,Jquery,XML,GridView Articles and code examples -- by Shawpnendu Bikash