VB.NET: How to UPDATE record on SQL Database

This topic is part of VB.NET SQL Database Basics Tutorial series. On this article, you will learn how to incorporate SQL UPDATE statements on your VB.NET application. An SQL UPDATE statement that changes the data of one or more records in a table. Either all the rows can be updated, or a subset may be chosen using a condition. For this article, we will focus particularly on updating records in Microsoft SQL Server.



Note: I assumed that you already installed your SQL Server and Visual Studio .NET.

Here is the step-by-step procedure to UPDATE record on SQL server:
1. Create your VB.NET project.
2. Include the following namespaces.

Imports System.Data
Imports System.Data.SqlClient

The System.Data namespace provides access to classes that represent the ADO.NET architecture while the System.Data.SqlClient namespace is the.NET Framework Data Provider for SQL Server.

3. Declare and instantiate your SQLConnection object and Command object as shown below

Dim con As New SqlConnection
Dim cmd As New SqlCommand

4. Pass the SQL connection string to ConnectionString property of your SqlConnection object.

con.ConnectionString = "Data Source=atisource;Initial 
Catalog=BillingSys;Persist Security Info=True;User ID=sa;Password=12345678"

5. Invoke the Open Method to connect to SQL Server.

con.Open()

6. Set the connection of the command object.

cmd.Connection = con

7. Pass the UPDATE SQL statement to the command object commandtext as shown below

cmd.CommandText = "UPDATE table SET field1 = value1, field2 = value2 
WHERE field3='Test'"

8. Use the ExecuteNonQuery() method to run the UPDATE SQL statement.

cmd.ExecuteNonQuery()

The full sample source code of updating records on SQL Database:

Dim con As New SqlConnection
Dim cmd As New SqlCommand
Try
   con.ConnectionString = "Data Source=atisource;Initial 
Catalog=BillingSys;Persist Security Info=True;User ID=sa;Password=12345678"
 con.Open()
 cmd.Connection = con
 cmd.CommandText = "UPDATE table SET field1 = value1, field2 = value2 
WHERE field3='Test'"
 cmd.ExecuteNonQuery()

Catch ex As Exception
 MessageBox.Show("Error while updating record on table..." & ex.Message, "Update Records")
Finally
 con.Close()
End Try

Related Topic: VB.NET Tutorial: SQL Database Basics