ObjectSharp Blogs

You are currently viewing

Dave Lloyd's 2 Cents

: 

A .NET Developer's Perspective


Home > Blogs > dave

EnforceConstraints

Posted: Friday, November 07, 2003 9:02 AM by dave
Filed under:

Have you ever tried to update a Dataset and received this message?

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

Not a problem in a small DataSet. But if your Dataset is more complex sometimes it's not easy to see the problem right away.

There is a property on the DataSet called EnforceConstraints. When you set this to true the DataSet will try to enforce all the constraints in the DataSet (Not null columns, Foreign keys, unique keys) The problem with this exception is, it tells you very little. You know you have violated a constraint, but which one? The way to find out is to look at the row and column errors.

Below is a method that will attempt to apply contraints and write the errors out to the output window. I hope someone out there finds it useful.

Public Sub GetDataSetErrors(ByVal ds As System.Data.DataSet)

 

Public Shared Sub GetDataSetErrors(ByVal ds As System.Data.DataSet)

 

Try

ds.EnforceConstraints =

True

Catch ex As Exception

Debug.WriteLine("DataSet errors: " & ds.DataSetName)

 

For Each table As DataTable In ds.Tables

 

Dim ErrorRows As DataRow()

ErrorRows = table.GetErrors()

 

For Each row As DataRow In ErrorRows

Debug.WriteLine("Table: " & table.TableName)

Debug.WriteLine(" Row Error: " & row.RowError)

 

Dim ErrorColumns As DataColumn()

ErrorColumns = row.GetColumnsInError()

 

For Each column As DataColumn In ErrorColumns

Debug.WriteLine("Column: " & column.ColumnName)

Debug.WriteLine(" Error: " & row.GetColumnError(column))

 

Next

 

Next

 

Next

 

End Try

 

End Sub

