VBScript Database Access Demos from
Demonstration script that uses a DSN to open a database named Northwind.
Const adOpenStatic = 3
Const adLockOptimistic = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
objConnection.Open _
"Northwind;fabrikam\kenmyer;34ghfn&!j"
objRecordSet.Open "SELECT * FROM Customers", _
objConnection, adOpenStatic, adLockOptimistic
objRecordSet.MoveFirst
Wscript.Echo objRecordSet.RecordCount
Connects to a Sybase Database by using ODBC.
'======
'= =
'= Created By: Devin H. Date: 01/29/2006 =
'= =
'= Name: Connect2Sybase.vbs Version: 1.0 =
'= =
'= Notes: This script will pull information from Sybase Database =
'= =
'= Requirements: Must have ODBC Connector Installed for this to work =
'= =
'======
On Error Resume Next
'======
'= Database connection settings, hence don't change this
'======
const adOpenStatic = 3
const adLockOptimistic = 3
const adUseClient = 3
'======
'= Detect Username for database
'= (Change these to your database name, username and password)
'======
ODBC = "DESSQL"
DBuser = "SysAdmin"
DBpwd = "masteryoda1983"
DBName = "C02"
'======
'= Connect to sybase database
'= Change TableName to whatever your table name is
'======
set objConnection = createObject("ADODB.Connection")
set objRecordset = createObject("ADODB.Recordset")
objConnection.Open "DSN=" & ODBC & ";uid=" & DBuser & ";pwd=" & DBpwd
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT * FROM " & DBName & ".TableName" , objConnection, _
adOpenStatic, adLockOptimistic
ObjRecordSet.MoveFirst
do while not ObjRecordSet.EOF
CustomerNotesID = objRecordset("CustNotesID")
CustomerID = objRecordset("CustomerID")
NoteType = objRecordset("NoteType")
UserCode = objRecordset("UserCode")
NoteDate = objRecordset("NoteDate")
LastUpdate = objRecordset("LastUpdate")
Setup = objRecordset("Setup")
Note = objRecordset("Note")
Wscript.Echo CustomerNotesID & "," & CustomerID & "," & NoteType & "," & UserCode & "," & NoteDate & "," & LastUpdate & "," & Setup & "," & Note
ObjRecordSet.MoveNext
loop
ObjConnection.Close
'======
'= The End
'======
Demonstration script that adds a new record to a database.
Const adOpenStatic = 3
Const adLockOptimistic = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = inventory.mdb"
objRecordSet.Open "SELECT * FROM GeneralProperties" , _
objConnection, adOpenStatic, adLockOptimistic
objRecordSet.AddNew
objRecordSet("ComputerName") = "atl-ws-01"
objRecordSet("Department") = "Human Resources"
objRecordSet("OSName") = "Microsoft Windows XP Professional"
objRecordSet("OSVersion") = "5.1.2600"
objRecordSet("OSManufacturer") = "Microsoft Corporation"
objRecordSet.Update
objRecordSet.Close
objConnection.Close
Demonstration script that deletes the record for a computer named WebServer from an ADO database with the DSN "Inventory."
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "DSN=Inventory;"
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT * FROM Hardware" , objConnection, _
adOpenStatic, adLockOptimistic
strSearchCriteria = "ComputerName = 'WebServer'"
objRecordSet.Find strSearchCriteria
objRecordset.Delete
objRecordset.Close
objConnection.Close
Demonstration script that searches a database for all records where the Message field contains the word PowerPoint.
Const adOpenStatic = 3
Const adLockOptimistic = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = eventlogs.mdb"
objRecordSet.Open "SELECT * FROM EventTable WHERE " & _
"Message Like '%PowerPoint%'", _
objConnection, adOpenStatic, adLockOptimistic
objRecordSet.MoveFirst
Wscript.Echo "Number of records: " & objRecordset.RecordCount
objRecordSet.Close
objConnection.Close
Demonstration script that sorts a recordset (in ascending order) on the EventCode field.
Const adOpenStatic = 3
Const adLockOptimistic = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = eventlogs.mdb"
objRecordSet.Open "SELECT * FROM EventTable " & _
"ORDER BY EventCode ASC", _
objConnection, adOpenStatic, adLockOptimistic
objRecordSet.MoveFirst
Do Until objRecordSet.EOF
Wscript.Echo objRecordSet.Fields.Item("EventCode"), objRecordSet.Fields.Item("Logfile")
objRecordSet.MoveNext
Loop
objRecordSet.Close
objConnection.Close
Demonstration script that updates sound card information for a computer named Webserver.
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "DSN=Inventory;"
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT * FROM Hardware" , objConnection, _
adOpenStatic, adLockOptimistic
strSearchCriteria = "ComputerName = 'WebServer'"
objRecordSet.Find strSearchCriteria
Set colSoundCards = GetObject("winmgmts:").ExecQuery _
("Select * from Win32_SoundDevice")
For Each objSoundCard in colSoundCards
objRecordset("ComputerName") = objSoundCard.SystemName
objRecordset("Manufacturer") = objSoundCard.Manufacturer
objRecordset("ProductName") = objSoundCard.ProductName
objRecordset.Update
Next
objRecordset.Close
objConnection.Close
Demonstration script that lists the different operating systems found in a database, as well as the number of computers running each operating system.
Const adOpenStatic = 3
Const adLockOptimistic = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = inventory.mdb"
objRecordSet.Open "SELECT OSName, Count(OSName) AS CountOfOSName" & _
" FROM GeneralProperties GROUP BY OSName ORDER BY Count(OSName) DESC", _
objConnection, adOpenStatic, adLockOptimistic
objRecordSet.MoveFirst
Do Until objRecordset.EOF
Wscript.Echo objRecordset.Fields.Item("OSName") & _
vbTab & objRecordset.Fields.Item("CountOfOSName")
objRecordset.MoveNext
Loop
objRecordSet.Close
objConnection.Close
Demonstration script that deletes all the records in a table named Hardware from an ADO database with the DSN "Inventory."
Open an ADO databae
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "DSN=Inventory;"
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT * FROM Hardware" , objConnection, _
adOpenStatic, adLockOptimistic
objRecordset.Close
objConnection.Close
Demonstration script that adds a new record to a database.
Const adOpenStatic = 3
Const adLockOptimistic = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = inventory.mdb"
objRecordSet.Open "SELECT * FROM GeneralProperties" , _
objConnection, adOpenStatic, adLockOptimistic
objRecordSet.AddNew
objRecordSet("ComputerName") = "atl-ws-01"
objRecordSet("Department") = "Human Resources"
objRecordSet("OSName") = "Microsoft Windows XP Professional"
objRecordSet("OSVersion") = "5.1.2600"
objRecordSet("OSManufacturer") = "Microsoft Corporation"
objRecordSet.Update
objRecordSet.Close
objConnection.Close
Queries a SQL Server table and searches a column for a specific user-defined string. The script then displays the result set in message boxes (or screen if using CScript).
'Setup our objects
Set ConWSH=WScript.CreateObject("ADODB.Connection")
'Define our connection string
strConnection = "Driver={SQL Server};Server=SERVER;User ID=USERID;Password=PASSWORD;Database=TEST;"
'and parameters
with ConWSH
.ConnectionString=strConnection
.ConnectionTimeout=25
.CommandTimeout=25
.Open
end with
'See what we're supposed to grab
strSearchTest = InputBox ("Search For:", "Who said that anyway?", "Enter Some Text")
'define our SQL statement
strSQL = "SELECT Column1, Column2 FROM tblTABLENAME WHERE Column1 like '%" & strSearchTest & "%'"
'Run the SQL statement
set rs=createobject("ADODB.Recordset")
with rs
.activeconnection=ConWSH
.CursorType=adOpenForwardOnly
.CursorLocation=3
.open strSQL
end with
'OK we've got a recordset to deal with. Let's run through it
'Check if it's empty or not
if rs.recordcount = 0 then
'Check if it's empty or not
MsgBox "No records found. Exiting."
'Exit with errorlevel 9. If we're called from a batch file this will indicate
'completion status/type so we can branch off to another handler if needed.
wScript.Quit(9)
else
'Recordset was not empty.
intrsCounter = 0
do while not rs.eof
strOutput=trim (rs.fields("Column1")) & vbNewLine & vbNewLine & trim(rs.fields("Column2"))
msgbox strOutput
rs.movenext
loop
end if
MsgBox "End of matching records."
'Clean up after ourselves, even though in WSH we don't really have to if the script ends here.
rs.close
set rs=nothing
ConWSH.close
set ConWSH=nothing
1