Visual Basic and SQL Server – Updating Data

In order to update data in an SQL Server database, the SQL ‘Update’ statement needs to be used. The following example updates the record in the ‘person’ table that was added in the example for inserting data.

Updating data follows the same pattern as inserting data. Firstly, a connection to the database is established, then the query parameters are defined as variables, which are bound in to the following SQL statement. One of the parameters is for an ‘id’, so that only the specified record is updated, in this case, the record with an ‘id’ of five. The other parameters are for updating the ‘lastname’ and ‘title’ fields against the record. The SQL statement is then executed and a confirmation message is displayed. The declaration and execution of the SQL statement is wrapped in a ‘Try-Catch-Finally’ block to catch any errors that may arise and close the database connection at the end.

' Database connection variable.
Dim connect = New SqlConnection(
    "Server=localhost\MSSQLSERVERDEMO; Database=Demo;" &
    "User Id=DemoUN; Password=DemoPW")

Try

    ' Connect to database.
    connect.Open()

Catch ex As Exception

    ' Confirm unsuccessful connection and stop program execution.
    Console.WriteLine("Database connection unsuccessful.")
    End

End Try

Try

    ' Query parameters.
    Dim lastname As String = "Bloggs"
    Dim title As String = "Mrs"
    Dim id As Integer = 5

    ' Query text.
    Dim sqlText As String = 
        "UPDATE person " &
        "SET lastname = @lastname, " &
        "    title = @title " &
        "WHERE id = @id"

    ' Query text incorporated into SQL command.
    Dim sqlUpdate As New SqlCommand(sqlText, connect)

    ' Bind the parameters to the query.
    sqlUpdate.Parameters.AddWithValue("@lastname", lastname)
    sqlUpdate.Parameters.AddWithValue("@title", title)
    sqlUpdate.Parameters.AddWithValue("@id", id)

    ' Execute SQL.
    sqlUpdate.ExecuteNonQuery()

    ' Confirm successful updating of person information.
    Console.WriteLine("Person information updated successfully.")

Catch ex As Exception

    ' Confirm error updating person information and exit.
    Console.WriteLine("Error updating person information.")
    End

Finally

    ' Close the database connection.
    connect.Close()

End Try

The contents of the ‘person’ table now looks as follows.

id firstname lastname title dob
1 Bob Smith Mr 1980-01-20
2 George Jones Mr 1997-12-15
3 Fred Bloggs Mr 1975-05-07
4 Alan White Mr 1989-03-20
5 Fiona Bloggs Mrs 1985-05-19