Comments

  • dave November 15, 2003 10:34 AM

    This is pretty handy. I was surprised to learn that broken constraints go into the datarow & column error strings. I've used those SetRowError and SetColumnError a lot to store validation rules. Makes me think its worth storing those externally in case they get wiped out by broken constraints.

  • dave February 5, 2004 11:51 AM

    thanks for the code, this is very handy

  • dave February 12, 2004 4:49 PM

    Priceless!!! I'm writing a lot of provider-agnostic code, and this was just the thing to make my life easier.

  • dave February 19, 2004 1:19 PM

    Thanks for saving my ass

  • dave February 19, 2004 1:22 PM

    Pardon my language, but I was hunting down this bug for about an hour, and your code was a lifesaver. Thanks again.

  • dave March 2, 2004 3:12 PM

    Excellent, this at least hints at what is the problem. Thanks!!

  • dave March 9, 2004 3:32 PM

    I was working on this from last 3 days. Finally got it. Thanks a lot man. Great Idea.

  • dave March 12, 2004 10:26 AM

    Hi Dave, this bit of code save our asses! We were really stumped with the canned exception message but your code gave us a HUGE clue as to what was crashing our web app. Again, thanks a million!

  • dave March 12, 2004 10:34 AM

    Hi Dave, here's a C# version we used:

    try
    {
    da.Fill( ds, "MyTable" );
    //ds.EnforceConstraints = true;
    }
    catch( Exception ex )
    {
    Response.Write( "DataSet errors: " + ds.DataSetName + "\n" );

    foreach( DataTable table in ds.Tables )
    {
    DataRow[] ErrorRows = table.GetErrors();

    foreach( DataRow row in ErrorRows )
    {
    Response.Write( "Table: " + table.TableName + "\n" );
    Response.Write( " Row Error: " + row.RowError );

    DataColumn[] ErrorColumns = row.GetColumnsInError();

    foreach( DataColumn column in ErrorColumns )
    {
    Response.Write( "Column: " + column.ColumnName + "\n" );
    Response.Write( " Error: " + row.GetColumnError( column ) + "\n" );
    }
    }
    }
    }

  • dave March 23, 2004 4:55 PM

    bravo!!!!
    exelente!!!

    gracias!.

  • dave April 16, 2004 1:49 AM

    Hi Dave,
    This bit of code saved me hours!!! I was tearing my hair out in trying to figure out which constraint i VIOLATED!!!!

    This code saved my ass in terms of time and ease of use. Kudos to you Dave, thanks again!!!
    Microsoft should not be so slack and out put such useless error messages!!!!

    Regards

  • dave June 10, 2004 11:38 AM

    where is "debug" defined???

    I recieve an error that debug is not defined.

  • dave June 10, 2004 12:00 PM

    It's in "System.Diagnostics"

  • dave June 10, 2004 12:01 PM

    Thanks to Jo Yoshida for posting the C# version. It's very much appreciated.

  • dave June 28, 2004 12:36 PM

    .

  • TrackBack August 18, 2004 4:21 PM

  • dave September 10, 2004 2:58 PM

    So wonderful!!!!! Thank you for sharing!

  • dave September 15, 2004 1:27 PM

    This code helped me very much.....Thank you

  • dave September 29, 2004 4:40 PM

    so does anybody have an idea on how to fix this problem. because i check my codes and i don't see where this null value is being entered..


    thank you in advance
    jen0s

  • dave September 30, 2004 8:57 AM

    If you write the method in the blog and call it, There will be a message in the output window telling you where the Null value is being entered. Dave

  • dave October 23, 2004 11:39 AM

    This approach is extremely useful. We have put similar code into the exception that we throw from our DAL. Our DAL exception class has a constructor that takes an exception and a dataset as parameters. If the exception is a constraint exception then the code loops through the dataset to find the RowError as you have shown.
    Together with the Exception Managment Application Block from microsoft this is pretty neat (the RowError is put into the AdditionalInfo collection of the Exception).

    The code would then look like this:
    catch(Exception ex)
    {
    LibraryException exp = new LibraryException("Failed to load mydataset", ex, ds);
    throw exp;
    }

  • dave October 29, 2004 2:08 PM

    Saved my life, thanks.

  • dave February 3, 2005 5:43 AM

    Saved my life, thanks.

  • dave February 14, 2005 2:31 AM

    great!

  • dave March 3, 2005 3:56 PM

    Very handy tool. Saved a lot of time in debugging.

  • dave April 8, 2005 11:39 AM

    Thanks, Dave! Just used this today.

  • dave August 22, 2005 4:25 PM

    Ey Dave, you're great!!! You saved my head and my ass. Thank you man!!

  • dave September 29, 2005 1:47 PM

    This is realy very useful in my application. I couldn't figure out the error for 2 days untill i used this one.

    Thanks A lot,
    Hetal Kamdar

  • dave November 29, 2005 7:04 AM

    Superb Exception !!!

  • dave December 26, 2005 8:18 PM

    thanks for your code!!! This really useful...

  • dave March 6, 2006 12:25 PM

    As my grandpappy usta say: ham dandy!

  • dave March 17, 2006 4:18 AM

    Dave!!! Thanks a lot!!!! Your method's indeed very useful! Thanks!!! Likewise you saved my life dude!! hehe. Thanks!!!

  • dave April 12, 2006 12:55 AM

    sorry for the stupid question, but where does response come from? (pertaining to C# code!)
    can't find it

  • dave April 12, 2006 8:49 AM

    You can use Debug.Writeline in C# also. It's even better to write to the application Event Log or something like that.

  • dave July 6, 2006 5:00 PM

    I have a trouble. How can i invoke the method getDataSetErrors()?

  • dave September 14, 2006 4:22 AM

    This is really great, thanks. I found the reference to this page from here: http://www.falafel.com/tabid/110/articleType/ArticleView/articleId/319/Default.aspx

    Unfortunately I'm a newbie to ASP.NET, so I was having trouble implementing the C# variation of the function.

    I finaly figured out that in my dataset file (.xsd), I could right-click on a datatable or a TableAdapter and choose "Preview Data...".

    In the resulting pane, I can select a method such as GetByProjectID(@ProjectID), provide the input param, and hit the "preview" button.

    The result is a view of the datatable populated with data from the database.

    In my case, a couple of the columns had no data because I did not include those columns in my stored procedure. Those two columns are defined as "not null" in the database.

    When I included those two columns in my procedure's SELECT statement, I could now see values in the datatable preview, and that solved my EnforceConstraints issue.

    I never use those two columns anywhere, but I guess that since the dataset is a disconnected version of the database table, all the integrity rules are enforced.

  • hydrocodone no prescription March 25, 2007 7:09 AM

    news

Leave a Comment

(required) 
(optional)
(required) 

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS