数据未保存在数据库中 [英] data not saving in database

问题描述

大家好，
我正在尝试使用存储过程将数据保存在表中.在保存数据的同时，我收到一条消息，提示

错误消息:
INSERT语句与COLUMN FOREIGN KEY约束"FK_tbl_Items_tbl_StoreProfile"冲突.在数据库"CSTORESDB"的表"tbl_StoreProfile"的列"stp_Storeid"中发生了冲突.
COMMIT TRANSACTION请求没有相应的BEGIN TRANSACTION.
该声明已终止.


存放程序:

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:

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

如果有人可以帮忙我会很感激

在此先感谢
Randheer kumar

I will be thankful if any one can help

Thanks in Advance
Randheer kumar

推荐答案

您正在尝试将数据插入表中，从而打破了已设置的外键约束

您要插入的表是tbl_Items，而要破坏的键是进入字段stp_Storeid的数据.

您需要查看进入变量@storeID的值，该值必须存在于表tbl_StoreProfile中，因为您已在其中定义了关系.

附加SQL事件探查器，并监视要发送到过程的值，或在过程中添加一些Debug语句-在您遇到的情况中最简单的方法

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

您的主键不应是自动编号.

Your Primary Key should not be an Autonumber.

这篇关于数据未保存在数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！