locked
Cannot configure SqlDataSource RRS feed

  • Question

  • User-952550035 posted

    Hello,

    I have met a problem while configuring SqlDataSource.

    Connection is OK, but when I test SELECT request, the Error message appears:

    There was an error executing the query. Please check the syntax of the command and if present, the types and values of the parameters and ensure they are correct.
    Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

    I tried the same SELECT request in Page_Load function and it works fine.

    Where is a problem ?

    Thanks in advance

    Tuesday, August 30, 2011 10:38 AM

Answers

  • User-2010311731 posted

    Sorry, I must have gotten confused.  Since CLMN_ID is already unique, please disregard my previous post.

    It looks like you also need CLMN1 to be unique so that it can function properly as a foreign key in another table.  If CLMN1 is not unique, then yes, you will have to include CLMN_ID in your SELECT query.

    The only way I can think for you to be able to delete the duplicate record would be to drop the foreign key constraints on your other table(s), then manually delete the record, then re-add the foreign key constraints.

    Ideally, I would recommend using CLMN_ID as the foreign key in your other tables instead of CLMN1.

    Matt

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 30, 2011 3:48 PM

All replies

  • User-2010311731 posted

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

    This usually means you are trying to insert a duplicate record or that you didn't define a primary key on the table.  Every record in a table needs to either have a primary key or else something in the record has to be unique.

    Since this is a SELECT statment, it probably means that you already have duplicate records in the table.

    If this doesn't help, then please post some code so we can see if something is wrong with your SQL statement.

     

    Matt

    Tuesday, August 30, 2011 10:55 AM
  • User-952550035 posted

    You are right,

    In my SELECT statement there were 2 columns - CLMN1 and CLMN2. CLMN1 - primary key but without Unique constraint.

    Then I went to my database and tried to put unique constraint to CLMN1.

    Operation failed because there were 2 records with same value of CLMN1, but with different CLMN_ID (CLMN_ID - primary & unique key).

    I tried to remove one of repeated records, but operation also failed, because CLMN1 is referenced in some table as foreign key.

    Only workaround I found is include in SELECT query the "REAL" primary key - CLMN_ID.

    This way it worked.

    Is there simple way to "cure" table of 2 identical values in CLMN1.

    Thanks.

     

    Tuesday, August 30, 2011 11:10 AM
  • User-2010311731 posted

    Is there simple way to "cure" table of 2 identical values in CLMN1.

    You could drop the primary key restraint on CLMN1 and add an auto-incementing integer as the primary key, delete the duplicate, then remove the new integer field and reset CLMN1 as the primary key.

    Sorry, I know that is not exacly "simple".  Be sure to do a full backup before you mess with the tables :-)

     

    Matt

    Tuesday, August 30, 2011 11:21 AM
  • User-952550035 posted

    Thanks Matt,

    add an auto-incementing integer as the primary key

    I don't exactly understand what you mean. Autoincrementing property exists already in CLMN_ID column.

    Should I also make CLMN1 autoincrement ?

    Tuesday, August 30, 2011 3:36 PM
  • User-2010311731 posted

    Sorry, I must have gotten confused.  Since CLMN_ID is already unique, please disregard my previous post.

    It looks like you also need CLMN1 to be unique so that it can function properly as a foreign key in another table.  If CLMN1 is not unique, then yes, you will have to include CLMN_ID in your SELECT query.

    The only way I can think for you to be able to delete the duplicate record would be to drop the foreign key constraints on your other table(s), then manually delete the record, then re-add the foreign key constraints.

    Ideally, I would recommend using CLMN_ID as the foreign key in your other tables instead of CLMN1.

    Matt

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 30, 2011 3:48 PM