ADO (Active Data Objects), this technology allows users to access data easily from many existing databases (such as Access or Paradox) or from ODBC compliant databases like Oracle or MS SQL Server. Using ADO is quite simple and allows programmers to provide flexible database front ends to users that are reliable and include many features.
Following are some of the key objects found in the ADO object model and some of their key methods and properties.
Connection Object
This object represents an open connection to the data source. This connection can be a local connection (say App.Path) or can be across a network in a client server application. Some of the methods and properties of this object are not available depending on the type of data source connected to.
Command Object
A command object specifies a specific method we intend to execute on or against the data source accessed by an open connection.
Record Set Object
The Record Set object represents a complete set of records from an executed command or from an underlying base table in the database. A key thing to note is that a Record Set object references only one record at a time as the current record.
Examples
1) Get Test Data from a Database and use in Data Driven Testing (through Scripting)
1) Dim con,rs
2) Set con=createobject("Adodb.connection")
3) Set rs=createobject("Adodb.recordset")
4) con.provider=("microsoft.jet.oledb.4.0")
5) con.open "C:\Documents and Settings\Administrator\My Documents\QTPInside.mdb"
6) rs.open "Select * From Login",con
7) While rs.eof <>True
8) SystemUtil.Run "C:\Program Files\Mercury Interactive\QuickTest Professional\samples\flight\app\flight4a.exe"
9) Dialog("Login").Activate
10) Dialog("Login").WinEdit("Agent Name:").Set rs.fields ("Agent")
11) Dialog("Login").WinEdit("Password:").Set rs.fields ("Password")
12) Dialog("Login").WinButton("OK").Click
13) Window("Flight Reservation").Close
14) rs.movenext
15) Wend
2) Exporting Data from a Database to an Excel Sheet
1) Dim con,rs
2) Set con=createobject("adodb.connection")
3) Set rs=createobject("adodb.recordset")
4) con.provider="microsoft.jet.oledb.4.0"
5) con.open"C:\Documents and Settings\admin\My Documents\QTPInside.mdb"
6) rs.open"select*from Login",con
7) Set ex=createobject("Excel.Application")
8) Set a=ex.workbooks.open("C:\Documents and Settings\admin\My Documents\QTPInside.xls")
9) Set b=a.worksheets("sheet1")
10) i=1
11) Do While Not rs.EOF
12) b.cells (i,1).value=rs.fields("agent")
13) b.cells(i,2).value=rs.fields("password")
14) rs.movenext
15) i=i+1
16) Loop
17) a.save
18) a.close
3) Exporting Data from a Database to a Text file
Dim objCon,objRs,ObjFso,myFile,myData,rc,r
Set objCon=createobject("Adodb.connection")
Set objRs=createobject("Adodb.Recordset")
set objFso=createobject("Scripting.Filesystemobject")
Set myFile=objFso.OpenTextFile("C:\Documents and Settings\gcr\My Documents\anand.txt",8)
objcon.provider=("Microsoft.jet.oledb.4.0")
objcon.open"C:\Documents and Settings\gcr\My Documents\QTPInside.mdb"
objrs.open "select * from login",objCon
r=1
Do until objRs.EOF
a=objRs.Fields ("Agent")
b=objRs.Fields ("Pwd")
myFile.Writeline a &","& b
r=r+1
objRs.MoveNext
Loop
myFile.Close
objCon.Close
4) Connecting to a SQL Sever 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
5) 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
6) Open Two Record sets
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
7) 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
8) Insert Data into a database table using Database Command Object
Dim objCon,objCom
Set objCon=Createobject("ADODB.connection")
objCon.open"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\QTPInside.mdb;"
Set objCom=Createobject("ADODB.Command")
objCom.ActiveConnection=objCon
objCom.CommandText="insert into Emp values('QTPInside',88233,30000)"
objCom.Execute
objCon.Close
Set objCom=Nothing
Set objCon=Nothing
9) Insert multiple sets of Data (using Excel sheet) into a database table using Database Command Object
Dim objCon,objCom,strEmpName,intEmpNo,intEmpSal,intRowcount,i
Set objCon=Createobject("ADODB.connection")
objCon.open"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\QTPInside.mdb;"
Set objCom=Createobject("ADODB.Command")
objCom.ActiveConnection=objCon
Datatable.AddSheet("input")
Datatable.ImportSheet "C:\QTPInside.xls",1,"input"
intRowcount=Datatable.GetSheet("input").GetRowCount
Msgbox intRowcount
For i=1 to intRowcount step 1
DataTable.SetCurrentRow(i)
strEmpName= DataTable.Value(1,"input")
intEmpNo= DataTable.Value(2,"input")
intEmpSal= DataTable.Value(3,"input")
objCom.CommandText="insert into Emp values( '"&strEmpName&" ',"&intEmpNo&","&intEmpSal&")"
objCom.Execute
Next
objCon.Close
Set objCom=Nothing
Set objCon=Nothing
