Updating Database Table with Programming in VB 2010

Hello friends! In this tutorial, we will update a database table through Visual Basic code. In the previous lessons, we created a contact database and added buttons for editing its records. Now we will let the application locate a particular record, change one of its values and save that change without asking the operator to type into the form.

This is useful when information arrives from another part of a system. In the hotel-management example that inspired this lesson, room information reached a central computer wirelessly. The software needed to identify the correct room and update its stored information. The same basic idea applies to a contact import, an instrument reading or a status message received through a serial port.

We will continue with the Visual Basic contact database and the database button controls from this series. The examples retain the Visual Studio 2010 Windows Forms workflow, while correcting several problems in the original short code sample.

What Does Updating a Database Programmatically Mean?

Suppose our contact table contains a person whose ContactID is 2. A new name arrives from an approved import. Instead of selecting that contact in a grid and pressing Save, our program performs the lookup and assignment itself. The important requirement is that the program updates the intended record, rather than whichever row happens to be selected on screen.

There are two separate copies of the information in this application. SQL Server holds the persistent table. The form's dataset holds an in-memory working copy loaded from that table. Changing the working copy can immediately change what the form displays, but it does not prove that SQL Server received the change.

Microsoft describes the TableAdapter save operation in its dataset update documentation. We will use that established mechanism rather than treating a refreshed grid as evidence of a successful save.

Check the Existing Form and Dataset

You do not need to redesign the form for this lesson. Keep the grid, details controls and navigation buttons already created. Before adding an automatic update, make sure the existing Fill and Save operations work with a disposable contact record.

The updated series uses the following names. These names belong to our example project; your original Visual Studio designer may have generated different identifiers.

The earlier version of this article used database1DataSet, ContactForm, ID and table5TableAdapter. Do not combine those names with the revised names in a single project. Open the dataset designer and inspect the table, columns and adapter that actually exist. The adapter must target the same table that you intend to change.

Our revised contact schema includes FullName, Phone, Email and Address. It does not require a LastName column. Consequently, the main example below changes FullName using a supplied string. If your own schema separates first and last names, apply the same technique to those columns using their actual names and length limits.

Understand the Difference Between a Key and a Row Position

The number 2 can mean two very different things. ContactID = 2 identifies a record whose stored key is 2. Rows(2) selects the third row currently present in the DataTable because collection indexes start at zero. Sorting, filtering, deleted records and gaps in identity values make positional assumptions unreliable.

For example, imagine that the loaded keys are 1, 4 and 9. Rows(2) refers to the contact with key 9. A primary-key lookup for 2 finds nothing. Neither result is surprising once we distinguish a record's identity from its current position in memory.

Dim table As System.Data.DataTable = Me.ContactsDataSet.Contact_Form
Dim contact As System.Data.DataRow = table.Rows.Find(2)

If contact Is Nothing Then
    MessageBox.Show("Contact 2 is not loaded.")
    Return
End If

The Rows.Find method requires a primary key on the DataTable. A typed dataset may also offer a generated method such as FindByContactID. Both approaches refer to key values; neither means “the second displayed row.”

A missing row also does not automatically mean the database lacks that contact. Your Fill query may load only a filtered subset. Decide whether the program should load the required record, report that it is outside the current working set, or use a separate database operation. Do not silently create a replacement contact when an update lookup fails.

Correct the Logic in the Original Example

The original sample assigned two variables using the same key lookup and then copied LastName into FullName. Both variables referred to the same row object. They were not independent copies and they did not represent two different contacts.

Using two names for one row is legal, but it adds confusion here. If you intend to copy between columns on the same record, use one row variable. If you intend to copy between two contacts, perform two separate lookups with two explicitly chosen keys and check both results before editing either record.

There was another important omission: the sample assumed every lookup succeeded and every source value could be read as a string. Real tables can contain missing records and database NULL values. A useful tutorial must explain those cases before attaching the code to a continuous stream of incoming messages.

Write a Focused Update Function

Place the following function inside your existing form class. It changes one contact's FullName and asks the generated TableAdapter to save that row. It uses ordinary DataRow access so that the logic remains visible without requiring a particular generated row class name.

This teaching example deliberately requires a clean working table before starting. Finish or discard manual edits first. That gives the function clear ownership of the change and prevents an automatic event from accidentally saving an unrelated contact that someone is still editing.

