Demonstration Script That Uses a DSN to Open a Database Named Northwind

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