How to save client data first and reservation next, with a single onpostasync on .net 7 core and select2, when the client does not exists?

Question

1. Expaination

The app is a Razor pages, Web Api on .net7.0 with bootstrap5 and select2 javascript.

Through a table and a popup button, i can successfully save reservations, when the client exists.

The problem arises when the client does not exist.

I click on the select box write the name to see if it exists.

If it does not, then the name written, gets copied in an <input>, which, on post should:

(a) save this new client and then,

(b) save the reservation for this one client.

The error i get:

SqlException: Cannot insert the value NULL into column 'KliEm', table 'My_Data65.dbo.Clientss'; column does not allow nulls. INSERT fails.

2. Question

What am i doing wrong?

3. The frontend code

  <div class="col-md-12">
            <!-- Modal -->
            <div class="modal fade" id="addreservationModal" tabindex="-1" role="dialog" aria-labelledby="addreservationModalLabel" aria-hidden="true">
                <div class="modal-dialog" role="document" style="max-width:420px;">
                    <div class="modal-content">
                        <form id="Create" method="post" asp-page-handler="Create">
                            <div class="modal-body">
                                <div asp-validation-summary="ModelOnly" class="text-danger"></div>

                                <div class="form-group p-0">
                                    <label asp-for="Reservations.MyDate" class="border-0 m-0" style="max-height:20px;font-size:70%;">Data</label>
                                    <input asp-for="Reservations.MyDate" class="form-control fw-bold text-primary" style="font-size:80%;"
                                           id="mydate" type="text" readonly />
                                    <span asp-validation-for="Reservations.MyDate" class="text-danger"></span>
                                </div>
                                <div class="form-group p-0">
                                    <label asp-for="Reservations.HourId" class="border-0 m-0" style="max-height:20px;font-size:70%;">Ora...</label>
                                            <select asp-for="Reservations.HourId" class="form-control fw-bold text-primary"
                                                    id="HourIdpopup" asp-items="ViewBag.HourId" style="font-size:80%;">
                                            </select>
                                            <span asp-validation-for="Reservations.PreOraId" class="text-danger"></span>
                                </div>
                                <div class="form-group p-0">
                                    <div class="row">
                                        <div class="col-12">
                                            <label asp-for="Reservations.CliId" class="border-0 m-0" style="max-height:20px;font-size:70%;">Clients name</label>
                                            <br>
                                            <select asp-for="Reservations.CliId" class="text-primary fw-bold form-select klient select2-container overflow-scroll" asp-items="ViewBag.CliId" name="original" id="original"
                                                    data-placeholder="--- Clients name ---" style="font-size:80%;max-height:240px;">
                                                <option value="">--- Clients name ---</option>                                                   
                                            </select>                                                
                                        </div>
                                        <div class="col-12">
                                            <input asp-for="Clients.KliEm" class="form-control fw-bold text-primary" name="originalcopy" id="originalcopy" style="font-size:80%;"> 
                                            <input asp-for="Clients.Tel" class="form-control fw-bold text-primary" name="tel" value="0" style="font-size:80%;">
                                            <input asp-for="Clients.Email" class="form-control fw-bold text-primary" name="email" value="a@a.a" style="font-size:80%;">
                                        </div>
                                    </div>
                                </div>                                  
                                <partial name="_BtnSavePartial" />
                            </div>
                        </form>
                    </div>
                </div>
            </div>
        </div>

The javascript code

<script>
    $("#original").keyup(function () {
        var textval = $(":selected", this).val();
        $('input[name=originalcopy]').val(textval);
    });
    $("#original").change(function () {
        var textval = $(":selected", this).val();
        $('input[name=originalcopy]').val(textval);
    });
</script>

4. The backend code

public async Task<IActionResult> OnPostCreateAsync()
    {
        try
        {
            _context.Reservations.Add(Reservation);
            await _context.SaveChangesAsync();
        }
        catch (DbUpdateException e)
        {
            e.ToString();
            _context.Clients.Add(Client);
            await _context.SaveChangesAsync();
            Reservation pp = new();
            pp.KliId = Client.CliId;
            pp.MyDate = Reservation.MyDate;
            pp.HourId = Reservation.HourId;
            _context.Reservations.Add(pp);
            await _context.SaveChangesAsync();
            return RedirectToPage("./Index1");
        }
        catch (Exception e)
        {  
            _ = e.ToString();
            return RedirectToPage("./Index");
        }
    }


                [BindProperty]
    public Clientss Clients { get; set; } = default!;
    [BindProperty]
    public Reservations Reservation { get; set; } = default!;

This is the database code:

1. The reservation table

   CREATE TABLE [dbo].[Reservation]( [ResId] [int] IDENTITY(1,1) NOT NULL, [MyDate] [date] NOT NULL, [HourId] [int] NOT NULL, [CliId] [int] NOT NULL, CONSTRAINT [PK__Reservation] PRIMARY KEY CLUSTERED ( [ResId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Reservation] WITH CHECK ADD FOREIGN KEY([CliId]) REFERENCES [dbo].[Clients] ([CliId]) GO ALTER TABLE [dbo].[Reservation] WITH CHECK ADD FOREIGN KEY([HourId]) REFERENCES [dbo].[ReservationHours] ([HourId]) GO

2. The client table

   SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Clients]( [CliId] [int] IDENTITY(1,1) NOT NULL, [Name] nvarchar NOT NULL, [Tel] char NOT NULL, [Email] nvarchar NOT NULL, CONSTRAINT [PK__Clients] PRIMARY KEY CLUSTERED ( [CliId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO

This is the AddNew button

  <a href="#addreservationModal" class="btn btn-sm border-0"
   style="color:gray;font-size:70%;min-width: 150px;max-width: 150px;min-height:50px; text-align:right;" data-toggle="modal" data-bs-toggle="modal" 
   data-mydate="@DateTime.UtcNow.ToString("yyyy-MM-dd")" data-hourid="@ora.OraId"  id="btnCreate">
    +
</a>

Answer 1

I tested your front end code and it has a problem.

You have added name="original" and name="originalcopy" to the second select tag and KliEm input respectively, which will cause the backend to fail to populate the corresponding model. You can open the browser's developer tools to view the details:

The model is not populated due to a parameter with the wrong name.

You need to change the name attribute to the corresponding name in the model. If you don't need the name attribute, you can remove it.

@*Remove name attribute*@
<select asp-for="Reservations.CliId" class="text-primary fw-bold form-select klient select2-container overflow-scroll" id="original" asp-items="ViewBag.CliId" data-placeholder="--- Clients name ---" style="font-size:80%;max-height:240px;">
    <option value="">--- Clients name ---</option>
</select>  
@*......*@ 
@*Change name attribute*@ 
<input asp-for="Clients.KliEm" class="form-control fw-bold text-primary" name="KliEm" id="originalcopy" style="font-size:80%;">
<input asp-for="Clients.Tel" class="form-control fw-bold text-primary" name="Tel" value="0" style="font-size:80%;">
<input asp-for="Clients.Email" class="form-control fw-bold text-primary" name="Email" value="a@a.a" style="font-size:80%;">

The javascript code:

<script>
    $("#original").keyup(function () {
        var textval = $(":selected", this).val();
        $('input[name=KliEm]').val(textval);
    });
    $("#original").change(function () {
        var textval = $(":selected", this).val();
        $('input[name=KliEm]').val(textval);
    });
</script>

Test Result:

In addition, I found that your database table does not match the actual model, and you do not have the Name property when submitting the form, that is, Name=null, which will also cause the same error: Cannot insert the value NULL into column 'Name'. Please make sure your database table is consistent with your model.