Failed to enable constraints. One or more rows con

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

Failed to enable constraints. One or more rows con

Author

Topic

MrKanin
Starting Member

11 Posts

Posted - 2011-01-08 : 11:27:46

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

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-08 : 11:47:15

You need to create an array, populate the array with your software ID numbers, then reference the resulting Array in your for each loop.

Is the method returning the values into an array? that would be the first place to look.

So you would create an array variable, populate it with the method, then reference the array variable for your for each sequence.

Poor planning on your part does not constitute an emergency on my part.

MrKanin
Starting Member

11 Posts

Posted - 2011-01-08 : 12:01:04

why do u want to create a array with software id's ?

its the package ide i use to get software name.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-08 : 12:15:51

Maybe I mistyped because I was trying to help. If you have the answers already, sorry to have bothered. You need to create an array for your for each loop, fill the array using the method, then loop through the array. However, since this is a SQL Server forum, not a C# code forum, and you didn't post the actual error I had to guess. The problem isn't the SQL you use to get the list, but you need your code to put that returned list into an Array for the C# code to use in it's loop.

Poor planning on your part does not constitute an emergency on my part.

MrKanin
Starting Member

11 Posts

Posted - 2011-01-08 : 12:20:02

ahh sorry didnt see the topic name was shortened.. here is the whole error massage:

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

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-08 : 12:43:15

Make sure the PackageID column in Software table is not nullable. This is likely what is causing your error

Poor planning on your part does not constitute an emergency on my part.

MrKanin
Starting Member

11 Posts

Posted - 2011-01-08 : 12:55:16

its am nullable.. because when i create software they are not assigned to a package.. that first comes after. but maby make it not nullable and just create it with a -1 will do the trick ?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-08 : 13:08:26

Create it with a zero. I am not 100% sure, but it seems that a nullable FK constraint causes the issue based on a few google searches and light reading.

Poor planning on your part does not constitute an emergency on my part.

MrKanin
Starting Member

11 Posts

Posted - 2011-01-08 : 13:28:10

have just tryed creating em all with a 0 insteed.. but off course it wont work. no Package got PackageID = 0,

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-08 : 14:05:23

I am lost. Are you erroring on CREATING them, or SELECTING THEM in your code? I realize that "no package got PackageID = 0", but the idea is that the packageID column should not allow NULL's.

Is the error happen
ing on your method call? or in the initial table population?

I am not even sure you need the constraint. You could likely have on delete triggers remove the needed records and not run into the constraint issues.

Poor planning on your part does not constitute an emergency on my part.

MrKanin
Starting Member

11 Posts

Posted - 2011-01-08 : 14:28:54

it is happening when i try to SELECT..

have tryed the select within Query builder and in SQL server management and both places its working with a PackageID i know is present.. but as soon as the PackageID comes from the foreach where i use the:

int PackageID = item.PackageID (<--- this PackageID i can see in debug that its right PackageID)

as input it fails.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-08 : 14:47:11

what do you mean "as input it fails?"..please clarify

Poor planning on your part does not constitute an emergency on my part.

MrKanin
Starting Member

11 Posts

Posted - 2011-01-08 : 14:57:28

in the first foreach i get some info on a package witch i save in these:

string PackageName = item.PackageName;
int PackageID = item.PackageID;
string PackageDiscription = item.PackageDiscription;

in the next foreach i use GetSelectedIndexSoftware() with PackageID as parametre. the same PackageID as stated above.

and its there i get the error..

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-08 : 15:01:30

So in the method to get the software name, it throws a constraint violation error?

And if you have a packageID = 1 in Software, the query to get Software name throws a constraint violation?

How exactly are you connecting to SQL to get execute the query?

Poor planning on your part does not constitute an emergency on my part.

MrKanin
Starting Member

11 Posts

Posted - 2011-01-08 : 15:16:05

i have my Visual studio connected to my database and i then use a dataset to get the tables and in that i have a method i can use called GetSelectedIndexSoftware(int PackageID)

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-08 : 15:26:43

That in memory dataset may be the problem. Have you considered connecting directly to SQL and executing the query directly there?

Otherwise, I think you need to check that dataset you build and confirm all of the column names , datatypes, lengths are consistent and accurate. It is possible you have a constraint identified incorrectly in that dataset definition.

Poor planning on your part does not constitute an emergency on my part.

MrKanin
Starting Member

11 Posts

Posted - 2011-01-08 : 15:58:22

i have thought about connecting directly to the database.. but i am working on this project for a company that want to use the dataset.. thats why i am using it.

the dataset tables are just a drag n drop of the tables in the database.. so dont know it that can alter the tables ?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-08 : 16:08:01

Did you recreate the dataset after specifying that the PackageID cannot contain NULL?

Poor planning on your part does not constitute an emergency on my part.

MrKanin
Starting Member

11 Posts

Posted - 2011-01-10 : 03:23:43

i have tryed to recreate the dataset. and its not helping.. i have now tryed to do this insteed:

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;
var softwares = s.GetSelectedIndexSoftware(PackageID);
if (softwares != null)
{
foreach (var software in softwares)
{
ListBox2.Items.Add(software.SoftwareName);
}
}
}

and the same error comes on:
var softwares = s.GetSelectedIndexSoftware(PackageID);

i have tryed debug and there i can see that the packageID that its getting in are valid and is known in the database.. but still the error comes.

MrKanin
Starting Member

11 Posts

Posted - 2011-01-10 : 04:09:33

Error found. it was because the GetSelectedInsexSoftware() query was only getting the softwareName. that was not enough. so when i expanded the select to get all info it worked.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources