Click here to Skip to main content
15,179,684 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
Hi All,
I am trying to save data in a table using store procedure.while saving the data i am getting a message saying

error message:
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_tbl_Items_tbl_StoreProfile'. The conflict occurred in database 'CSTORESDB', table 'tbl_StoreProfile', column 'stp_Storeid'.
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
The statement has been terminated.


store procedure:
SQL
ALTER       proc usp_InsertItems
(
@categoryID int,
@storeID int,
@itemCode varchar(30),
@itemName varchar(30),
@qty int,
@uomID int,
@itemValue decimal(18,2),
@desc varchar(255),
@status bit,
@returnValue int out
)
as

if not exists(select itm_ItemCode from tbl_Items where (itm_ItemCode =@itemCode and stp_Storeid = @storeID /*and ctg_Categoryid = @categoryID*/))
begin
	if not exists(select itm_ItemCode from tbl_NonInventoryItems where (itm_ItemCode =@itemCode and stp_Storeid = @storeID /*and ctg_Categoryid = @categoryID*/))
	begin
		if not exists(select Lott_ItemCode from tbl_Lottery where (Lott_ItemCode =@itemCode and stp_Storeid = @storeID /*and ctg_Categoryid = @categoryID*/))
		begin
			if not exists(select itm_ItemName from tbl_Items where (itm_ItemName = @itemName and stp_Storeid = @storeID /*and ctg_Categoryid = @categoryID*/))
			begin
				begin tran insertItems
				declare @id int
				select @id=isnull(max(itm_Itemid),0)+1 from tbl_Items
				insert into tbl_Items 
				(
					itm_Itemid,ctg_Categoryid,stp_Storeid,itm_ItemCode,itm_ItemName,itm_OpeningQty,uom_Uomid
					,item_ItemValue,itm_Description,itm_Status
				)
				values
				(
					@id,@categoryID,@storeID,@itemCode,@itemName,@qty,@uomID,@itemValue,@desc,@status
				)
					if @@error > 0
					begin 
						rollback tran insertItems
					end
				commit tran insertItems
				set @returnValue = 1
				return @returnValue
			end
			else
			begin
				set @returnValue = 3
				return @returnValue
			end	
		end
		else 
		begin
			set @returnValue = -2
			return @returnValue
		end
	end
	else
	begin
		set @returnValue = -3
		return @returnValue
	end
end
else
begin
	set @returnValue = -4
	return @returnValue
end



I will be thankful if any one can help

Thanks in Advance
Randheer kumar
Posted
Updated 31-May-11 2:59am
v6

You're trying to insert data into your table that breaks the Foreign Key constraint you have set up

The table you are inserting into is tbl_Items and the key you are breaking is the data going into field stp_Storeid.

You need to have a look at the value going into variable @storeID, this needs to exist in table tbl_StoreProfile since you have defined a relationship there.

Attach SQL profiler and watch for the value being sent to the procedure, or add some Debug statements to your procedure - whatever is easiest in your situation
   
Comments
randheer kumar 1-Jun-11 1:20am
   
thanks for the reply. i solved this issue.actually there is no value passing to @storeid.Thanks for the reply once again.
Your Primary Key should not be an Autonumber.
   

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

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900