VBScript tutorial 8 | Working with SQL

This is the last tutorial in the VBScript Tutorials for Beginners. This VBScript beginner tutorial explains how to work with databases using VBScript. The COM objects that VBScript can use are the ADO objects such as the Connection object and the Recordset object. Please view the VBScript tutorial 8 or read on... What is Connection? A Connection object connects the VBScript to the database. A Connection object may be open or closed. If the Connection is open, we may run SQL commands on the database to get data from the database. What is Recordset? A Recordset object is a result set from the database. We may display the Recordset data using our VBScript. Now, let us see a VBScript example with SQL commands.

' VBScript code
' This script is just an example. Write your own script, test it on a non-production database and use it at your own risk.
Option Explicit
' Define the ADO objects for connection and recordset.
Dim objConnection, objRecordSet
OpenADOObjects
ShowCustomers
'AddTempCustomer
'UpdateCustomer
'DeleteTempCustomer
CloseADOObjects

Sub ShowCustomers
With objRecordSet
    ' Display the records
    Do While Not .EOF
     WScript.Echo objRecordSet("Name") &" has their office address at " & objRecordSet("BillAddress")
        .MoveNext
    Loop
End With
End Sub
' More VBScript code follows
Sub AddTempCustomer
    ' Add a dummy record
    objRecordSet.AddNew
    objRecordSet("CustomerId") = 4
    objRecordSet("Name") =  "Cust4"
    objRecordSet("BillAddress") = "xxxxxxxxxxxxxxxxxxxxxxx"
    objRecordSet("ShipAddress") = "yyyyyyyyyyyyyyyyyyy"
    objRecordSet.Update
End Sub

Sub UpdateCustomer
    ' Update existing customer
    objConnection.Execute "UPDATE Customers SET Name = 'Customer1' WHERE NAME='Cust1'"
End Sub

Sub DeleteTempCustomer
    ' Delete the dummy record (added using the procedure AddTempCustomer above).
    ' Write the Delete SQL carefully! It must have the Where clause to select only the dummy record.
    objConnection.Execute "DELETE Customers WHERE CustomerId=4"   
End Sub

Sub OpenADOObjects
    Set objConnection = CreateObject("ADODB.Connection")
    objConnection.Open ConnectionString()
    Set objRecordSet = CreateObject("ADODB.RecordSet")
    ' Assuming a Customers table exists with CustomerId, Name, BillAddress and ShipAddress columns.
    ' Open the recordset with the SQL query.
    objRecordSet.Open "SELECT * FROM Customers", objConnection, 1, 3 ' the last two arguments are adOpenKeySet and adLockOptimistic
End Sub

Sub CloseADOObjects
    objRecordSet.Close
    objConnection.Close
End Sub

Function ConnectionString()
    ' A connection string has data about server name, database name and login information.
    ' I wrote the connection string using the format from https://www.connectionstrings.com/
End Function

Want to learn with a diagram and working VBScript code? Please view my VBScript tutorial 8. Thank you.