将数据插入表中的sql异常 [英] sql exception in inserting data to table

问题描述

你好朋友
将数据插入表时出现以下异常

Hi friends
The following exception occur when inserting data to table

<pre>The INSERT statement conflicted with the FOREIGN KEY constraint "FK_PermenentAddress_details_StudentAdmission_details". The conflict occurred in database "School", table "dbo.StudentAdmission_details", column 'AdmissionNumber'.

我的存储过程是

and my stored procedure is

create procedure [dbo].[sp_studadmissioninsert](@admissionnumber int,@admissiondate datetime,@fromyear smallint,@toyear smallint,@classname varchar(30),@studname varchar(50),@dob datetime,@gender char(1),@age smallint,@fathername varchar(50),@fatheroccupation varchar(50),@mothername varchar(50),@motheroccupation varchar(50),@medium varchar(25),@studenttype varchar(25),@identimark1 varchar(300),@identimark2 varchar(300),@religion varchar(15),@cast varchar(50),@castcategory varchar(10),@phonenumber varchar(20),@email varchar(80),@previousschool varchar(max),@needtransport char(1),   @street varchar(200),@city varchar(50),@state varchar(50),@country varchar(50),@zipcode varchar(50),@street1 varchar(200),@city1 varchar(50),@state1 varchar(50),@country1 varchar(50),@zipcode1 varchar(15),@datetime datetime,@adminid int,@applicationnumber int )
as
begin
declare @cassid int,@academicid int
select @academicid=Acadamic_year_id from Acadamic_year where Acadamic_year_from=@fromyear and Acadamic_year_to=@toyear
select @cassid=Standered_id from Standered_details where Acadamic_year_id=(select Acadamic_year_id from Acadamic_year where Acadamic_year_from=@fromyear and Acadamic_year_to=@toyear)and Standered_name=@classname

if exists (select  StudentName from StudentApplication_details where ApplicationNumber=@applicationnumber)
begin
update StudentAdmission_details set Admissiondate=@admissiondate,Academic_year_id=@academicid,ClassId=@cassid,StudentName=@studname,Age=@age,FatherOccupation=@fatheroccupation,MotherName=@mothername,MotherOccupation=@motheroccupation,Medium=@medium,StudentType=@studenttype,IdentificationMark1=@identimark1,IdentificationMark2=@identimark2,Religion=@religion,Caste=@cast,CastCategory=@castcategory,Phone_number=@phonenumber,EmailId=@email,Previous_School=@previousschool,Transport_status=@needtransport,Updateddatetime=@datetime,adminid=@adminid where AdmissionNumber=@admissionnumber and ApplicationNumber=@applicationnumber
end
else
begin
--declare @cassid int,@academicid int
--select @academicid=Acadamic_year_id from Acadamic_year where Acadamic_year_from=@fromyear and Acadamic_year_to=@toyear
--select @cassid=Standered_id from Standered_details where Acadamic_year_id=(select Acadamic_year_id from Acadamic_year where Acadamic_year_from=@fromyear and Acadamic_year_to=@toyear)and Standered_name=@classname
insert into StudentAdmission_details(AdmissionNumber ,ApplicationNumber ,Admissiondate,Academic_year_id,ClassId,StudentName,DOB,Gender,Age,FatherName,FatherOccupation,MotherName,MotherOccupation,Medium,StudentType,IdentificationMark1,IdentificationMark2,Religion,Caste,CastCategory,Phone_number,EmailId,Previous_School,Transport_status,Createddatetime,adminid)values(@admissionnumber, @applicationnumber,@admissiondate,@academicid,@cassid,@studname,@dob,@gender,@age,@fathername,@fatheroccupation,@mothername,@motheroccupation,@medium,@studenttype,@identimark1,@identimark2,@religion,@cast,@castcategory,@phonenumber,@email,@previousschool,@needtransport,@datetime,@adminid)
end
if exists(select Street,City,State,Country,Zipcode from TemporaryAddress_details where AdmissionNumber=@admissionnumber and ApplicationNumber=@applicationnumber)
begin
update TemporaryAddress_details set Street=@street,City=@city,State=@state,Country=@country,Zipcode=@zipcode,Updateddatetime=@datetime,adminid=@adminid where AdmissionNumber=@admissionnumber and ApplicationNumber=@applicationnumber
end
else
begin
insert into TemporaryAddress_details(Street,City,State,Country,Zipcode,Createddatetime,adminid,ApplicationNumber,AdmissionNumber)values(@street,@city,@state,@country,@zipcode,@datetime,@adminid,@applicationnumber,@admissionnumber)
end
if exists(select Street,City,State,Country,Zipcode from PermenentAddress_details where StudentApplicationNumber=@applicationnumber and StudentAdmissionNumber=@admissionnumber )
begin
update PermenentAddress_details set Street=@street,City=@city,State=@state,Country=@country,Zipcode=@zipcode,Updateddatetime=@datetime,adminid=@adminid where StudentApplicationNumber=@applicationnumber and StudentAdmissionNumber=@admissionnumber
end
else
begin
insert into PermenentAddress_details(Street,City,State,Country,Zipcode,StudentApplicationNumber,StudentAdmissionNumber,Createddatetime,adminid)values(@street,@city,@state,@country,@zipcode,@applicationnumber,@admissionnumber,@datetime,@adminid)
end
end

在studentAdmission_details中，"AdmissionNumber是主键，应用程序号，Acadamic_year_id是外键.过程已成功编译.但是插入失败.这可能是我的表上的问题.

In studentAdmission_details "AdmissionNumber is primarykey and Application Number,Acadamic_year_id is foreign key.procedure successfully compiled. but insertion is failed. what could be the problem on my table.

推荐答案

它正好说明了它的含义.您正在尝试将一个值插入到具有FK约束的列中，该列与查找表中的任何值都不匹配.

在表dbo.StudentAdmission_details中，它具有对另一个表的外键引用. FK 的工作方式是，在该列中不能有值，在引用表的主键列中也不能有值.

--Amit

It means exactly what it says. You''re trying to insert a value into a column that has a FK constraint on it that doesn''t match any values in the lookup table.

In your table dbo.StudentAdmission_details, it has a foreign key reference to another table. The way a FK works is it cannot have a value in that column that is not also in the primary key column of the referenced table.

--Amit

这篇关于将数据插入表中的sql异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！