24
{System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ProjectResponse__ProjectQuestionId". The conflict occurred in database "RG_ProjectData", table "dbo.ProjectQuestion", column 'ProjectQuestionId'.
The statement has been terminated.
public void ProjectResponseandRespondentToDB(DataRow dr, string contents, int iProjectQuestionID, int iAnswerNumber)
{
// Create the command object and set the SQL statement
SqlCommand cmd = new SqlCommand("prAddProjectResponseandRespondent", conn);
cmd.CommandType = CommandType.StoredProcedure;
// Values used in the first insert
cmd.Parameters.Add("@ProjectRespondentVcId", SqlDbType.NVarChar);
cmd.Parameters["@ProjectRespondentVcId"].Value = dr[0];
// Values used in the first insert
cmd.Parameters.Add("@ProjectRespondentCompletionDa te", SqlDbType.DateTime);
cmd.Parameters["@ProjectRespondentCompletionDate"].Value = DateTime.ParseExact(dr[1].ToString(), "yyyyMMdd", new System.Globalization.CultureInfo("en-US"));
cmd.Parameters.Add("@ProjectResponseMention", SqlDbType.Int);
cmd.Parameters["@ProjectResponseMention"].Value = 1;
// Values used in the second insert
cmd.Parameters.Add("@ProjectQuestionId", SqlDbType.Int);
cmd.Parameters["@ProjectQuestionId"].Value = iProjectQuestionID;
cmd.Parameters.Add("@ProjectResponseContent", SqlDbType.NVarChar);
cmd.Parameters["@ProjectResponseContent"].Value = contents;
cmd.Parameters.Add("@ProjectResponseOpenEndContent ", SqlDbType.NText);
cmd.Parameters["@ProjectResponseOpenEndContent"].Value = DBNull.Value;
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
And here is my stored proc:
DECLARE @SQL4 NVARCHAR(4000)
SET @SQL4 = N'EXECUTE ' + @nvcDatabaseName + N'.dbo.sp_executesql N''
CREATE Procedure [dbo].[prAddProjectResponseandRespondent]
@ProjectRespondentVcId nvarchar(255),
@ProjectRespondentCompletionDate datetime,
@ProjectQuestionId int,
@ProjectResponseMention int ,
@ProjectResponseContent nvarchar(255) ,
@ProjectResponseOpenEndContent ntext
AS
declare @ProjectRespondentId int
SET NOCOUNT ON
INSERT INTO [ProjectRespondent] (
ProjectRespondentVcId,
ProjectRespondentCompletionDate)
VALUES (
@ProjectRespondentVcId,
@ProjectRespondentCompletionDate)
SELECT @ProjectRespondentId = SCOPE_IDENTITY()
INSERT INTO [RG_ProjectData].[dbo].[ProjectResponse]
([ProjectRespondentId]
,[ProjectQuestionId]
,[ProjectResponseMention]
,[ProjectResponseContent]
,[ProjectResponseOpenEndContent])
VALUES
(@ProjectRespondentId,
@ProjectQuestionId,
@ProjectResponseMention,
@ProjectResponseContent,
@ProjectResponseOpenEndContent)'''
EXECUTE (@SQL4)
