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

asp.netcsql

Hey there. i am having a problem. i want to get info on a package from database and get the software linked to that package..

my db looks like this.

create table Package
(
    PackageID int identity,
    PackageName varchar(50) not null,
    PackageDiscription varchar(max) not null,
    primary key (PackageID)
)



create table Software
(
    SoftwareID varchar(50) not null,
    SoftwareName varchar(50) not null,
    DownloadPath varchar(100) not null,
    PackageID int,
    Primary key (SoftwareID),
    Foreign Key (PackageID) references Package on delete cascade on update cascade
)

what i am trying to do is this:

protected void BtnPackageSelect_Click(object sender, EventArgs e)
{
    SoftwareTableAdapters.PackageTableAdapter p = new SoftwareTableAdapters.PackageTableAdapter();
    SoftwareTableAdapters.SoftwareTableAdapter s = new SoftwareTableAdapters.SoftwareTableAdapter();

    foreach(var item in p.GetSelectedPackage(DropDownList1.SelectedValue))
    {
        string PackageName = item.PackageName;
        int PackageID = item.PackageID;
        string PackageDiscription = item.PackageDiscription;

        Label1.Text = "Name: " + PackageName;
        Label1.Visible = true;
        Label2.Text = "ID: " + PackageID;
        Label2.Visible = true;
        Label3.Text = "Discription: " + PackageDiscription;
        Label3.Visible = true;
        ListBox2.Visible = true;

        foreach (var software in s.GetSelectedIndexSoftware(PackageID)) <----- this is there the error come.
        {
            ListBox2.Items.Add(software.SoftwareName);
        }
    }
}

the method GetSelectedIndexSoftware(packageID) looks like this:

SELECT SoftwareName
FROM Software
WHERE (PackageID = @PackageID)

all i got in the database now is:

insert into Package values('1', '1')
insert into Package values('2', '2')

insert into Software values('1', '1', '1', null)
insert into Software values('2', '2', '2', null)
insert into Software values('3', '3', '3', null)
insert into Software values('4', '4', '4', null)

where all software's null have been updated with 1

am fairly new to coding so am a bit lost.. hopeone someone can help me out to find the problem

——EDIT 1—–
i have now tryed to alter the create of Software to not allow null, and then create a package first called not linked where all software that are not yet linked to a package will be linked to. On create it is created with 1 in PackageID.
It didnt help.

——EDIT 2—–
i have now tryed to not have a foreign key on Software table.
didnt helt either

Best Answer

I think the reason you are getting an error is because you did not assign a value to the PackageID of the Software table. Therefore, when you do a "... s.GetSelectedIndexSoftware(PackageID)" in the for loop you are getting null. In your insert statements, you are not assigning a value to PackageID.

insert into Software values('1', '1', '1', null)

should be

insert into Software values('1', '1', '1', n)

where n is an id value from the PackageID column in the Package table.

If that does not work. You might want to try to make the SoftwareID column as an integer identity column on the Software table.

Related Solutions

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

This problem is usually caused by one of the following

null values being returned for columns not set to AllowDBNull
duplicate rows being returned with the same primary key.
a mismatch in column definition (e.g. size of char fields) between the database and the dataset

Try running your query natively and look at the results, if the resultset is not too large. If you've eliminated null values, then my guess is that the primary key columns is being duplicated.

Or, to see the exact error, you can manually add a Try/Catch block to the generated code like so and then breaking when the exception is raised:

enter image description here

Then within the command window, call GetErrors method on the table getting the error.
For C#, the command would be ? dataTable.GetErrors()
For VB, the command is ? dataTable.GetErrors

enter image description here

This will show you all datarows which have an error. You can get then look at the RowError for each of these, which should tell you the column that's invalid along with the problem. So, to see the error of the first datarow in error the command is:
? dataTable.GetErrors(0).RowError
or in C# it would be ? dataTable.GetErrors()[0].RowError

enter image description here

C# – DataTable.Load, One or more rows contain values violating non-null, unique, or foreign-key constraints

I managed to solve this by getting the schema of the table, iterating through the rows of the schema table (which are columns of the actual table) and creating a column with same properties as the schema column (only difference is setting new column's AllowDBNull to true and Unique and AutoIncrement to false) and finally adding the new column to a fresh datatable which will later be filled with data of our actual table (by the help of a DataReader to get only data and not the schema).

Here is the code:

using (SqlCeConnection C = new SqlCeConnection(DBStr))
            using (Cmd)
            {   //using SqlCeCommand
                Cmd.Connection = C;
                C.Open();
                using (SqlCeDataReader Rdr = Cmd.ExecuteReader())
                {
                    //Create datatable to hold schema and data seperately
                    //Get schema of our actual table
                    DataTable DTSchema = Rdr.GetSchemaTable();
                    DataTable DT = new DataTable();
                    if (DTSchema != null)
                        if (DTSchema.Rows.Count > 0)
                            for (int i = 0; i < DTSchema.Rows.Count; i++)
                            {
                                //Create new column for each row in schema table
                                //Set properties that are causing errors and add it to our datatable
                                //Rows in schema table are filled with information of columns in our actual table
                                DataColumn Col = new DataColumn(DTSchema.Rows[i]["ColumnName"].ToString(), (Type)DTSchema.Rows[i]["DataType"]);
                                Col.AllowDBNull = true;
                                Col.Unique = false;
                                Col.AutoIncrement = false;
                                DT.Columns.Add(Col);
                            }

                    while (Rdr.Read())
                    {
                        //Read data and fill it to our datatable
                        DataRow Row = DT.NewRow();
                        for (int i = 0; i < DT.Columns.Count; i++)
                        {
                            Row[i] = Rdr[i];
                        }
                        DT.Rows.Add(Row);
                    }
                    //This is our datatable filled with data
                    return DT;
                }
            }

Best Answer

Related Solutions

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

C# – DataTable.Load, One or more rows contain values violating non-null, unique, or foreign-key constraints

Related Topic