Modifying Data  «Prev  Next»

Lesson 3 Inserting records
Objective Insert new records into the database using bound controls.

Inserting Database Records

VirtualBookShelf.com wouldn't last long if new customers couldn't sign up and new titles couldn't be added to the catalog. The previous lesson built an Update button for an existing Customer row; this lesson builds the second of this module's three handlers, an Insert button that adds a brand new one.

The legacy version of this lesson reused the Update button's own machinery to do it. A call to the Recordset's addRecord method created a blank row buffer, and clicking the same Update button from the previous lesson, calling updateRecord, actually wrote it to the database. The modern version collapses that two-step sequence into a single INSERT statement, built the same way this module's Update handler was: with SqlCommand, no Entity Framework Core, and every value a visitor supplies passed through a parameter.

What the Legacy Simulation Did

The original lesson's walkthrough used a file called CustManage_1.asp. Adding an Insert button meant dragging a Button DTC onto the page, naming it InsertBtn, giving it an Insert caption, and opening the Script Outline to add one line to its onclick event:

Sub InsertBtn_onclick
    CustRecords.addRecord
End Sub
Clicking that button didn't insert a row by itself. It cleared the bound Textbox controls and put the Recordset into a blank, add-pending state; a visitor then typed in the new customer's information and clicked the Update button from the previous lesson, whose updateRecord call was the one that actually committed the row to the database. The legacy text was direct about this: "the addRecord method adds a blank record, then the updateRecord method updates it with the real information." Two buttons, two method calls, one new row.

The legacy simulation's own sample data is worth noting but not reusing directly: it asked a visitor to enter a ten-digit CustNo, a last and first name, and an address broken into ADDR1, ADDR2, City, ST, and Zip. That's a different shape than VirtualBookShelf's actual Customer table, which tracks FirstName, LastName, Email, and Phone, not a mailing address. This lesson's own working example builds against the real columns instead.

From Two Calls to One: addRecord, updateRecord, and a Single INSERT

The legacy sequence needed two separate method calls because of how a Recordset's edit buffer works: addRecord allocates an empty row in memory, and a separate call is required to flush it to the database, the same updateRecord call an existing row's edits already used. ADO.NET has no equivalent two-step process, because SqlCommand doesn't maintain an in-memory row buffer at all. An INSERT statement names every column and value it needs up front and commits them all in one call, the same simplification the previous lesson noted for UPDATE.

INSERT INTO Customer (FirstName, LastName, Email, Phone)
VALUES (@FirstName, @LastName, @Email, @Phone);
That single statement does the work of both the legacy addRecord and updateRecord calls combined: there's no separate "create a blank row" step, because the row doesn't exist in any form until this statement actually runs.

A Classic ADO Shortcut Worth Knowing, Not Teaching

Classic ADO code that opened a Recordset just to call AddNew had its own performance trap: a careless SELECT * FROM Customer forced the database to stream every existing row before a single new one could be added. The fix was a schema-only cursor, a query like SELECT CustomerID, FirstName, LastName, Email, Phone FROM Customer WHERE 1 = 0, which SQL Server recognizes as unsatisfiable and returns instantly, with zero rows but full column information for the Recordset to build its blank buffer from.

Nothing in this lesson's modern approach needs that trick, since an INSERT statement never retrieves existing rows in the first place, but it's worth knowing the trap it was solving: fetching data you don't need is wasted work, whether the technology is a 1990s Recordset or a modern SqlCommand.

Building the Insert Handler

Here's the full implementation, filling in the OnPostInsertAsync comment this module's first lesson left behind. It reads its values from the NewCustomer property the form just posted back, the same bound property declared above:

public async Task<IActionResult> OnPostInsertAsync()
{
    using SqlConnection connection = new(_connectionString);
    await connection.OpenAsync();

    SqlCommand command = new(
        "INSERT INTO Customer (FirstName, LastName, Email, Phone) " +
        "OUTPUT INSERTED.CustomerID " +
        "VALUES (@FirstName, @LastName, @Email, @Phone)",
        connection);
    command.Parameters.AddWithValue("@FirstName", NewCustomer.FirstName);
    command.Parameters.AddWithValue("@LastName", NewCustomer.LastName);
    command.Parameters.AddWithValue("@Email", NewCustomer.Email);
    command.Parameters.AddWithValue("@Phone", NewCustomer.Phone);

    object? result = await command.ExecuteScalarAsync();
    int newCustomerId = Convert.ToInt32(result);

    return RedirectToPage("./Details", new { id = newCustomerId });
}
The OUTPUT INSERTED.CustomerID clause is this lesson's one genuinely new technique. It tells SQL Server to hand back the value it just generated for the new row's IDENTITY column, in the same round trip as the insert itself. Because that clause returns exactly one value for exactly one new row, this handler calls ExecuteScalarAsync instead of ExecuteNonQueryAsync, the method this module has used for every other Insert and Update so far. ExecuteScalarAsync returns the first column of the first row a statement produces, which here is the new CustomerID, ready to use immediately, for a redirect to a details page, a confirmation message, or anything else that needs to know which row was just created.

