Blazor - Cannot insert the value NULL into column

I filled the below for and the country code was populated from a country table as a lookup based on the country chosen from the InputSelect column, "Country Name", circled in blue. However, when I fill the rest of the form and click on submit, it threw the errors shown below.

However, if I enter the same code manually in an inputText column, "Country Code", then it submits the form.

I figured that because the country column has a country lookup table that has the CountryName column and the corresponding CountryCode column. So I have two models in place, one for country and the other for school, so when the countryname is chosen, it calls it from the country model and which the insert is not done on, the insert is done on the school model @insertschool. In this case, the school model does not see any insert for the costcode column. Is there anyway that this can be be done using the @person model rather than uning both the @person and @person1? Or maybe make use of a temporary table?

Error: System.Data.SqlClient.SqlException (0x80131904): Cannot insert the value NULL into column 'CountryCode', table 'ITMS.dbo.School'; column does not allow nulls. INSERT fails.

Code:

@page "/Data/School"

@using DataAccessLibrary
@using DataAccessLibrary.Models
@using BlazorDemoUI.Models

@inject ISchoolData _db

<h1>Choose a School</h1>
@if (Schools != null)
{
    <h4>Schools</h4>
    <EditForm Model="@newPerson" OnValidSubmit="@InsertSchool">
    <DataAnnotationsValidator />
    <ValidationSummary />

    <div class="col-12 row">
        <label class="col-12 font-weight-bold">Country Name:</label>

        <InputSelect @bind-Value="@newPerson2.CountryName">
            <option value="0">Select</option>

            @foreach (var item in Countries)
            {
                <option value="@item.CountryCode">@item.CountryName </option>
            }
        </InputSelect>

        &nbsp;<ValidationMessage For="@(() => newPerson2.CountryName)" />
        <br />
        @*<input type="submit" class="form-control col-1 btn btn-primary" value="Save" />*@

        <div class="col-12 row">
            <span class="col-2"></span>
        </div>

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

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

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

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

        <div class="col-12 row">
            <label class="col-2 font-weight-bold">Country Code:</label>
            <InputText id="CountryCode" @bind-Value="@newPerson2.CountryName" placeholder="CountryCode" />
            &nbsp;<ValidationMessage For="@(() => newPerson.CountryCode)" />
        </div>

        @*<div class="col-12 row">
            <label class="col-2 font-weight-bold">Country Code:</label>
            <InputText id="CountryCode" @bind-Value="@newPerson2.CountryName" placeholder="CountryCode" />
            &nbsp;<ValidationMessage For="@(() => newPerson.CountryCode)" />
        </div>*@

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

        <input type="submit" class="form-control col-1 btn btn-primary" value="Save" />
    </div>
</EditForm>
}

<h4>Current School</h4>
@if (Schools is null)
{
    <p><em>Loading...</em></p>
}
else
{
    <table class="table table-striped">
        <thead>
            <tr>
                @*<th>CountryName</th>*@
                <th>SchoolID</th>
                <th>Name</th>
                <th>Location</th>
                <th>Address</th>
                <th>PostCode</th>
                <th>CountryCode</th>
            </tr>
        </thead>
        <tbody>
            @foreach (var person in Schools)
            {
                <tr>
                    @*<td>@person.CountryName</td>*@
                    <td>@person.SchoolID</td>
                    <td>@person.Name</td>
                    <td>@person.Location</td>
                    <td>@person.Address</td>
                    <td>@person.PostCode</td>
                    <td>@person.CountryCode</td>
                    <td> 
                        <input type="button" class="btn btn-primary" value="Edit" />
                    </td>
                    <td>
                        <input type="button" class="btn btn-danger" value="Delete" />
                    </td>
                </tr>
            }
            </tbody>
        </table>
        }

    @code {
            List<SchoolModel> Schools;
            DisplaySchoolModel newPerson = new DisplaySchoolModel();
            List<CountryModel> Countries;
            DisplayCountryModel newPerson2 = new DisplayCountryModel();

            protected override async Task OnInitializedAsync()
            {
                Schools = await _db.GetSchool();
                Countries = await _db.GetCountry();
            }

            private async Task InsertSchool()
            {
                SchoolModel C = new SchoolModel
                                    {
                                        // CountryName = newPerson2.CountryName,
                                        SchoolID = newPerson.SchoolID,
                                        Name = newPerson.Name,
                                        Location = newPerson.Location,
                                        Address = newPerson.Address,
                                        PostCode = newPerson.PostCode,
                                        CountryCode = newPerson2.CountryCode,
                                    };

                await _db.InsertSchool(C);

                Schools.Add(C);

                newPerson = new DisplaySchoolModel();
            }

            using System;
            using System.Collections.Generic;
            using System.Text;

            namespace DataAccessLibrary.Models
            {
            public class SchoolModel
            {
            public string CountryName { get; set; }
            public int SchoolID { get; set; }
            public string Name { get; set; }
            public string Location { get; set; }
            public string Address { get; set; }
            public string PostCode { get; set; }
            public string CountryCode { get; set; }
            public int SchoolAdminPersonID { get; set; }

            }
            }



            using DataAccessLibrary.Models;
            using System;
            using System.Collections.Generic;
            using System.Text;
            using System.Threading.Tasks;

            namespace DataAccessLibrary
            {
            public class SchoolData : ISchoolData
            {
            private readonly ISqlDataAccess _db;

            public SchoolData(ISqlDataAccess db)
            {
            _db = db;
            }

            public Task<List<SchoolModel>> GetSchool()
            {
            string sql = "select * from school";

            return _db.LoadData<SchoolModel, dynamic>(sql, new { });
            }

            public Task InsertSchool(SchoolModel School)
            {
            string sql = @"insert into dbo.School (Name, Location,Address,PostCode,CountryCode,SchoolAdminPersonID)
                    values (@Name,@Location,@Address,@PostCode,@CountryCode,@SchoolAdminPersonID);";

            return _db.SaveData(sql, School);
            }

            public Task<List<CountryModel>> GetCountry()
            {
            string sql = "select * from dbo.Country";

            return _db.LoadData<CountryModel, dynamic>(sql, new { });
            }

Right now you have both a newPerson and a newPerson2, which makes things quite confusing. Also in your "Country Code" Input element you are actually not binding it to the CountryCode property, which means that newPerson2.CountryCode in InsertSchool() is always gonna be null.

I would suggest you remove newPerson2 and change the Country Name dropdown and Country Code input as follows:

<InputSelect @bind-Value="@newPerson.CountryCode">
    <option value="0">Select</option>

    @foreach (var item in Countries)
    {
        <option value="@item.CountryCode">@item.CountryName</option>
    }
</InputSelect>

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

Then down in InsertSchool() you should of course use newPerson: CountryCode = newPerson.CountryCode.

I haven't tested the above, but I expect it should work. What it means is that your dropdown (InputSelect) will set the value of newPerson.CountryCode based on your choice. When this happens, the Country Code Input will be updated by showing newPerson.CountryCode as well as ensuring the CountryCode is filled out when saving it to the DB.