SP insert error : Conflict with foreign Key

Question

I know there are several questions related to this, but I trued them it did not work.

I have an sp to insert datas into a table, the table has foreign keys as well.

This is the structure of my table that I want to insert,

Iam using asp.net and my sp looks like below

ALTER PROCEDURE [dbo].[spInsertJob]
@CompanyID INT,
@DepartmentID INT,
@No VARCHAR(50),
@Date DATETIME,
@CustomerID INT,
@JobTypeID INT,
@BillNo VARCHAR(50),
@GoodsType VARCHAR(50),
@Remarks VARCHAR(250),
@Cancelled BIT,
@CancelledRemarks VARCHAR(50),
@UserId INT,
@Closed BIT,
@Shipper VARCHAR(100),
@SupplierInvoice VARCHAR(50),
@HBillNo VARCHAR(50),
@JobStartedDate DATETIME,
@AssignedStaffid INT,
@VesselorFlightName VARCHAR(100),
@VesselorFlightArvDate DATETIME,
@FormNumber VARCHAR(50),
@FinancialId INT,
@NoofPackages INT,
@TypeOfPackages VARCHAR(100),
@ChargableWeight VARCHAR(50),
@Volume VARCHAR(50),
@DispatchMode VARCHAR(500),
@TransactionId INT OUT,
    @TransactionNo NVARCHAR(50) OUT

AS
BEGIN
     SET NOCOUNT ON;
     DECLARE @Id AS INT
       SELECT @Id=isnull(max(Id),0)+1 FROM  Job

 INSERT INTO Job( Id, CompanyID, DepartmentID, No, Date, CustomerID, JobTypeID, BillNo, GoodsType, Remarks, Cancelled, CancelledRemarks, UserId, Closed, Shipper, SupplierInvoiceNo, HBillNo, JobStartedDate, AssignedStaffid, VesselorFlightName, VesselorFlightArvDate, FormNumber,  NoOfPackages, TypeOfPackages, ChargableWeight, Volume, DispatchMode) 

values(@Id,@CompanyID,@DepartmentID,@No,@Date,@CustomerID,@JobTypeID,@BillNo,@GoodsType,@Remarks,@Cancelled,@CancelledRemarks,@UserId,@Closed,@Shipper,@SupplierInvoice,@HBillNo,@JobStartedDate,@AssignedStaffid,@VesselorFlightName,@VesselorFlightArvDate,   @FormNumber,@NoofPackages, @TypeOfPackages, @ChargableWeight, @Volume, @DispatchMode)

SELECT @TransactionId = @Id
SELECT @TransactionNo = @No 
SET NOCOUNT OFF;
END

I got error

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Job_JobType". The conflict occurred in database "dbname", table "dbo.JobType".
The statement has been terminated.

this is my JobType table

so I tried to insert like this and error remains

INSERT INTO Job (Id,CompanyID,DepartmentID,No,Date,CustomerID,JobTypeID,BillNo,GoodsType,Remarks,Cancelled,CancelledRemarks,UserId,Closed,Shipper,SupplierInvoiceNo,HBillNo,JobStartedDate,AssignedStaffid,VesselorFlightName,VesselorFlightArvDate,FormNumber,NoOfPackages,TypeOfPackages,ChargableWeight,Volume,DispatchMode)

VALUES (88,1,0,'test',2018-01-01,13,1,3246,'','',0,'',2,0,'','',234632,2018-01-01,1,'test',2018-01-01,'',1,0,'','0','');

Possible duplicate of [INSERT statement conflicted with the FOREIGN KEY constraint](https://stackoverflow.com/questions/2965837/insert-statement-conflicted-with-the-foreign-key-constraint) — VDWWD, Mar 15 '18 at 11:03
What's the value of `@JobTypeID`, and does it exist in `dbo.JobType` (I'm assuming the FK relationship, can you show the FK?) — HoneyBadger, Mar 15 '18 at 11:05
The value of `JobTypeID` of `1` does not exist in the `JobType` table. — VDWWD, Mar 15 '18 at 11:05
it is a foreign key constraint, May be you are inserting a value for job_id which is not present in Parent table. — Sas, Mar 15 '18 at 11:06
https://stackoverflow.com/questions/2965837/insert-statement-conflicted-with-the-foreign-key-constraint check this link if it helps — Sas, Mar 15 '18 at 11:12

score 0 · Answer 1 · answered Mar 15 '18 at 11:13

0

In your insert query, the JobTypeID is being inserted as ''. If it is a foreign key, you cannot insert a '' into it. Enter a valid Id into it

answered Mar 15 '18 at 11:13

Praneet Nadkar

823
7
16

I am inserting 1 not " – Mar 15 '18 at 11:29

SP insert error : Conflict with foreign Key

1 Answers1