无法将值NULL插入列'；StaffID'；，桌子

本文关键字：桌子 StaffID 插入列 NULL | 更新日期: 2023-09-27 17:59:30

我正在使用查询生成器插入LeaveApplication表。据推测，当我点击申请休假时，他们应该会收到一条消息"申请成功"。相反，我得到了这样的错误："无法将值NULL插入表的‘StaffID’列；列不允许为NULL。insert失败。语句已终止"。修复错误可能与我的查询生成器有关。但我不知道如何修复它。我会在下面附上我的代码和错误的图片。

我的输出：

点击图像

LeaveApplication数据库表：

CREATE TABLE [dbo].[LeaveApplications] (
    [Id]                 INT          IDENTITY (1, 1) NOT NULL,
    [StaffId]            INT          NOT NULL,
    [LeaveTypeId]        INT          NOT NULL,
    [StartDate]          VARCHAR (10) NOT NULL,
    [StartLeavePeriodId] INT          NOT NULL,
    [EndDate]            VARCHAR (10) NOT NULL,
    [EndLeavePeriodId]   INT          NOT NULL,
    [NumDays]            FLOAT (53)   NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_LeaveApplications_ToStaff] FOREIGN KEY ([StaffId]) REFERENCES [dbo].[Staff] ([StaffId]),
    CONSTRAINT [FK_LeaveApplications_ToLeaveType] FOREIGN KEY ([LeaveTypeId]) REFERENCES [dbo].[LeaveType] ([Id]),
    CONSTRAINT [FK_LeaveApplications_ToStartLeavePeriod] FOREIGN KEY ([StartLeavePeriodId]) REFERENCES [dbo].[LeavePeriod] ([LeavePeriodId]),
    CONSTRAINT [FK_LeaveApplications_ToEndLeavePeriod] FOREIGN KEY ([EndLeavePeriodId]) REFERENCES [dbo].[LeavePeriod] ([LeavePeriodId])
);

ApplyLeave.aspx编码：