Private Function UpdateContactName(ByVal contactId As Integer,
                                   ByVal suppliedName As String) As Integer
    If contactId <= 0 Then
        Throw New ArgumentException("A positive contact ID is required.")
    End If

    Dim newName As String = If(suppliedName, String.Empty).Trim()
    If newName.Length = 0 OrElse newName.Length > 100 Then
        Throw New ArgumentException("Enter a name of 1 to 100 characters.")
    End If

    If Not Me.ValidateChildren() Then
        Throw New InvalidOperationException("Finish the current form edit first.")
    End If
    Me.Contact_FormBindingSource.EndEdit()

    Dim table As System.Data.DataTable = Me.ContactsDataSet.Contact_Form
    If table.PrimaryKey.Length = 0 Then
        Throw New InvalidOperationException("ContactID must be a primary key.")
    End If
    If Me.ContactsDataSet.HasChanges() Then
        Throw New InvalidOperationException("Save or discard pending edits first.")
    End If

    Dim contact As System.Data.DataRow = table.Rows.Find(contactId)
    If contact Is Nothing Then
        Throw New InvalidOperationException("The requested contact is not loaded.")
    End If

    Dim oldName As String = If(contact.IsNull("FullName"),
                              String.Empty, CStr(contact("FullName")))
    If String.Equals(oldName, newName, StringComparison.Ordinal) Then
        Return 0
    End If

    contact("FullName") = newName
    Return Me.Contact_FormTableAdapter.Update(
        New System.Data.DataRow() {contact})
End Function

The 100-character limit comes from the revised contact table in this series. Match it to your real database column rather than copying it blindly. The positive-key check is an application assumption for our identity-based contact IDs, not a rule that every SQL primary key must follow.

The final call uses the generated adapter's DataRow-array Update overload. If your adapter does not expose the expected overload or update command, inspect its generated methods and configuration. Do not replace the call with an unrelated adapter simply because IntelliSense offers a method named Update.

The function returns zero when the requested name already matches the loaded value. Otherwise it returns the adapter's affected-row result. It does not display a success message itself, which keeps it reusable from a button, a queued device message or another part of the form.

Call the Function and Handle a Failed Save

For your first trial, create a test contact and note its actual ContactID. Call the function once from an existing test button. Substitute that key in the example below; do not assume your database contains contact 2.

Try
    Dim affected As Integer = UpdateContactName(2, "Ayesha Khan")
    If affected = 1 Then
        MessageBox.Show("The contact name was saved.")
    Else
        MessageBox.Show("No database row was changed.")
    End If
Catch ex As System.Data.DBConcurrencyException
    MessageBox.Show("The stored record changed. Review it before trying again.")
Catch ex As System.Data.SqlClient.SqlException
    MessageBox.Show("The database could not save this change.")
Catch ex As ArgumentException
    MessageBox.Show(ex.Message)
Catch ex As InvalidOperationException
    MessageBox.Show(ex.Message)
End Try

If the save fails, the proposed value can remain in the dataset. That is intentional: showing it as unsaved is more honest than pretending it was committed. Our clean-table check blocks the next automatic update until the operator has resolved the pending change.

Do not immediately call Fill over the whole table to make an error disappear. A refresh can discard local work. In an actual application, offer a deliberate choice to retry an appropriate transient failure, discard the pending edit, or reload and compare the stored record. A concurrency conflict deserves review because another writer may have changed the same contact.

The ADO.NET concurrency documentation explains the distinction between a successful update and a command that no longer matches its expected original record. The exact conflict checks depend on the SQL generated for your TableAdapter.

Handle NULL Values and Copying Between Columns

A database NULL means that a value is absent. It is not the same as an empty string. When your source column is optional, check it before assigning it to a required destination column.

If contact.IsNull("LastName") Then
    Throw New InvalidOperationException("This contact has no last name to copy.")
End If

Dim proposedName As String = CStr(contact("LastName")).Trim()

This small fragment applies only to a table that actually contains LastName. It illustrates the original article's copy operation; it is not an extra step to paste into the revised Contact Form schema. Pass a validated proposed name to the update function once you have decided that copying it is appropriate.

For a hotel or sensor application, choose columns that represent the incoming information. A room number belongs in a room identifier field; a temperature belongs in a numeric measurement column with a defined unit. Storing every incoming value in FullName merely because that column appears in the tutorial would make the database difficult to interpret.

Connect Incoming Data to the Update Process

