FOREIGN KEY约束问题，即使值已经存在
本文关键字：存在 KEY 约束 问题 FOREIGN | 更新日期: 2023-09-27 18:28:48
在这里发帖之前，我已经研究了各种解决方案，这是我的最后手段。我似乎根本搞不清代码是怎么回事。该值将插入到1个表中，但当我插入到另一个表中时，它会给出错误"insert语句与FOREIGN KEY约束"FK_QuizAnswer_ToQuiz"冲突"。
当我插入它时，这个值已经存在于数据库中，我知道这一点，因为我已经测试过从数据库中检索值并将其分配给标签，即使我这样做，我也会得到同样的错误。请帮忙。
ASPX页面
<asp:MultiView ID="MultiView1" ActiveViewIndex="0" runat="server">
<asp:View ID="View1" runat="server">
<table>
<tr>
<td>
<asp:Label ID="QuizName" runat="server" Text="Quiz Name: "></asp:Label>
</td>
<td>
<asp:TextBox ID="QuizNameTextBox" Placeholder="Enter Quiz Name" runat="server"></asp:TextBox>
<asp:Label ID="nameerror" runat="server" ForeColor="Red" Visible="false" Text="The quiz name already exists, please create another."></asp:Label>
<asp:RequiredFieldValidator ID="QuizNameRequired" ForeColor="Red" Display="Dynamic" ControlToValidate="QuizNameTextBox" runat="server" ErrorMessage="The quiz name is required."></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td>
<asp:Label ID="ForCourse" runat="server" Text="For Course: "></asp:Label>
</td>
<td>
<asp:DropDownList ID="DropDownList1" runat="server">
<asp:ListItem value="1">Course51531</asp:ListItem>
<asp:ListItem value="7">Course6</asp:ListItem>
<asp:ListItem value="9">Course8</asp:ListItem>
<asp:ListItem value="12">Course 11</asp:ListItem>
<asp:ListItem value="35">Course 1sdwd</asp:ListItem>
<asp:ListItem value="36">ewfwef</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td>
<asp:Label ID="Reattempt" runat="server" Text="Can this be re-attempted?: "></asp:Label>
</td>
<td>
<asp:RadioButtonList AutoPostBack="true" ID="Reattemptradio" runat="server">
<asp:ListItem Text="Yes" Value="Yes"></asp:ListItem>
<asp:ListItem Text="No" Value="No"></asp:ListItem>
</asp:RadioButtonList>
<asp:RequiredFieldValidator ID="RequiredFieldValidator6" ControlToValidate="Reattemptradio" Display="Dynamic" ForeColor="Red" runat="server" ErrorMessage="Please select a value."></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td>
<asp:Label ID="Reattemp1" Visible="false" runat="server" Text="How many times?: "></asp:Label>
</td>
<td>
<asp:TextBox ID="reattemptbox1" Visible="false" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td></td>
<td>
<asp:Button ID="Continue" CommandName="SwitchViewByID" CommandArgument="View2" ToolTip="Click here to create questions for the quiz." runat="server" Text="Continue" OnClick="Continue_Click" />
</td>
</tr>
</table>
</asp:View>
<asp:View ID="View2" runat="server">
<table>
<tr>
<td>
<asp:Label ID="Label1" runat="server" Text="Question No: "></asp:Label>
</td>
<td>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
<asp:Label ID="Label2" runat="server" Text="Question Name"></asp:Label>
</td>
<td>
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
<asp:Label ID="Label3" runat="server" Text="Question Type:"></asp:Label>
</td>
<td>
<asp:DropDownList ID="DropDownList2" runat="server">
<asp:ListItem Text="---Select---" Value="---Select---"></asp:ListItem>
<asp:ListItem Text="Drop Down Menu" Value="Drop Down Menu"></asp:ListItem>
<asp:ListItem Text="Radio Buttons" Value="Radio Buttons"></asp:ListItem>
<asp:ListItem Text="Check Box" Value="Check Box"></asp:ListItem>
<asp:ListItem Text="TextBox" Value="TextBox"></asp:ListItem>
<asp:ListItem Text="Multi Line Response" Value="Multi Line Response"></asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td>
<asp:Label ID="Label4" runat="server" Text="Pass Mark: "></asp:Label>
</td>
<td>
<asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td> </td>
<td>
<asp:Button ID="Button1" CommandName="SwitchViewByID" CommandArgument="View3" OnClick="Button1_Click" runat="server" Text="Button" />
</td>
</tr>
</table>
</asp:View>
<asp:View ID="View3" runat="server">
<table>
<tr>
<td>
<asp:Label ID="ResponseLabel11" runat="server" Text="Enter another Response: "></asp:Label>
</td>
<td>
<asp:TextBox ID="ResponseTextBox11" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator5" ControlToValidate="ResponseTextBox11" Display="Dynamic" ForeColor="Red" runat="server" ErrorMessage="A response is required."></asp:RequiredFieldValidator>
</td>
<td>Note: The response in this textbox should be an answer.
</td>
</tr>
<tr>
<td>
<asp:Label ID="ResponseLabel12" runat="server" Text="Enter another Response: "></asp:Label>
</td>
<td>
<asp:TextBox ID="ResponseTextBox12" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator7" ControlToValidate="ResponseTextBox12" Display="Dynamic" ForeColor="Red" runat="server" ErrorMessage="Another response is required."></asp:RequiredFieldValidator>
</td>
<td>
<asp:CheckBox ID="Answer12" Text="Is Answer?" runat="server" />
</td>
</tr>
<tr>
<td></td>
<td>
<asp:Button ID="Button2" OnClick="Button2_Click" runat="server" Text="Button" />
</td>
</tr>
</table>
</asp:View>
</asp:MultiView>
c#文件
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class Default3 : System.Web.UI.Page
{
int quizid = 0;
int check = 0;
int questid = 0;
int counter = 1;
int count = 0;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Page_PreRender(object sender, EventArgs e)
{
}
protected void Continue_Click(object sender, EventArgs e)
{
DropDownList1.SelectedValue = DropDownList1.SelectedValue;
using (var connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "INSERT INTO [Quiz] ([QuizName], [CourseId], [AllowedAttempts]) VALUES (@QuizName, @CourseId, @AllowedAttempts)";
command.Parameters.AddWithValue("@QuizName", SqlDbType.NVarChar).Value = QuizNameTextBox.Text;
command.Parameters.AddWithValue("@CourseId", SqlDbType.Int).Value = Convert.ToInt32(DropDownList1.SelectedValue.ToString());
if (Reattemptradio.SelectedValue.ToString() == "Yes")
{
command.Parameters.AddWithValue("@AllowedAttempts", SqlDbType.Int).Value = Convert.ToInt32(reattemptbox1.Text);
command.ExecuteNonQuery();
}
else
{
command.Parameters.AddWithValue("@AllowedAttempts", SqlDbType.Int).Value = 0;
command.ExecuteNonQuery();
}
}
}
}
protected void Button1_Click(object sender, EventArgs e)
{
using (var connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT QuizId FROM Quiz WHERE (QuizName = '" + QuizNameTextBox.Text + "')";
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
quizid = Convert.ToInt32(reader["QuizId"]);
ViewState["quiz"] = quizid;
}
}
}
using (var command = connection.CreateCommand())
{
command.CommandText = "INSERT INTO [QuizQuestion] ([QuizId], [QuestionNo], [QuestionType], [QuestionName], [QuestionMark]) VALUES (@QuizId, @QuestionNo, @QuestionType, @QuestionName, @QuestionMark)";
command.Parameters.AddWithValue("@QuizId", SqlDbType.Int).Value = quizid;
command.Parameters.AddWithValue("@QuestionNo", SqlDbType.Int).Value = TextBox1.Text;
command.Parameters.AddWithValue("@QuestionName", SqlDbType.NVarChar).Value = TextBox2.Text;
command.Parameters.AddWithValue("@QuestionType", SqlDbType.NVarChar).Value = DropDownList2.SelectedValue.ToString();
command.Parameters.AddWithValue("@QuestionMark", SqlDbType.NVarChar).Value = TextBox4.Text;
command.ExecuteNonQuery();
}
}
}
protected void Button2_Click(object sender, EventArgs e)
{
ViewState["count"] = Convert.ToInt32(ViewState["count"]) + counter + 1;
using (var connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT QuizQuestionId FROM QuizQuestion WHERE (QuestionName = '" + TextBox2.Text + "')";
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
questid = Convert.ToInt32(reader["QuizQuestionId"].ToString());
}
}
}
using (var command = connection.CreateCommand())
{
command.CommandText = "INSERT INTO [QuizAnswer] ([QuizId], [QuizQuestionId], [Response], [Sequence], [CorrectResponse]) VALUES (@QuizId, @QuizQuestionId, @Response, @Sequence, @CorrectResponse)";
command.Parameters.AddWithValue("@QuizId", SqlDbType.Int).Value = Convert.ToInt32(ViewState["quiz"]);
command.Parameters.AddWithValue("@QuizQuestionId", SqlDbType.Int).Value = Convert.ToInt32(questid);
command.Parameters.AddWithValue("@Response", SqlDbType.NVarChar).Value = ResponseTextBox11.Text;
command.Parameters.AddWithValue("@Sequence", SqlDbType.Int).Value = count;
command.Parameters.AddWithValue("@CorrectResponse", SqlDbType.Bit).Value = Convert.ToBoolean("True");
command.ExecuteNonQuery();
}
}
}}
测验答案表
CREATE TABLE [dbo].[QuizAnswer]
(
[QuizAnswerId] INT IDENTITY (1, 1) NOT NULL,
[QuizQuestionId] INT NOT NULL,
[QuizId] INT NOT NULL,
[Response] NVARCHAR (MAX) NOT NULL,
[Sequence] INT NOT NULL,
[CorrectResponse] BIT DEFAULT ((0)) NOT NULL,
PRIMARY KEY CLUSTERED ([QuizAnswerId] ASC),
CONSTRAINT [FK_QuizAnswer_ToQuizQuestion]
FOREIGN KEY ([QuizQuestionId]) REFERENCES [dbo].[QuizQuestion] ([QuizQuestionId]),
CONSTRAINT [FK_QuizAnswer_ToQuiz]
FOREIGN KEY ([QuizId]) REFERENCES [dbo].[Quiz] ([QuizId])
);
测验表
CREATE TABLE [dbo].[Quiz]
(
[QuizId] INT IDENTITY (1, 1) NOT NULL,
[QuizName] NVARCHAR (50) NOT NULL,
[CourseId] INT NOT NULL,
[TotalMarks] INT DEFAULT ((1)) NULL,
[PassMarks] INT NULL,
[AllowedAttempts] INT DEFAULT ((0)) NOT NULL,
[IsActive] BIT DEFAULT ((0)) NOT NULL,
PRIMARY KEY CLUSTERED ([QuizId] ASC),
CONSTRAINT [FK_Quiz_ToCourse]
FOREIGN KEY ([CourseId]) REFERENCES [dbo].[Course] ([CourseId])
);
当您试图在Button2_Click事件中将答案存储到QuizAnswer表中时，看起来您正在从ViewState:中提取QuizId
command.Parameters.AddWithValue("@QuizId", SqlDbType.Int).Value = Convert.ToInt32(ViewState["quiz"]);
您可能需要在这一点上设置一个断点，并确保QuizId值实际上正确返回，或者在回发过程中丢失。
问题很可能存在于以下行中：
command.Parameters.AddWithValue("@QuizId", SqlDbType.Int).Value = Convert.ToInt32(ViewState["quiz"]);
在这一行之后放置一个断点，查看您的参数值也被分配了什么，或者运行一个sql探查器，查看实际传递了什么。
您可能没有正确地将quizId分配给ViewState["quiz"]
您可以使用Button2的CommandArgument属性将QuizID附加到它。
<asp:Button ID="Button2" OnCommand="Button2_Click" runat="server" Text="Button" />
然后方法看起来是这样的：
protected void Button2_Click(object sender, CommandEventArgs e)
在Button1_Click中，您可以这样分配quizId：
Button2.CommandArgument = quizid;
一般来说，问题的一个原因可能是ViewState受到在视图之间切换的代码的影响。每次视图更改时，都会有一次回发。这可能解释了ViewState不再具有该值的原因。