<%@ Page Title="" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="ApplyLeave.aspx.cs" Inherits="BookReservation.ApplyLeave" %>
<%@ Register assembly="AjaxControlToolkit" namespace="AjaxControlToolkit" tagprefix="ajaxToolkit" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContentPlaceHolder" runat="server">
    <asp:ScriptManager ID="ScriptManager1" runat="server">
    </asp:ScriptManager>
    <h3>Leave Application</h3>
    <asp:Label ID="lblLeaveType" runat="server" CssClass="labelClass" Text="Leave Type:"></asp:Label>
    <asp:DropDownList ID="ddlLeaveType" runat="server" Width="200px" DataSourceID="SqlDSLeaveType" DataTextField="Description" DataValueField="Id">
    </asp:DropDownList>
    <br />
    <asp:Label ID="Label1" runat="server" CssClass="labelClass" Text="Start Date:"></asp:Label>
    <asp:TextBox ID="tbStartDate" runat="server" Width="200px"></asp:TextBox>
    <ajaxToolkit:CalendarExtender ID="CalExtStartDate" runat="server" TargetControlID="tbStartDate" />
    <br />
    <asp:Label ID="lblStartLeavePeriod" runat="server" CssClass="labelClass" Text="Start Leave Period:"></asp:Label>
    <asp:DropDownList ID="ddlStartLeavePeriod" runat="server" Width="110px" DataSourceID="SqlDSStartLeavePeriod" DataTextField="Description" DataValueField="LeavePeriodId">
    </asp:DropDownList>
    <br />
    <asp:Label ID="lblEndDate" runat="server" CssClass="labelClass" Text="End Date:"></asp:Label>
    <asp:TextBox ID="tbEndDate" runat="server" Width="200px"></asp:TextBox>
    <ajaxToolkit:CalendarExtender ID="CalExtEndDate" runat="server" TargetControlID="tbEndDate" />
    <asp:CompareValidator ID="cvEndDate" runat="server" ErrorMessage="Select a date" ControlToCompare="tbStartDate" ControlToValidate="tbEndDate" ForeColor="Red"></asp:CompareValidator>
    <br />
    <asp:Label ID="lblEndLeavePeriod" runat="server" CssClass="labelClass" Text="End Leave Period:"></asp:Label>
    <asp:DropDownList ID="ddlEndLeavePeriod" runat="server" Width="110px" DataSourceID="SqlDSEndLeavePeriod" DataTextField="Description" DataValueField="LeavePeriodId">
    </asp:DropDownList>
    <br />
    <br />
    <asp:Button ID="btnApply" runat="server" Text="Apply Leave" OnClick="btnApply_Click" />
    <br />
    <br />
    <asp:Label ID="lblOutput" runat="server"></asp:Label>
    <br /><br />
    <asp:SqlDataSource ID="SqlDSLeaveType" runat="server" ConnectionString="<%$ ConnectionStrings:LeaveManagementCS %>" SelectCommand="SELECT * FROM [LeaveType]"></asp:SqlDataSource>
    <br />
    <asp:SqlDataSource ID="SqlDSStartLeavePeriod" runat="server" ConnectionString="<%$ ConnectionStrings:LeaveManagementCS %>" SelectCommand="SELECT * FROM [LeavePeriod]"></asp:SqlDataSource>
    <br />
    <asp:SqlDataSource ID="SqlDSEndLeavePeriod" runat="server" ConnectionString="<%$ ConnectionStrings:LeaveManagementCS %>" SelectCommand="SELECT * FROM [LeavePeriod]"></asp:SqlDataSource> <br />
    <asp:SqlDataSource ID="SqlDSApplyLeave" runat="server" ConnectionString="<%$ ConnectionStrings:LeaveManagementCS %>" DeleteCommand="DELETE FROM [LeaveApplications] WHERE [Id] = @Id" InsertCommand="INSERT INTO [LeaveApplications] ([StaffId], [LeaveTypeId], [StartDate], [StartLeavePeriodId], [EndDate], [EndLeavePeriodId], [NumDays]) VALUES (@StaffId, @LeaveTypeId, @StartDate, @StartLeavePeriodId, @EndDate, @EndLeavePeriodId, @NumDays)" SelectCommand="SELECT * FROM [LeaveApplications]" UpdateCommand="UPDATE [LeaveApplications] SET [StaffId] = @StaffId, [LeaveTypeId] = @LeaveTypeId, [StartDate] = @StartDate, [StartLeavePeriodId] = @StartLeavePeriodId, [EndDate] = @EndDate, [EndLeavePeriodId] = @EndLeavePeriodId, [NumDays] = @NumDays WHERE [Id] = @Id">
        <DeleteParameters>
            <asp:Parameter Name="Id" Type="Int32" />
        </DeleteParameters>
        <InsertParameters>
            <asp:Parameter Name="StaffId" Type="Int32" />
            <asp:ControlParameter ControlID="ddlLeaveType" Name="LeaveTypeId" PropertyName="SelectedValue" Type="Int32" />
            <asp:ControlParameter ControlID="tbStartDate" Name="StartDate" PropertyName="Text" Type="String" />
            <asp:ControlParameter ControlID="ddlStartLeavePeriod" Name="StartLeavePeriodId" PropertyName="SelectedValue" Type="Int32" />
            <asp:ControlParameter ControlID="tbEndDate" Name="EndDate" PropertyName="Text" Type="String" />
            <asp:ControlParameter ControlID="ddlEndLeavePeriod" Name="EndLeavePeriodId" PropertyName="SelectedValue" Type="Int32" />
            <asp:Parameter Name="NumDays" Type="Double" />
        </InsertParameters>
        <UpdateParameters>
            <asp:Parameter Name="StaffId" Type="Int32" />
            <asp:Parameter Name="LeaveTypeId" Type="Int32" />
            <asp:Parameter Name="StartDate" Type="String" />
            <asp:Parameter Name="StartLeavePeriodId" Type="Int32" />
            <asp:Parameter Name="EndDate" Type="String" />
            <asp:Parameter Name="EndLeavePeriodId" Type="Int32" />
            <asp:Parameter Name="NumDays" Type="Double" />
            <asp:Parameter Name="Id" Type="Int32" />
        </UpdateParameters>
    </asp:SqlDataSource><br />
</asp:Content>

ApplyLeave.aspx。cs编码：

public partial class ApplyLeave : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            CalExtStartDate.StartDate = DateTime.Today;
        }
        protected void btnApply_Click(object sender, EventArgs e)
        {
            try
            {
                SqlDSApplyLeave.Insert();
                lblOutput.Text = "Application success";
            }
            catch(Exception ex)
            {
                lblOutput.Text = ex.Message;
            }
        }
    }

InsertQuery:

点击图像

无法将值NULL插入列'；StaffID'；，桌子

从StaffID中删除NOT NULL，因此更改此项：

[StaffId]            INT          NOT NULL,

到此：

[StaffId]            INT          ,

并且您的外键在这里也必须允许NULL：

CONSTRAINT [FK_LeaveApplications_ToStaff] FOREIGN KEY ([StaffId]) REFERENCES [dbo].[Staff] ([StaffId]),
相关文章：