mistux Site Admin
Joined: 25 Jun 2004 Posts: 1042 Location: South Bend, Indiana USA
|
Posted: Fri May 02, 2008 1:00 pm Post subject: Working with Databases (Many code samples) |
|
|
Source:
http://www.vbsedit.com/scripts/misc/database/default.asp
(They have a very good VB Script Editor, check it out.)
=============================================
' Add a New Record to a Table
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
======================================
' Clear a Database Table
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 "Delete * FROM Hardware" , objConnection, _
adOpenStatic, adLockOptimistic
objConnection.Close
============================
' Connect to a SQL Server Database
Const adOpenStatic = 3
Const adLockOptimistic = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
objConnection.Open _
"Provider=SQLOLEDB;Data Source=atl-sql-01;" &; _
"Trusted_Connection=Yes;Initial Catalog=Northwind;" &; _
"User ID=fabrikam\kenmyer;Password=34DE6t4G!;"
objRecordSet.Open "SELECT * FROM Customers", _
objConnection, adOpenStatic, adLockOptimistic
objRecordSet.MoveFirst
Wscript.Echo objRecordSet.RecordCount
=============================
' Connect to an ADO Database
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
============================
' Create a JET Database
Set objConnection = CreateObject("ADOX.Catalog")
objConnection.Create _
"Provider = Microsoft.Jet.OLEDB.4.0; " &; _
"Data Source = new_db.mdb"
===========================
' Create a Table in a JET Database
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open _
"Provider= Microsoft.Jet.OLEDB.4.0; " &; _
"Data Source=new_db.mdb"
objConnection.Execute "CREATE TABLE EventTable(" &; _
"EventKey COUNTER ," &; _
"Category TEXT(50) ," &; _
"ComputerName TEXT(50) ," &; _
"EventCode INTEGER ," &; _
"RecordNumber INTEGER ," &; _
"SourceName TEXT(50) ," &; _
"TimeWritten DATETIME ," &; _
"UserName TEXT(50) ," &; _
"EventType TEXT(50) ," &; _
"Logfile TEXT(50) ," &; _
"Message MEMO)"
objConnection.Close
===========================
' Delete a Record from a Recordset
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
=========================
' Delete Multiple Records from a Table
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 "DELETE * FROM GeneralProperties WHERE " &; _
"Department = 'Human Resources'", _
objConnection, adOpenStatic, adLockOptimistic
objConnection.Close
=======================
' List Basic Statistics Derived from a Recordset
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
=============================
' List the Number of Records in a Recordset
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" , _
objConnection, adOpenStatic, adLockOptimistic
objRecordSet.MoveFirst
Wscript.Echo "Number of records: " &; objRecordset.RecordCount
objRecordSet.Close
objConnection.Close
===========================
' List the Top 25 Records in a Recordset
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 TOP 25 * FROM GeneralProperties " &; _
"ORDER BY TotalPhysicalMemory DESC", _
objConnection, adOpenStatic, adLockOptimistic
objRecordSet.MoveFirst
Do Until objRecordset.EOF
Wscript.Echo objRecordset.Fields.Item("ComputerName") &; _
vbTab &; objRecordset.Fields.Item("TotalPhysicalMemory")
objRecordset.MoveNext
Loop
objRecordSet.Close
objConnection.Close
==========================
' List Unique Records in a Recordset
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 DISTINCT OSName FROM " &; _
"GeneralProperties ORDER BY OSName", _
objConnection, adOpenStatic, adLockOptimistic
objRecordSet.MoveFirst
Do Until objRecordset.EOF
Wscript.Echo objRecordset.Fields.Item("OSName")
objRecordset.MoveNext
Loop
objRecordSet.Close
objConnection.Close
==========================
' Open a Database Using a DSN
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
============================
' Open Two Recordsets
Const adOpenStatic = 3
Const adLockOptimistic = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
Set objRecordSet2 = CreateObject("ADODB.Recordset")
objConnection.Open _
"Provider= Microsoft.Jet.OLEDB.4.0; " &; _
"Data Source=inventory.mdb"
objRecordSet.Open "SELECT * FROM GeneralProperties Where ComputerName = 'Computer1'", _
objConnection, adOpenStatic, adLockOptimistic
objRecordSet.MoveFirst
objRecordSet2.Open "SELECT * FROM Storage Where ComputerName = 'Computer1'", _
objConnection, adOpenStatic, adLockOptimistic
objRecordSet2.MoveFirst
Do Until objRecordset.EOF
Wscript.Echo objRecordset.Fields.Item("ComputerName")
Wscript.Echo objRecordset.Fields.Item("OSName")
objRecordSet.MoveNext
Loop
Do Until objRecordset2.EOF
Wscript.Echo objRecordset2.Fields.Item("DriveName"), _
objRecordset2.Fields.Item("DriveDescription")
objRecordSet2.MoveNext
Loop
objRecordSet.Close
objRecordSet2.Close
objConnection.Close
========================
' Save a Recordset in XML format
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adPersistXML = 1
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" , _
objConnection, adOpenStatic, adLockOptimistic
objRecordSet.MoveFirst
objRecordSet.Save "output.xml", adPersistXML
objRecordSet.Close
objConnection.Close
=========================
'Search a Database Using a LIKE Query
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
========================
' Search a Database Using Numeric Criteria
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 EventCode = 1054", _
objConnection, adOpenStatic, adLockOptimistic
objRecordSet.MoveFirst
Wscript.Echo "Number of records: " &; objRecordset.RecordCount
objRecordSet.Close
objConnection.Close
=========================
' Search a Database Using Variable Criteria
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"
dtmDate = "#1/7/2004#"
objRecordSet.Open "SELECT * FROM EventTable Where TimeWritten = " &; dtmDate, objconnection, adOpenStatic, adLockOptimistic
objRecordSet.MoveFirst
Do Until objRecordSet.EOF
Wscript.Echo objRecordset.Fields.Item("EventCode") &; vbTab _
&; objRecordset.Fields.Item("Logfile")
objRecordSet.MoveNext
Loop
=======================
' Search for a Record in a Recordset
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
If objRecordset.EOF Then
Wscript.Echo "Record cannot be found."
Else
Wscript.Echo "Record found."
End If
objRecordset.Close
objConnection.Close
===========================
' Searching a Database Using String Criteria
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 Type = 'Error'", objConnection, adOpenStatic, _
adLockOptimistic
objRecordSet.MoveFirst
Wscript.Echo "Number of records: " &; objRecordset.RecordCount
objRecordSet.Close
objConnection.Close
=============================
' Sort a Recordset
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
===========================
' Sort a Recordset on Multiple Fields
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, Logfile DESC", _
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
=========================
' Update a Record in a Recordset
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
=============================
' Update Multiple Records in a Recordset
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 "UPDATE GeneralProperties SET " &; _
"Department = 'Accounting'", _
objConnection, adOpenStatic, adLockOptimistic
objConnection.Close
============================== |
|