ADO - Update Records in ADO

1. Ways to Update Records in ADO

There are three main methods:

  1. Using Recordset.Update → Update a record after fetching it.

  2. Using Connection.Execute → Run an SQL UPDATE query directly.

  3. Using Command Object → Best for parameterized updates and stored procedures.

2. Method 1: Using Recordset.Update

The Update method is used after modifying field values in a Recordset.

Syntax

rs("FieldName") = NewValue
rs.Update

Example in VB

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

' Open connection
conn.Open "Provider=SQLOLEDB;Data Source=MYPC\SQLEXPRESS;Initial Catalog=EmployeeDB;User ID=sa;Password=1234;"

' Open recordset with write access
rs.Open "SELECT * FROM Employees WHERE EmployeeID=5", conn, 1, 3  ' adOpenKeyset, adLockOptimistic

' Update employee salary
If Not rs.EOF Then
    rs("Salary") = 70000
    rs.Update
    MsgBox "Salary updated successfully!"
Else
    MsgBox "Employee not found!"
End If

rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing

When to Use:

  • For updating specific records after fetching them.

  • Useful when you want to view and modify data interactively.

3. Method 2: Using Connection.Execute (Recommended for Bulk Updates)

This method executes an UPDATE SQL statement directly. It's faster and uses less memory.

Example in VB

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection

' Open connection
conn.Open "Provider=SQLOLEDB;Data Source=MYPC\SQLEXPRESS;Initial Catalog=EmployeeDB;User ID=sa;Password=1234;"

' Update salary for employees in IT department
conn.Execute "UPDATE Employees SET Salary = Salary + 5000 WHERE Department='IT'"

MsgBox "Salaries updated successfully!"

conn.Close
Set conn = Nothing

Advantages:

  • Very fast.

  • Ideal for bulk updates.

  • No need to open a Recordset.

4. Method 3: Using Command Object (Best for Secure Updates)

Use the Command object with parameters to avoid SQL injection and improve security.

Example in VB

Dim conn As ADODB.Connection
Dim cmd As ADODB.Command

Set conn = New ADODB.Connection
Set cmd = New ADODB.Command

' Open connection
conn.Open "Provider=SQLOLEDB;Data Source=MYPC\SQLEXPRESS;Initial Catalog=EmployeeDB;User ID=sa;Password=1234;"

' Configure command
Set cmd.ActiveConnection = conn
cmd.CommandText = "UPDATE Employees SET Salary = ? WHERE EmployeeID = ?"
cmd.CommandType = 1  ' adCmdText

' Add parameters
cmd.Parameters.Append cmd.CreateParameter("Salary", 3, 1, , 80000)       ' adInteger
cmd.Parameters.Append cmd.CreateParameter("EmployeeID", 3, 1, , 5)      ' adInteger

' Execute command
cmd.Execute

MsgBox "Employee salary updated successfully!"

conn.Close
Set cmd = Nothing
Set conn = Nothing

Advantages:

  • Safer than string-based SQL.

  • Recommended when dealing with user input.

  • Useful for stored procedures.

5. Example: ADO Update in ASP Classic

<%
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")

conn.Open "Provider=SQLOLEDB;Data Source=MYPC\SQLEXPRESS;Initial Catalog=EmployeeDB;User ID=sa;Password=1234;"

' Update salary for a single employee
conn.Execute "UPDATE Employees SET Salary = 90000 WHERE EmployeeID = 10"

Response.Write "Employee salary updated successfully!"

conn.Close
Set conn = Nothing
%>

6. Example: ADO Update in VBScript

Dim conn
Set conn = CreateObject("ADODB.Connection")

conn.Open "Provider=SQLOLEDB;Data Source=MYPC\SQLEXPRESS;Initial Catalog=EmployeeDB;User ID=sa;Password=1234;"

' Update record directly
conn.Execute "UPDATE Employees SET Department='Finance' WHERE EmployeeID=7"

WScript.Echo "Employee department updated successfully!"

conn.Close
Set conn = Nothing

7. Best Practices for ADO Update

  • Use Connection.Execute for fast updates.

  • Use parameterized queries to prevent SQL Injection.

  • Always use WHERE when updating to avoid changing all records accidentally.

  • Use transactions for bulk updates:

    conn.BeginTrans
conn.Execute "UPDATE Employees SET Salary = Salary + 2000 WHERE Department='Sales'"
conn.CommitTrans

  • Close Recordsets and Connections after use to free memory.

 

 

Do you want me to create that diagram? It will make the update process clearer.