Declaring the Bound Form Model

The Insert form's markup, shown next, binds its fields through NewCustomer.FirstName and similar expressions, which means the page model needs a small, plain class to hold those four values while the form is being filled out, along with an empty instance for the initial page load:

public class NewCustomerInput
{
    public string FirstName { get; set; } = string.Empty;
    public string LastName { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
    public string Phone { get; set; } = string.Empty;
}

[BindProperty]
public NewCustomerInput NewCustomer { get; set; } = new();

public void OnGet()
{
    // Nothing to load for a blank Insert form; NewCustomer
    // starts out empty from its own default initializer above.
}
Notice there's no CustomerID property on NewCustomerInput at all, not even one left blank. That's deliberate, and it's the same idea the previous section about overposting will return to: a bound model that simply has no place to put a CustomerID can't accidentally receive one, no matter what a visitor's browser sends.

Wiring the Form to the Handler

The markup points a form at this handler the same way Lesson 2's did, through asp-page-handler="Insert":

<form method="post" asp-page-handler="Insert">
    <div>
        <label asp-for="NewCustomer.FirstName">First Name:</label>
        <input asp-for="NewCustomer.FirstName" />
    </div>
    <div>
        <label asp-for="NewCustomer.LastName">Last Name:</label>
        <input asp-for="NewCustomer.LastName" />
    </div>
    <div>
        <label asp-for="NewCustomer.Email">Email Address:</label>
        <input asp-for="NewCustomer.Email" type="email" />
    </div>
    <div>
        <label asp-for="NewCustomer.Phone">Phone:</label>
        <input asp-for="NewCustomer.Phone" />
    </div>
    <button type="submit">Insert</button>
</form>
Notice what's missing compared to the Update form built in the previous lesson: there's no hidden field for a CustomerID at all. A new row doesn't have one yet; it doesn't exist until the INSERT statement above creates it.

Why CustomerID Never Appears in the Insert Form

This isn't just a convenience, it's a requirement. VirtualBookShelf's CustomerID column is defined as IDENTITY, which means SQL Server generates its value automatically every time a row is inserted. Trying to supply a value for it directly, the way a careless handler might if it blindly inserted every field a form happened to submit, fails with a specific, well-documented error: 544, Cannot insert explicit value for identity column in table 'Customer' when IDENTITY_INSERT is set to OFF. The classic ADO code this lesson's research touched on carried the same caution in a different form: never assign a value to rs.Fields("ProductId") before calling .AddNew. Same rule, same reason, two different technologies.

Guarding Against Overposting

There's a real security concern that comes up specifically with insert and update forms: overposting, sometimes called mass assignment. If a bound model has a property for a sensitive column, a malicious visitor can add a hidden field to the POST request that was never on the visible form at all, targeting that property directly and potentially setting a column they have no business touching.

This lesson's NewCustomerInput class, declared above, is safe from that risk for a simple reason: it only has four properties, FirstName, LastName, Email, and Phone. There's no CustomerID property anywhere on it. A visitor could submit a field named NewCustomer.CustomerID in the POST request, and ASP.NET Core's model binder would simply have nowhere to put it; a value with no matching property is ignored, not stored. The protection here isn't filtering out dangerous fields after the fact, it's that the bound model was never given a place to receive one in the first place. That's a cleaner fix than the allow-list approach EF Core's own tooling reaches for, because there's no list to maintain and no way to forget an entry on it.

Wrapping the Handler in Structured Error Handling

An insert has at least one failure mode an update usually doesn't: if Email has a unique constraint, which is a reasonable choice for a customer table, inserting a duplicate address raises SQL Server's familiar error 2627. This module's error handling, already covered for updates, applies here the same way:

public async Task<IActionResult> OnPostInsertAsync()
{
    try
    {

        using SqlConnection connection = new(_connectionString);
        await connection.OpenAsync();

        SqlCommand command = new(
            "INSERT INTO Customer (FirstName, LastName, Email, Phone) " +
            "OUTPUT INSERTED.CustomerID " +
            "VALUES (@FirstName, @LastName, @Email, @Phone)",
            connection);
        command.Parameters.AddWithValue("@FirstName", NewCustomer.FirstName);
        command.Parameters.AddWithValue("@LastName", NewCustomer.LastName);
        command.Parameters.AddWithValue("@Email", NewCustomer.Email);
        command.Parameters.AddWithValue("@Phone", NewCustomer.Phone);

        object? result = await command.ExecuteScalarAsync();
        int newCustomerId = Convert.ToInt32(result);

        return RedirectToPage("./Details", new { id = newCustomerId });
    }
    catch (SqlException ex) when (ex.Number == 2627 || ex.Number == 2601)
    {
        ModelState.AddModelError(string.Empty, "That email address is already registered.");
        return Page();
    }
    catch (SqlException ex)
    {
        _logger.LogError(ex, "Failed to insert new customer");
        ModelState.AddModelError(string.Empty, "Something went wrong creating that record. Please try again.");
        return Page();
    }
}
Catching 2627 and 2601 specifically, before the general SqlException catch below it, lets the handler tell a visitor exactly what went wrong, a duplicate email, rather than the generic message every other database error gets.

Frequently Asked Questions

Why does this handler call ExecuteScalarAsync instead of ExecuteNonQueryAsync? Because the OUTPUT INSERTED.CustomerID clause makes this statement return a single value, the new row's identity. ExecuteNonQueryAsync would still run the insert correctly, but it only returns a row count, with no way to retrieve the generated CustomerID in the same call.

What happens if the INSERT statement doesn't include an OUTPUT clause at all? The row still gets inserted, but nothing comes back to tell the handler what CustomerID SQL Server assigned it. For a page that needs to redirect to the new record afterward, as this lesson's handler does, that identity value is essential; for a simpler insert that just confirms success, it can be left out and ExecuteNonQueryAsync used instead.

Could SCOPE_IDENTITY() be used instead of an OUTPUT clause? Yes, that's the older, equally valid approach: run the INSERT, then run a second statement, SELECT SCOPE_IDENTITY();, in the same batch. OUTPUT INSERTED.CustomerID does the same job in a single statement instead of two, which is why this lesson used it.

Does every column on Customer need a value in this INSERT statement? No, only the columns that don't have a default and aren't generated automatically. CustomerID is generated by its IDENTITY property; any column with its own DEFAULT constraint, a signup date that defaults to the current time, for example, only receives that default when it's left out of the statement's column list entirely, not when it's explicitly set to NULL.

What if a visitor submits a form with an empty first name? As written, this handler's INSERT would run and store an empty string, assuming the column allows it; catching that earlier, with a [Required] data annotation and a ModelState.IsValid check before the ADO.NET code runs, follows exactly the pattern the previous lesson introduced for validating Email.

Why catch error 2627 and 2601 together rather than just one of them? Both indicate a uniqueness violation, 2627 for a UNIQUE constraint and 2601 for a unique index, and either can fire depending on exactly how the constraint on Email was created. Catching both covers the same underlying problem regardless of which specific mechanism enforces it.

Is the overposting risk this lesson described specific to Insert handlers, or does it apply to Update too? It applies to any handler that binds a submitted value onto a database row, Insert and Update alike, and the fix is the same idea either way: give the bound model no property for anything a visitor shouldn't control. The previous lesson's OnPostUpdateAsync took customerId and newEmail as explicit method parameters rather than a bound object at all, a different mechanism reaching the same result: there was never a property or parameter a hidden field could target.

Why does NewCustomerInput get its own class instead of reusing a Customer class that already has a CustomerID property? That's exactly the point. A single Customer class with every column, including CustomerID, would need some other mechanism, an allow-list, a [BindNever] attribute, something, to keep that column safe during an insert. A separate, deliberately narrow NewCustomerInput class needs nothing extra, because the dangerous property was never there to begin with.

Should this INSERT statement be tested in Azure Data Studio before it's wrapped in a SqlCommand? Yes, the same habit this course has followed since Module 3. Running INSERT INTO Customer (FirstName, LastName, Email, Phone) OUTPUT INSERTED.CustomerID VALUES ('Test', 'User', 'test@example.com', '555-0100'); directly confirms the statement is valid, respects any constraints on Email, and actually returns the new identity value, before it's harder to inspect inside a handler.

Could this same Insert pattern add a new row to the Inventory table instead of Customer? Yes, with no change to the overall shape. An INSERT INTO Inventory (Title, Author, ISBN, Price, QuantityOnHand) OUTPUT INSERTED.BookID VALUES (...) statement works exactly the same way, parameterized the same way, read back the same way with ExecuteScalarAsync.

What's Ahead

This lesson built the second of this module's three handlers, a working Insert button that replaces the legacy Recordset's two-step addRecord/updateRecord sequence with a single parameterized INSERT statement, and introduced this lesson's one new technique, reading a generated identity value back with OUTPUT INSERTED and ExecuteScalarAsync. The next, and final, lesson in this module builds the Delete button, the simplest of the three operations named back in Lesson 1.

Deleting a row needs no columns, no values, and no identity to retrieve, just a WHERE clause naming the row to remove, which is exactly why the legacy course itself promised that deleting rows is easier than inserting them.

Inserting Records - Exercise

Click the Exercise link below to practice inserting new records into the BookTable.
Inserting Records - Exercise

SEMrush Software 3 SEMrush Banner 3