Blazor - The INSERT statement conflicted with the FOREIGN KEY constraint

1

I'm getting the conflicted error message on my foreign key table, however, the drop-down menu populates the course category ID, an INT field as a number but the column is bound on InputText, as a string instead of InputNumber, although io tried using the InputNumber data type on the razor page, it didn't work. Looks like the numbers are being converted into string, hence the error or is there anything that I'm doing wrong here? I was able to enter the fields manually without the drop down menu and it works, this why i think the number is being converted to string.

Error

System.Data.SqlClient.SqlException (0x80131904): The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Course_CourseCategory". The conflict occurred in database "ITMS", table "dbo.CourseCategory", column 'CourseCategoryID'

enter image description hereenter image description here

Razor page

 <div class="col-12 row">
     <label class="col-2 font-weight-bold">Course Title:</label>
     <InputSelect @bind-Value="@newPerson.Course">
         <option value="0">Select</option>

         @foreach (var item in CourseCategories)
         {
            <option value="@item.CourseCategoryID">@item.Title</option>
         }
     </InputSelect>
     &nbsp;<ValidationMessage For="@(() => newPerson.Course)" />
</div>

<div class="col-12 row">
    <label class="col-2 font-weight-bold">Course Category ID:</label>
    <InputText id="CourseCategoryID" @bind-Value="newPerson.Course" placeholder="CourseCategoryID" />
    &nbsp;<ValidationMessage For="@(() => newPerson.CourseCategoryID)" />
</div>

Model

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading.Tasks;

namespace BlazorDemoUI.Models
{
    public class DisplaySchoolModel
    {
        public string CountryName { get; set; }
        [Required]
        public int SchoolID { get; set; }
        [Required]
        public string Name { get; set; }
        [Required]
        public string Location { get; set; }
        [Required]
        public string Address { get; set; }
        [Required]
        public string PostCode { get; set; }
        public string CountryCode { get; set; }
        [Required]
        public int SchoolAdminPersonID { get; set; }
    }
}

Tables:

CREATE TABLE [dbo].[CourseCategory]
(
    [CourseCategoryID] [int] IDENTITY(1,1) NOT NULL,
    [Code] [nvarchar](50) NOT NULL,
    [Title] [nvarchar](50) NOT NULL,
    [Summary] [ntext] NULL,
    [Description] [ntext] NULL,
    [Notes] [ntext] NULL,
    [Duration] [smallint] NULL,

    CONSTRAINT [PK_CourseCategory] 
        PRIMARY KEY CLUSTERED ([CourseCategoryID] ASC)
) ON [PRIMARY]

CREATE TABLE [dbo].[Course]
(
    [CourseID] [int] IDENTITY(1,1) NOT NULL,
    [CourseCategoryID] [int] NOT NULL,
    [SchoolID] [int] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [Course] [nvarchar](50) NOT NULL,
    [StartDate] [smalldatetime] NOT NULL,
    [Duration] [int] NOT NULL,
    [Seats] [int] NOT NULL,
    [Notes] [ntext] NULL,
    [PublicClass] [bit] NOT NULL,
    [SeatsAvailable] [int] NOT NULL,
    [Instructor] [nvarchar](50) NULL,
    [IsCancelled] [bit] NOT NULL,
    [ReasonForChange] [nvarchar](250) NULL,
    [HasPrerequisite] [bit] NOT NULL,
    [PrerequisiteName] [nvarchar](100) NULL,

    CONSTRAINT [PK_Course] 
        PRIMARY KEY CLUSTERED ([CourseID] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Course] WITH CHECK 
    ADD CONSTRAINT [FK_Course_CourseCategory]     
        FOREIGN KEY([CourseCategoryID]) REFERENCES [dbo].[CourseCategory] ([CourseCategoryID])
                ON UPDATE CASCADE
                ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Course] CHECK CONSTRAINT [FK_Course_CourseCategory]
GO

ALTER TABLE [dbo].[Course] WITH CHECK 
    ADD CONSTRAINT [FK_Course_School]  
        FOREIGN KEY([SchoolID]) REFERENCES [dbo].[School] ([SchoolID])
                ON UPDATE CASCADE
                ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Course] CHECK CONSTRAINT [FK_Course_School]
GO
c#
asp.net
razor
blazor
asked on Stack Overflow Apr 15, 2020 by SQLBen • edited Apr 15, 2020 by marc_s

1 Answer

2

InputSelect does not support binding to an integer: https://github.com/dotnet/aspnetcore/blob/master/src/Components/Web/src/Forms/InputSelect.cs

Use <select> tag instead of InputSelect like

<select @bind="model.ByCountryId">
    @if (model?.Countries != null)
    {
        @foreach (var cnt in model.Countries)
        {
            <option value="@cnt.Id">@cnt.Name</option>
        }
    }

answered on Stack Overflow Apr 15, 2020 by Nick

User contributions licensed under CC BY-SA 3.0