Friday, 6 Mar 2026

VB.NET Database Insert: Add Bookings Step-by-Step

Connecting and Inserting Data with VB.NET

When building a booking system in VB.NET, writing data to the database is a fundamental task. Let's say you have a form where users select seats for a customer. This guide walks you through the process of inserting those bookings into the database, focusing on existing customers. We'll cover validation, SQL command execution, and updating the UI.

Understanding the Database Structure

Our example database has three tables:

  • Customers: Stores customer details (CustomerID, Name, etc.)
  • Seats: Holds seat information (SeatID, SeatNumber, etc.)
  • Bookings: Links customers to seats (BookingID, CustomerID, SeatID, etc.)

The BookingID is an auto-number field in Access, so we don't need to specify it in our INSERT statement. This simplifies the code.

Validating User Input

Before inserting data, ensure the user has selected at least one seat. We loop through the form controls to check for selected seats:

Dim seatSelected As Boolean = False
For Each ctrl As Control In Me.Controls
    If TypeOf ctrl Is PictureBox Then
        Dim pb As PictureBox = DirectCast(ctrl, PictureBox)
        If pb.Tag.ToString() = "Selected" Then
            seatSelected = True
            Exit For
        End If
    End If
Next
If Not seatSelected Then
    MessageBox.Show("Please select at least one seat.")
    Return
End If

This code sets a flag if any seat is selected. If not, we show a message and exit.

Building the SQL INSERT Statement

For each selected seat, we construct an SQL INSERT command. Initially, we hardcode values to test:

INSERT INTO Bookings (CustomerID, SeatID) VALUES (1, 24)

Test this in Access first to verify syntax. Then, we translate it to VB.NET code.

Executing the Insert Command

To run the INSERT from VB.NET, we use the OleDbConnection and OleDbCommand objects. Here's the setup:

Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\MyDatabase.accdb"
Using conn As New OleDbConnection(connectionString)
    conn.Open()
    Dim sql As String = "INSERT INTO Bookings (CustomerID, SeatID) VALUES (1, 24)"
    Using cmd As New OleDbCommand(sql, conn)
        cmd.ExecuteNonQuery()
    End Using
End Using

The Using statement ensures resources are disposed of properly. ExecuteNonQuery runs the command and returns the number of rows affected.

Handling Multiple Seat Bookings

To insert multiple seats, loop through the selected seats and execute an INSERT for each:

For Each ctrl As Control In Me.Controls
    If TypeOf ctrl Is PictureBox AndAlso ctrl.Tag.ToString() = "Selected" Then
        Dim seatID As Integer = Integer.Parse(ctrl.Name.Substring(10)) ' Extract seat number from PictureBox name
        Dim sql As String = "INSERT INTO Bookings (CustomerID, SeatID) VALUES (" & customerID & ", " & seatID & ")"
        Using cmd As New OleDbCommand(sql, conn)
            cmd.ExecuteNonQuery()
        End Using
    End If
Next

Key points:

  • Extract the SeatID from the PictureBox name (e.g., "PictureBox24" becomes 24)
  • Use string concatenation to build the SQL (note: parameterized queries are better for security, covered later)
  • Execute the command inside the loop

Updating the User Interface

After inserting bookings, refresh the UI to show the new data. Move the booking display code to a separate procedure:

Private Sub UpdateBookings()
    ' Code to reload bookings and update UI
End Sub

Call this after the insert loop and in the form's Load event:

' After inserting bookings
UpdateBookings()

Best Practices and Common Pitfalls

  1. Close Connections: Always close the database connection. The Using block handles this automatically
  2. Error Handling: Add Try...Catch blocks to handle database errors
  3. Auto-Number Gaps: If you delete records, Access doesn't reuse auto-numbers. Compact the database to reset them
  4. Security: Avoid SQL injection by using parameterized queries (especially when handling user input)

Next Steps: Beyond Existing Customers

This tutorial focused on existing customers. In reality, you'll need to:

  1. Allow selecting customers from a list (instead of hardcoding IDs)
  2. Add new customers during the booking process
  3. Implement parameterized queries for security

Code Checklist for Inserting Bookings:

  1. Validate seat selections
  2. Loop through selected seats
  3. Build SQL INSERT for each seat
  4. Execute the command
  5. Update the UI
  6. Close the database connection

Recommended Resources

  • Books: "Database Programming with VB.NET" by Carsten Thomsen (covers ADO.NET in depth)
  • Tools: Use Microsoft Access for small projects; for larger apps, consider SQL Server Express
  • Communities: Stack Overflow for troubleshooting specific issues

Conclusion

Inserting bookings into a database from VB.NET involves validating input, constructing SQL commands, and executing them in a loop for multiple records. Remember to close connections and update the UI.

What aspect of database insertion do you find most challenging? Share your experience in the comments below.

Youtube
Blog→