Now we can return to the original reason for this tutorial: saving information that arrives automatically. Treat a device message as input that must be parsed and checked. A message should identify both the target record and the value to apply.

1. Collect a complete message according to the protocol, such as a line terminated by a newline.
2. Separate its fields and validate the identifier with Integer.TryParse.
3. Check the value against the destination column's rules.
4. Put the validated update into a controlled processing path.
5. Save it and record whether persistence succeeded before acknowledging success.

Do not assume that one serial receive event contains exactly one complete message. A message can arrive in pieces, and several messages can arrive together. Keep a receive buffer and process complete frames; otherwise a partial identifier can select the wrong record or produce a misleading validation error.

Windows Forms controls belong to their UI thread. The SerialPort.DataReceived reference notes that its handler runs on a secondary thread. Move form-bound dataset work to the UI thread with an appropriate invocation, or give a dedicated background worker its own connection and data objects. Do not share a form's bound dataset casually between concurrent handlers.

The simple synchronous function here suits a small demonstration. A continuous system needs a queue and clear ownership so that database delays do not block incoming data or freeze the interface. For the serial-port setup itself, continue with our Visual Basic COM-port tutorial.

Choose a Sensible Save Frequency

An automatic update does not mean that every received byte should trigger a database write. Separate incoming message rate from the rate at which useful state changes need to be persisted.

For example, suppose 40 rooms report their state once every five seconds. The arrival rate is 40 / 5 = 8 messages per second. If only one quarter of those reports represent a changed state, checking for changes reduces the expected state-update rate to approximately two writes per second. These are example workload assumptions, not measured limits for SQL Server.

Decide whether you need a latest-state table, a historical event log, or both. A latest-state table replaces the current room status. An event log appends a new record with its timestamp and source identifier. Repeatedly overwriting one row cannot later reveal how the value changed over time.

If several related changes must succeed together, design an explicit transaction around them. A loop that saves many rows independently can leave some updates committed when a later one fails. Choose that behavior intentionally rather than assuming a dataset automatically makes a whole business operation atomic.

Handle Duplicate and Out-of-Order Messages

A wireless sender may repeat a message because it did not receive an acknowledgement. Reapplying the same name to the same contact is usually harmless, but repeating an instruction such as “increase the balance” is a different matter. Give events a source identifier and sequence number when duplicate execution would change the meaning of the operation.

Arrival order can also differ from measurement order. If a delayed room-status message arrives after a newer one, an unconditional update can replace fresh information with stale information. Store an appropriate source sequence or timestamp and define which version is allowed to replace the current state. Keep clock synchronization and sequence resets in mind when choosing that rule.

These decisions belong to the application protocol. The database cannot infer whether a repeated message is a retry, a correction or a new event. Define that meaning before connecting the update function to unattended equipment.

Troubleshooting and Practical Review

There is also a subtle trap with AcceptChanges. It accepts the current values into the dataset's change-tracking baseline; it is not the command that writes them to SQL Server. Calling it before the adapter has processed your edits can remove the row-state information needed for saving. Keep that distinction clear when following older code examples.

Review one known contact through the complete workflow: read its key, apply a different name, observe the save result and independently inspect the same configured database. This checks the behavior you care about without relying on the appearance of a bound text box.

Frequently Asked Questions

Can I update the database without a Save button?

Yes. Call a validated update operation from application logic. The button is only one possible trigger; locating the correct record and handling persistence failures remain necessary.

Does FindByID(2) return the second record?

No. A generated primary-key lookup searches for the key value 2. A positional index and a primary key are different concepts, even if they happen to identify the same row in a small example.

Why does my project use different dataset names?

Visual Studio generates identifiers from your database and designer choices. Use the names in your project consistently. Renaming a caption on the form does not rename the underlying dataset or TableAdapter.

Should I build an SQL string using incoming text?

No. If you use direct SQL instead of a configured TableAdapter, pass values through typed parameters. Keep table and column choices under application control rather than accepting SQL fragments from a device or user.

Is this the recommended starting point for a new application?

This lesson maintains an existing Visual Studio 2010 and typed-dataset tutorial. For a new application, choose a supported development platform and evaluate its current data-access options. The concepts of identity, validation, concurrency and explicit persistence still apply.

What should happen when the database is unavailable?

Report or queue the unsaved update according to the application's requirements. Do not label it saved simply because the form accepted a value. A queued system also needs limits, duplicate handling and a clear recovery procedure.