Reputation: 391
Project background: working with MS-Access 2010 user front end and SQL Server 2008 back end that stores the database. I write stored procedures in SQL and run them on the SQL Server then call them in VBA from Access.
When a button is pressed after retrieving the values needed and storing them to the appropriate variable, I call my stored procedure and pass it the needed parameters. The desired effect is for those values to be stored in the table tblFromICPMS
. But when I press the button I get the error:
-2147217873
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblFromICPMS_tblWOSampleTest". The conflict occurred in database "GSATest", table "dbo.tblWOSampleTest". in ImportICPMS
I have looked at people questions with a similar issue and most of the time the problem seems to lie in their stored procedure. I can't really figure out what is wrong with my stored procedure and what the conflict could be caused by.
Provided spec: In a spec I was given, I was provided with SQL code to make a table (TblFromICPMS
) with foreign keys.
Here is the script for that:
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo]. [FK_tblFromICPMS_tblWOSampleTest]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblFromICPMS]'))
ALTER TABLE [dbo].[tblFromICPMS]
DROP CONSTRAINT [FK_tblFromICPMS_tblWOSampleTest]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DF_tblFromICPMS_DateImported]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[tblFromICPMS]
DROP CONSTRAINT [DF_tblFromICPMS_DateImported]
END
GO
/****** Object: Table [dbo].[tblFromICPMS] Script Date: 5/14/2014 9:29:33 AM ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblFromICPMS]') AND type in (N'U'))
DROP TABLE [dbo].[tblFromICPMS]
GO
/****** Object: Table [dbo].[tblFromICPMS] Script Date: 5/14/2014 9:29:33 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblFromICPMS]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblFromICPMS]
(
[FromICPMSID] [int] IDENTITY(1,1) NOT NULL,
[Sample Name] [nvarchar](255) NULL,
[Date And Time Acquired] [nvarchar](255) NULL,
[Analyte] [nvarchar](20) NULL,
[Concentration] [nvarchar](255) NULL,
[Units] [nvarchar](255) NULL,
[DateImported] [datetime] NOT NULL,
[DateAnalyzed] [datetime] NULL,
[BatchID] [int] NOT NULL,
[QueueID] [int] NULL,
[WOID] [nvarchar](10) NULL,
[SampleID] [int] NULL,
[TestID] [int] NULL,
[ActResult] [float] NULL,
[Issue] [nvarchar](255) NULL,
CONSTRAINT [PK_tblFromICPMS] PRIMARY KEY CLUSTERED ([FromICPMSID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo]. [DF_tblFromICPMS_DateImported]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[tblFromICPMS]
ADD CONSTRAINT [DF_tblFromICPMS_DateImported]
DEFAULT (getdate()) FOR [DateImported]
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo]. [FK_tblFromICPMS_tblWOSampleTest]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblFromICPMS]'))
ALTER TABLE [dbo].[tblFromICPMS] WITH CHECK
ADD CONSTRAINT [FK_tblFromICPMS_tblWOSampleTest]
FOREIGN KEY([WOID], [SampleID], [TestID])
REFERENCES [dbo].[tblWOSampleTest] ([WOID], [SampleID], [TestID])
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblFromICPMS_tblWOSampleTest]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblFromICPMS]'))
ALTER TABLE [dbo].[tblFromICPMS] CHECK CONSTRAINT [FK_tblFromICPMS_tblWOSampleTest]
GO
And here is the script for my stored procedure:
CREATE PROCEDURE upInsertFromICPMSRecord
@SampleName nvarchar(60),
@DateTimeAcquired nvarchar(60),
@Analyte nvarchar(60),
@Concentration nvarchar(60),
@QueueID nvarchar(60),
@Units nvarchar(60),
@ThisWOID nvarchar(60),
@ThisSampleID nvarchar(60),
@ThisTestID nvarchar(60),
@ActResult nvarchar(60),
@Issue nvarchar(60),
@BatchID nvarchar(60)
AS
INSERT INTO tblFromICPMS ("Sample Name","Date And TimeAcquired",Analyte,concentration,Units,DateImported,DateAnalyzed,BatchID, QueueID, WOID, SampleID, TestID, ActResult, Issue)
VALUES (@SampleName, @DateTimeAcquired, @Analyte, @Concentration,
@Units, getdate(),
CONVERT(datetime, @DateTimeAcquired), @BatchID, @QueueID,
@ThisWOID, @ThisSampleID, @ThisTestID, @ActResult, @Issue);
GO
For some additional background, another stored procedure I was required to make was GetTestIdForAnalyte
which basically tested to see if a TESTID
exists for the particular file being processed. It has an output parameter that stores the number of rows affected. Therefore if it is greater than zero I know a TestID
exists. I am working on a test database and the files I am working on are not in the system so the testID
is never going to exist. I don't know if this is causing the issue, but here is the stored procedure for that.
CREATE PROCEDURE upGetTestIDForAnalyte
@WOID nvarchar(60),
@SampleID nvarchar(60),
@Analyte nvarchar(60),
@RecordsAffected int OUT
AS
SELECT
TestID = t1.TestID
FROM
tblWOSampleTest t1
JOIN
tblTest t2 ON t1.TestID = t2.TestID;
WHERE
@WOID = t1.WOID
AND @SampleID = t1.SampleID
AND @Analyte = t2.Analyte
SET @RecordsAffected = @@ROWCOUNT
GO
If there is a TestID
then I can continue on and insert the records by calling my other stored procedure, else I insert an error message into an error log. I just kind of ignored that for the sake of testing because I thought It wouldn't matter.
Upvotes: 2
Views: 2388
Reputation: 8729
This is basically just the constraint telling you that you're trying to insert a record into tblFromICPMS
that does not have a matching record in tblWOSampleTest
with the same WOID
, SampleID
, and TestID
.
With the constraint in place, you MUST have a record in tblWOSampleTest
with those values BEFORE you can insert them into tblFromICPMS
- thats what a foesign key constraint is..
http://www.w3schools.com/sql/sql_foreignkey.asp
Upvotes: 1