none
[URGENT!!! pls help!!!!] "Failed to enable constraints exception - how to overcome?" RRS feed

  • Question

  • Hi,

    I have 3 very simple tables:
    "Orders" table (has 2 fields: "order_id" which is a primary key and "order_desc")
    "Payments" table (has 2 fields: "payment_id" which is a primary key and "amount")
    "Payment2Orders" table (has 2 fields: "payment_id" which is a primary key and "order_id" which is also a primary key). There are relationships between "Payment2Orders" and the 2 other tables.
    Note that there might be several payments for 1 order (as if the customer has a payment plan or something)

    Once database created, I went to Visual studio, created a dataset and a stored procedure (all via the Designer) which returns all orders assigned to payments:
    SELECT orders.order_id, orders.order_desc payements.amount
    FROM orders INNER JOIN
    payement2orders ON orders.order_id = payement2orders.orderid INNER JOIN
    payements ON payement2orders.payement_id = payements.payement_id

    When I run the stored procedure in the VS (in the DataSet designer), all is fine, but when I run it from the code, it says gives an exception saying "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints." I can understand why this message comes (this is due that the query returns same order (with same order_id) which is "not allowed" due to the primary key in the order_id). I don’t want to remove this primary key as it sounds reasonable to make sure that an order has a unique ID

    In order to overcome this, I have changed .EnforceConstraints = False in the automatic generated code wherever a dataset was created but it didn’t help (hope I did it well).
    Any suggestions? I'm quite lost…

    Thanks!!!


    Wednesday, March 23, 2011 10:51 AM

Answers

All replies

  • Hello Loren,

     

    Welcome to the MSDN Forum!

    According to your description, I think what I suggest you to solve that problem is to delete the table and query, then drag and drop the stored procedure to the dataset designer AGAIN.

    I hope this can help you.

     

    Have a nice day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, March 25, 2011 2:45 AM
    Moderator
  • Thanks Jackie Sun, but it didn't help. As I said, the exception comes when filling "orders"'s (this table has a primary key (order_id)) datatable using the table adapter (which returns several rows WITH SAME order_id). I did a work-around which I'm not sure is a good practice. I went to the dataset designer and removed the primary key of "orders" table. Now all is fine, no exception, BUT I have seen that this didnt affect the data base itself (key is still there!) so I dont really understand why changing the table definition in the dataset designer doesnt affect the database. Actually, I'm happy it doesnt change the database, as I wanted this key to be there. I guess the dataset can be different from the database(?)
    Friday, March 25, 2011 7:38 AM
  •  I guess the dataset can be different from the database(?)


    Hi Loren,

     

    Thanks for your feedback.

    You know, in one table or datatable, the PK or the entity key is unique. So I think you've found the direction to resolve your issue.

    About the above statement you said I quoted, I don't think so. Because during our development, we usually meet this situation that dataset is different from database, there are some reasons. please read this:

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/316380ba-ceaf-493a-a54c-1f978b6a1322

    I hope this can help you.

     

    have a nice weekend!


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, March 25, 2011 7:48 AM
    Moderator
  • Thanks, Jackie Sun! This helped.
    Friday, March 25, 2011 4:06 PM