ADO - Add Records in ADO

1. Ways to Add Records in ADO

There are three main methods to add data:

  1. Using Recordset.AddNew → Best when you want to insert data interactively.

  2. Using Connection.Execute → Best for simple insert queries.

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

2. Method 1: Using Recordset.AddNew

The AddNew method lets you add a new row directly to the Recordset, then save it with the Update method.

Syntax

Recordset.AddNew
Recordset("FieldName") = Value
Recordset.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 "Employees", conn, 1, 3  ' adOpenKeyset, adLockOptimistic

' Add new record
rs.AddNew
rs("FirstName") = "John"
rs("LastName") = "Doe"
rs("Salary") = 55000
rs.Update

MsgBox "New record added successfully!"

' Close objects
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing

3. Method 2: Using Connection.Execute (Best for Fast Inserts)

This method executes an INSERT SQL statement directly.

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;"

' Insert a new record
conn.Execute "INSERT INTO Employees (FirstName, LastName, Salary) VALUES ('Alice', 'Smith', 60000)"

MsgBox "Record inserted successfully!"

conn.Close
Set conn = Nothing

When to Use:

  • For simple inserts without fetching data back.

  • Faster than Recordset.AddNew.

4. Method 3: Using Command Object (Best for Stored Procedures / Parameters)

The Command object is used for secure inserts, especially when working with user inputs.

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 = "INSERT INTO Employees (FirstName, LastName, Salary) VALUES (?, ?, ?)"
cmd.CommandType = 1  ' adCmdText

' Add parameters
cmd.Parameters.Append cmd.CreateParameter("FirstName", 200, 1, 50, "David")  ' adVarChar
cmd.Parameters.Append cmd.CreateParameter("LastName", 200, 1, 50, "Brown")
cmd.Parameters.Append cmd.CreateParameter("Salary", 3, 1, , 70000)           ' adInteger

' Execute insert
cmd.Execute

MsgBox "Record inserted successfully!"

conn.Close
Set cmd = Nothing
Set conn = Nothing

Advantages:

  • Prevents SQL Injection.

  • Best for inserting user-provided data safely.

  • Preferred for stored procedures.

5. Example: ADO Add in ASP Classic

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

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

' Open recordset with write access
rs.Open "Employees", conn, 1, 3  ' adOpenKeyset, adLockOptimistic

' Add a new employee
rs.AddNew
rs("FirstName") = "Emma"
rs("LastName") = "Watson"
rs("Salary") = 65000
rs.Update

Response.Write "New record added successfully!"

rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
%>

6. Example: ADO Add 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;"

' Add record directly
conn.Execute "INSERT INTO Employees (FirstName, LastName, Salary) VALUES ('Sophia', 'Taylor', 72000)"

WScript.Echo "Record inserted successfully!"

conn.Close
Set conn = Nothing

7. Best Practices

  • Use Connection.Execute for fast inserts.

  • Use Command parameters for user-provided inputs to prevent SQL Injection.

  • Always close Recordsets and Connections.

  • Use transactions if inserting multiple records.

  • Validate data before inserting into the database.