none
database issue: constraint exception RRS feed

  • Question

  • hi all:

    the system always popup an error message "constraint exception", which says Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

     

    the error massage still popup even i had switched off EnforceConstriants property of my dataset 

    can one explain what's going on and how to fix it?

     

    more details: the code works fine with empty table. the system popup error message once data is entered into table

     

     

    really appreciate for your help

     

    regards

     

    Andy.W

     

    Friday, January 18, 2008 10:11 AM

Answers

  • What's going on is exactly what the exception message says is going on.  The hard part is finding out which row(s) is/are violating which constraint(s).  Try this:

     

    Code Snippet

     

    foreach (DataRow r in myTable.Rows)

    {

       if (r.HasErrors)

       {

          if (!String.IsEmptyOrNull(r.RowError))

          {

             Console.WriteLine(r.RowError);

          }

          foreach (DataColumn c in r.ColumnsInError)

          {

             string s = r.GetColumnError(c);

             if (!String.IsEmptyOrNull(s))

             {

                Console.WriteLine("Column {0}: {1}", c.ColumnName, s);

             }

          }

       }

    }

     

     

     

    Friday, January 25, 2008 12:01 AM

All replies

  • Basically the error is saying you are violating one of the database constraints when you are sending the data back to the database.  A good example is sending a new record to the database whos primary key already exists

     

    Friday, January 18, 2008 10:39 AM
  • actually, i am not sending data back to the database, i am retrieving data from database via GetNameAndAddress method, which i created:

     

    something like:

     

    Dim table As DataTable = ContactDataTableAdapter.GetNameAndAddress()

    ...

    dim name as string = table.rows(0)(0).tostring

    ...

    ....

    Friday, January 18, 2008 10:54 AM
  • Move if from VB Express forum to .NET Framework Data Access and Storage forum for better responses.
    Wednesday, January 23, 2008 7:19 AM
  • What's going on is exactly what the exception message says is going on.  The hard part is finding out which row(s) is/are violating which constraint(s).  Try this:

     

    Code Snippet

     

    foreach (DataRow r in myTable.Rows)

    {

       if (r.HasErrors)

       {

          if (!String.IsEmptyOrNull(r.RowError))

          {

             Console.WriteLine(r.RowError);

          }

          foreach (DataColumn c in r.ColumnsInError)

          {

             string s = r.GetColumnError(c);

             if (!String.IsEmptyOrNull(s))

             {

                Console.WriteLine("Column {0}: {1}", c.ColumnName, s);

             }

          }

       }

    }

     

     

     

    Friday, January 25, 2008 12:01 AM
  • How did you originally get the table created in the ds, generated from a database schema or from XSD? There are two potential areas that could cause problems:

    1 - field values not conforming to column definitions (ie type, null, etc)
    2 - relationships between tables not correct (ie a FK back to another table that dosn't have the corresponding row)

    When you said you had set EnforceConstraints to false on your parent DS...is there any way that the table you are dealing with is somehow not part of the same instance? 

    In general, when I am working with large DS containing multiple tables all of which need filling from a database, I try my best to make sure I do the loading of data in the correct order.  Sometimes, ie when management are breathing down my neck, I'll turn the constraints off but that generally means I will have issues later on.  I guess one option would be to try and fill the table one row at a time, so give the dataadapter a FillByFieldName method, at least that way you should trap the offending row of data at the point at which you are trying to load it....
    Friday, January 25, 2008 3:53 AM
  • Setting EnforceConstraints to false is a near-certain guarantee that you'll be working some late nights in a couple of weeks trying to figure out what the hell went wrong with your data.

     

    Friday, January 25, 2008 9:01 AM
  • thank you all for your reply, the code works fine now

     

    Friday, January 25, 2008 1:02 PM