Update a database using a recordset object and bound controls.
Updating Database Records
The previous lesson named SqlCommand and ExecuteNonQueryAsync as the pair that replaces classic ADO's Recordset methods, and sketched three handler methods without writing a complete, working version of any of them. This lesson writes the first of those three: a real Update button that changes an existing row in VirtualBookShelf's Customer table, built entirely on what Module 4 and the previous lesson already established.
The legacy version of this lesson used Visual InterDev's data-bound Recordset and Textbox Design Time Controls together, which made updating a row almost trivially easy: only one line of script, a call to the Recordset object's updateRecord method, actually did the work. The modern version is just as short, it's simply built out of different pieces.
A generic customer-record editing view, the kind of bound form a visitor sees: labeled text boxes, a status dropdown, and navigation buttons for moving between records. The fields shown here are illustrative sample data; this lesson's own Update handler works directly against VirtualBookShelf's real CustomerID and Email columns, built the modern way rather than through a bound Design Time Control.
What the Legacy Simulation Did
The original lesson's walkthrough used a file called CustManage.asp, which already contained a Recordset DTC and several Textbox DTCs bound to a customer's data, along with a RecordsetNavBar DTC for moving between rows. Adding an Update button meant dragging a Button DTC onto the page, naming it UpdateBtn, giving it an Update caption, and opening the Script Outline to add a single line of code to its onclick event:
Sub UpdateBtn_onclick
CustRecords.updateRecord
End Sub
That one line was the entire implementation. Because the Textbox DTCs were already bound to the Recordset's fields, whatever a visitor typed into them was already sitting in the Recordset's in-memory copy of the row by the time the button was clicked; updateRecord simply told the Recordset to send that copy back to the database. Nothing about that convenience survived Visual InterDev, but the shape it left behind, one button, one short handler, one call that does the actual work, is exactly what this lesson rebuilds.
Classic ADO's Hidden Complexity: Cursors and Lock Types
Before rebuilding that shape, it's worth seeing what a Recordset's updateRecord actually required underneath the DTC's convenience, because the contrast is instructive. A classic ADO Recordset can't be updated at all unless it's opened with the right cursor type and lock type:
Open that same Recordset with its default lock type instead, adLockReadOnly, and calling .Update fails outright: a read-only Recordset simply doesn't support it. Open it without a keyset or dynamic cursor, and it fails for a different reason, the OLE DB provider has no way to find the exact underlying row once script tries to write to it. Worse, the SELECT statement powering the Recordset has to include the table's primary key column, CustomerID above, or the same lookup failure happens even with the right cursor and lock type.
None of that is a limitation of ADO.NET's SqlCommand, because SqlCommand doesn't have a cursor concept at all. There's no Recordset staying positioned on a row, no lock type to choose, and no separate requirement that a column appear in some other statement first. An UPDATE statement's WHERE clause names the row directly, and that's the only place the primary key needs to appear.
Building the Update Handler
With that contrast in mind, here's the real implementation. The page model gains an OnPostUpdateAsync method, filling in the comment the previous lesson's preview left behind:
public async Task<IActionResult> OnPostUpdateAsync(int customerId, string newEmail)
{
using SqlConnection connection = new(_connectionString);
await connection.OpenAsync();
SqlCommand command = new(
"UPDATE Customer SET Email = @NewEmail WHERE CustomerID = @CustomerID",
connection);
command.Parameters.AddWithValue("@NewEmail", newEmail);
command.Parameters.AddWithValue("@CustomerID", customerId);
int rowsAffected = await command.ExecuteNonQueryAsync();
if (rowsAffected == 0)
{
ModelState.AddModelError(string.Empty,
"That customer record could not be found. It may have been removed.");
}
return RedirectToPage();
}
That's the entire handler, and it's not much longer than the legacy DTC's own single line once the surrounding plumbing is counted the same way: open a connection, build a parameterized command, run it, check how many rows it actually changed. The @NewEmail and @CustomerID placeholders keep every value a visitor supplies out of the SQL text itself, the same discipline this course has held to since Module 3 and that the previous lesson carried into this module without exception.
Wiring the Form to the Handler
The page's markup points a form at this handler using the asp-page-handler Tag Helper the previous lesson introduced:
The hidden CustomerId field is doing the same job the legacy Recordset's current-row position did, telling the handler exactly which row to change, just carried explicitly in the submitted form instead of implicitly in a Recordset's cursor. Leaving that field out of the form isn't a cosmetic mistake: without it, customerId arrives at OnPostUpdateAsync as zero, the WHERE clause matches nothing, and the handler's own row-count check catches the resulting no-op update rather than letting it fail silently.
The Primary Key, Two Ways
It's worth pulling one thread from the classic ADO comparison a little further, because the same underlying idea shows up in both the legacy and modern versions of this lesson, just in different places. Classic ADO needed the primary key in the SELECT statement that opened the Recordset, so the keyset cursor could track exactly which row .Update would eventually write to. The modern version needs the primary key in the UPDATE statement's own WHERE clause, so the database knows exactly which row to change. Both are the same requirement, identify one specific row unambiguously, expressed through whatever mechanism the technology actually uses: a cursor's internal bookmark in one case, an ordinary WHERE clause in the other.
What Happens on an Ordinary Page Load
This lesson's OnPostUpdateAsync method handles the form submission, but something still needs to show the current Email value in the form before a visitor changes anything. That's OnGetAsync's job, built the same way Module 4 already covered: a SqlCommand running a parameterized SELECT, a SqlDataReader reading the single matching row, and the result stored in a bound property the Razor markup above already reads through asp-for="Email".
[BindProperty]
public int CustomerId { get; set; }
[BindProperty]
public string Email { get; set; } = string.Empty;
public async Task OnGetAsync(int customerId)
{
using SqlConnection connection = new(_connectionString);
await connection.OpenAsync();
SqlCommand command = new(
"SELECT CustomerID, Email FROM Customer WHERE CustomerID = @CustomerID",
connection);
command.Parameters.AddWithValue("@CustomerID", customerId);
using SqlDataReader reader = await command.ExecuteReaderAsync();
if (await reader.ReadAsync())
{
CustomerId = reader.GetInt32(reader.GetOrdinal("CustomerID"));
Email = reader.GetString(reader.GetOrdinal("Email"));
}
}
Nothing about retrieving that starting value is new; it's the exact pattern Module 4 built, reused here to populate a form instead of a table. The [BindProperty] attribute on CustomerId and Email is what lets the same two properties serve double duty: OnGetAsync fills them for display, and when the form posts back to OnPostUpdateAsync, ASP.NET Core's model binding fills them again from whatever the visitor actually submitted, without either handler needing to read the HTTP request directly.
Guarding Against Bad Input Before the Database Ever Sees It
The previous lesson's FAQ mentioned data annotation attributes, such as [Required], as the modern equivalent of whatever field-level validation a DTC's property sheet might have offered. This lesson is a natural place to actually use one. Marking Email with [EmailAddress] tells ASP.NET Core to reject a malformed address before OnPostUpdateAsync even runs:
Checking ModelState.IsValid at the top of the handler, before any ADO.NET code runs at all, keeps an obviously invalid value from ever reaching the UPDATE statement:
public async Task<IActionResult> OnPostUpdateAsync(int customerId, string newEmail)
{
if (!ModelState.IsValid)
{
return Page();
}
// ...the SqlCommand shown earlier runs from here.
}
This is a different kind of safety than the parameterization this course has emphasized since Module 3. Parameters stop a malicious or malformed value from being interpreted as SQL; model validation stops an obviously wrong value, an empty string, a missing @ sign, from reaching the database at all. Both matter, and neither replaces the other.
Wrapping the Handler in Structured Error Handling
The previous lesson's FAQ described wrapping ADO.NET calls in a try/catch block; this lesson's handler is a concrete place to show it rather than just describe it. A Customer.Email update is a reasonably safe statement, but it can still fail, a connection can drop, or a constraint elsewhere in the database can reject the new value:
public async Task<IActionResult> OnPostUpdateAsync(int customerId, string newEmail)
{
try
{
using SqlConnection connection = new(_connectionString);
await connection.OpenAsync();
SqlCommand command = new(
"UPDATE Customer SET Email = @NewEmail WHERE CustomerID = @CustomerID",
connection);
command.Parameters.AddWithValue("@NewEmail", newEmail);
command.Parameters.AddWithValue("@CustomerID", customerId);
int rowsAffected = await command.ExecuteNonQueryAsync();
if (rowsAffected == 0)
{
ModelState.AddModelError(string.Empty, "That customer record could not be found.");
}
}
catch (SqlException ex)
{
_logger.LogError(ex, "Failed to update CustomerID {CustomerId}", customerId);
ModelState.AddModelError(string.Empty, "Something went wrong saving that change. Please try again.");
}
return RedirectToPage();
}
The catch block here does exactly what it did in Module 4's error-handling lesson: logs the real exception somewhere a developer can actually find it, and shows the visitor a plain message instead of a raw SqlException. Nothing about this pattern is specific to updating data; it's the same structure this course already built, applied to this lesson's own handler.
The Same Pattern Against a Different Table
Nothing about OnPostUpdateAsync's shape is specific to Customer. The same approach updates VirtualBookShelf's Inventory table just as directly, say, adjusting a book's QuantityOnHand after a shipment arrives:
public async Task<IActionResult> OnPostUpdateQuantityAsync(int bookId, int newQuantity)
{
using SqlConnection connection = new(_connectionString);
await connection.OpenAsync();
SqlCommand command = new(
"UPDATE Inventory SET QuantityOnHand = @NewQuantity WHERE BookID = @BookID",
connection);
command.Parameters.AddWithValue("@NewQuantity", newQuantity);
command.Parameters.AddWithValue("@BookID", bookId);
await command.ExecuteNonQueryAsync();
return RedirectToPage();
}
Only the table name, the column being changed, and the parameter names are different; the connection, the command, the parameterization, and the row-count check all stay exactly the same. That repetition is the point: once one Update handler is built correctly, every other Update handler in this application follows the same four steps.
Frequently Asked Questions
Why does the handler check rowsAffected instead of trusting that the update worked? Because ExecuteNonQueryAsync succeeding and the update actually changing a row are two different things. A WHERE clause that matches zero rows, say because the customer was deleted a moment earlier, still runs without error; the only way to notice nothing actually changed is to check the integer ExecuteNonQueryAsync returns.
Could this same handler update more than one column at once? Yes. Adding another column to the SET clause and another parameter for its value works exactly the same way, whether that's Phone, FirstName, or any other column on Customer. This lesson kept the example to one column, Email, to keep the first working handler as simple as possible.
What happens if two visitors try to update the same customer's email at nearly the same time? Whichever UPDATE statement reaches SQL Server last wins; the second write simply overwrites the first, with no error raised on either side. SQL Server 2025's Optimized Locking reduces how long a row stays locked during a short update like this one, labeling each row by its most recent modifying transaction and avoiding an early lock when a query's predicate can be checked against the latest committed version first, but that's a performance improvement, not a conflict-detection feature. Catching an actual overwrite, by comparing the row's state against what the visitor originally loaded, is a more advanced technique than this lesson's first working button needs.
Does AddWithValue need to know the exact SQL Server column type ahead of time? No. AddWithValue infers a parameter's type from the .NET value it's given, a C# string becomes an appropriately typed text parameter, an int becomes an integer parameter, and so on. For most columns, including Email and CustomerID here, that inference works correctly without any extra configuration.
Is there a way to run this same update directly in Azure Data Studio before wiring it into the handler? Yes, and it's worth doing exactly that first, the same habit this course has followed since Module 3. Running UPDATE Customer SET Email = 'test@example.com' WHERE CustomerID = 1; directly confirms the statement is valid and affects the row it's supposed to before it's ever wrapped in a SqlCommand and harder to inspect.
What should happen if ExecuteNonQueryAsync throws an exception instead of simply returning zero? The structured exception handling the previous lesson's FAQ already described applies here without changes: a try block around the ADO.NET call and a catch (SqlException ex) block that logs the error and shows the visitor a plain message. An exception means something went wrong with the statement itself, such as a constraint violation; a zero row count means the statement ran fine but matched nothing, two different situations worth telling apart.
Why does the legacy example need both a cursor type and a lock type, when the modern version needs neither? Because a Recordset's whole design assumed it might stay open and connected while a visitor edited bound controls, which meant it needed to track a current row (the cursor) and know whether changes to that row were even permitted (the lock type). A SqlCommand running an UPDATE statement has no such lifetime, it connects, runs one statement, and is done, so neither concept has anywhere to attach.
Could the Update button and the retrieval that fills the form live on two separate Razor Pages instead of one? They could, but keeping OnGetAsync and OnPostUpdateAsync on the same page keeps the data and the action that changes it next to each other, closer to how the legacy Recordset DTC and its bound Button DTC sat together on one page in the first place.
What's Ahead
This lesson built the first of this module's three handlers, a working Update button backed by a parameterized SqlCommand and checked against how many rows it actually changed, replacing the legacy Recordset's updateRecord method and the keyset cursor and lock type it quietly depended on. The next lesson in this module adds the second: an Insert button that adds a new row to VirtualBookShelf's data rather than changing an existing one, using the same SqlCommand and ExecuteNonQueryAsync pair, just with an INSERT statement in place of an UPDATE.
After that, this module's final lesson builds the Delete button, completing the three operations named in Module 5's opening lesson. Each one reuses exactly what this lesson just established: a connection opened the same way, a parameterized command built the same way, and a row count checked the same way, only the SQL statement itself changing from one lesson to the next.