question

abhimanyu avatar image
abhimanyu asked

Stored Proc Insert fails

Hi

When I excute my proc I get an error

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ContactInformation_EndUser_iContactInformationID". The conflict occurred in database "MSC-UFCFUD-60-M", table "dbo.Tbl_ContactInformation", column 'ipknContactInformationID'.

My understanding is that if there is no data in the parent table, I will not be able to insert data in to child table. However, my parent table has a row of data, it is fetched by the code, but when the insert statement is executed I get the above error.

My code is below. EndUser Table has three foreign keys. Execution first two insert statement works fine. but the third insert statement fails when Inserting into Tbl_EndUser. The value returned for @ifknEndUserTypeID =1 which is a valid value. I have put the code in bold that fails.

USE [MSC-UFCFUD-60-M]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('sp_EndUser_Insert'))
BEGIN
DROP PROCEDURE [dbo].[sp_EndUser_Insert]
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_EndUser_Insert]
@zEndUserType NVARCHAR(50)
,@zFirstName NVARCHAR(50)
,@zLastName NVARCHAR(50)
,@zPhone NVARCHAR(30)
,@zMobile NVARCHAR(30)
,@zFax NVARCHAR(30)
,@zTitle NVARCHAR(10)
,@zGender NVARCHAR(1)
,@dtDOB DATE
,@iAge INT
,@iAddressID INT
,@iContactInformationID INT
,@zPassword NVARCHAR(20)
,@zEMail NVARCHAR(100)
,@zEndUserImage NVARCHAR(100)
,@uidActivationCode UNIQUEIDENTIFIER
,@bUserActivated BIT
,@dteAccountCreationDate DATETIME
,@dteLastLoginDate DATETIME
,@zAddressLine1 NVARCHAR(50)
,@zAddressLine2 NVARCHAR(50)
,@zCity NVARCHAR(50)
,@zPostCode NVARCHAR(10)
AS
BEGIN


-- 1. INSERT THE ADDRESS
BEGIN TRANSACTION

DECLARE @ipAddressID INT
DECLARE @ipContactInformationID INT
DECLARE @ifknEndUserTypeID INT

INSERT INTO Tbl_Address (
zAddressLine1
,zAddressLine2
,zCity
,zPostCode
)
VALUES (
@zAddressLine1
,@zAddressLine2
,@zCity
,@zPostCode
)

SET @ipAddressID = @@IDENTITY


-- TRANSACTION NEEDS TO BE ROLLED BACK IN CASE OF ERROES

IF @@ERROR <> 0
BEGIN

ROLLBACK

-- RAISE AN ERROR AN RETURN
RAISERROR ('Error INSERT INTO Tbl_Address.', 16, 1)

RETURN

END


-- INSERT INFORMATION INTO Tbl_ContactInformation
INSERT INTO Tbl_ContactInformation
(
zPhone
,zMobile
,zFax
,zEMail
)
VALUES
(
@zPhone
,@zMobile
,@zFax
,@zEMail
)

SET @ipContactInformationID = @@IDENTITY


/*
TRANSACTION NEEDS TO BE ROLLED BACK IN CASE OF ERROES
*/
IF @@ERROR <> 0
BEGIN

ROLLBACK

-- RAISE AN ERROR AND RETURN
RAISERROR ('Error INSERT INTO Tbl_ContactInformation.', 16, 1)
RETURN

END



/*
Retrieve EndUserTypeID from the EndUserType table for the selected endusertype
*/



SELECT
@ifknEndUserTypeID = ipknEndUserTypeID
FROM
Tbl_EndUserType
WHERE
zEndUserType = @zEndUserType

/*
INSERT INFORMATION INTO Tbl_EndUser
*/

INSERT INTO Tbl_EndUser
(

ifknEndUserTypeID
,zTitle
,zFirstName
,zLastName
,zGender
,dtDOB
,iAge
,iAddressID
,iContactInformationID
,zPassword
,zEMail
,zEndUserImage
,uidActivationCode
,bUserActivated
,dteAccountCreationDate

)
VALUES
(

@ifknEndUserTypeID
,@zTitle
,@zFirstName
,@zLastName
,@zGender
,@dtDOB
,@iAge
,@iAddressID
,@iContactInformationID
,@zPassword
,@zEMail
,@zEndUserImage
,@uidActivationCode
,0
,getdate()
)

END

stored procedures
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

erlokeshsharma08 avatar image
erlokeshsharma08 answered

Check the foreign key "FK_ContactInformation_EndUser_iContactInformationID".

I think it refers to a table dbo.Tbl_ContactInformation.

And then check whether value of @iContactInformationID is there in the table or not.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WRBI avatar image
WRBI answered

My first day back from a holiday, so I might be a bit rusty:

You do:

DECLARE @ipContactInformationID INT
SET @ipContactInformationID = @@IDENTITY

Then in your insert you do:

,@iContactInformationID

Does this need to be @ipContactInformationID? The lower case p is missing.

Regards,

Dave.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.