confliction when inserting data to sql

Question

0.00/5 (No votes)

See more:

I have three table

1-first one is the parent table and have those columns

AdvId    AdvName   RegId

2-second one is child table

RegId RegName   GovId

and there's A FK between the first and the second table
3-third one is child table also but to the second table

GovId GovName

and there's aforign key between the 2nd and 3rd table

NOW I have this sql statenment to isert data

SQL

ALTER proc [dbo].[InsertUser]
(@AdvName nvarchar(50),
@AddGov int,
@Addtype int,
@Addreg int,
@AddFloor int,
@Addfinish int,
@Addsalary int,
@Addtel int)
 
 as insert into AdvertiseNames
 (AdvertiseName,EstateId,FinishingId,FloorId,RegionId,Salary,Telephone)
 values
 (@AdvName,@Addtype,@AddFloor,@Addfinish,@Addsalary,@Addtel,@Addreg)
 
insert into Regions
(GovNo)
values
(@AddGov)

Now I insert the regid and the govid from dropdownlist and advname from textbox

I have this Error

 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_AdvertiseName_Regions". The conflict occurred in database "RealEstateSite", table "dbo.Regions", column 'RegionsId'.
Cannot insert the value NULL into column 'RegionName', table 'RealEstateSite.dbo.Regions'; column does not allow nulls. INSERT fails.
The statement has been terminated.
The statement has been terminated.

>>>what shoul I do ?????

Posted 30-Dec-11 0:37am

Sarah Mohammed77

Updated 30-Dec-11 7:35am

v3

Add a Solution

2 solutions

Solution 2

first you have written some missing

SQL

insert into regions
(GovId)
values
(@Addgov)

insert regionname also in this query. you have set this column not null. and you are inserting null value, so there is an error.
Try this, if not solve then send me what error comes now.

Posted 30-Dec-11 0:43am

Sarvesh Kumar Gupta

Updated 30-Dec-11 2:21am

v2

Comments

Sarah Mohammed77 30-Dec-11 7:18am

but I inserted regionid which taken from dropdownlist into table 1
why should I insert the region name again???

Sarah Mohammed77 30-Dec-11 7:18am

and there is a FK between tow table

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2011-12-30T00:40:00

Yuo have a foreign key between the tables checking that AdvertiseName.RegionsId value is found in regions. With the parameters you have supplied this does not exist.

So check the value in your parameter @Addreg

Also check that you have supllied all necessary columns and values in each insert.

Addition:
It looks like the inserts are in wrong order, first insert to master table then to child.
Also the values look that they are in different order than the columns. Should the insert statement be like:

SQL

ALTER proc [dbo].[InsertUser]
(@AdvName nvarchar(50),
@AddGov int,
@Addtype int,
@Addreg int,
@AddFloor int,
@Addfinish int,
@Addsalary int,
@Addtel int)
 
 as 
insert into Regions
(GovNo)
values
(@AddGov);

insert into AdvertiseNames
 (AdvertiseName,EstateId,FinishingId,FloorId,RegionId,Salary,Telephone)
 values
 (@AdvName,@Addtype,@Addfinish,@AddFloor,@Addreg,@Addsalary,@Addtel);

Also is the value in @Addtype really supposed to go to field EstateId?

One more thing. If you every time add a row to regions, won't that lead to duplicates? Should you add the region only if it does not exist?