Connecting to excel as database using Adodb in VBScript/QTP

We can connect to excel file using Adodb object in vbscript.  Following are the steps in working with excel as database.

1.       Create an instance of connection :

Set objConn = CreateObject("Adodb.connection")

2.       Create connection string to connect to excel database as follows

strCon = "Driver={Driver do Microsoft Excel(*.xls)};DriverId=790;Dbq=" & Workbook &";"

Here we have created a database for excel Workbook . The sheets in the workbook acts as table of the database.

3.       Connect to database as shown below:

objConn.open strCon

Now we are done with the connection to excel , we require SQL to run on the data to extract required information . 

4. Create a SQL as required as shown below:

strSQL =  "select name from [Table1$];"

Note here, we have to give sheet name acting as table within square bracket and with suffix “$” as shown above.

5.   Create a recordset as shown below and execute the query using recordset, all  the records based on query will be stored in recordset.

Set rs = CreateObject("ADODB.recordset")

rs.Open strSQL,objConn

6.       We can transfer the data extracted from query back in excel using copyrecordset method

XLSheet.range("A2").copyfromrecordset rs

Where XLSheet is the reference of required sheet in the excel  file and A2 is the location in sheet from which data will be pushed in the sheet.

Reporting in QTP - Reporter Object

When we run a test in QTP, QTP generates a report based on inbuilt native reporting for steps  defined in QTP. To add user defined results, we have reporter object in QTP.

Below are the properties  and methods  of reporter object which will be described in details below.

Reporter.Filter Property 

Retrieves or sets the current mode for displaying events in the Test Results. You can use this property to completely disable or enable reporting of steps following the statement, or you can indicate that you only want subsequent failed or failed and warning steps to be included in the report.

Reporter.filter = FilterValue.

Filter value can be

·         0 or rfEnableAll – Default , all reporting events are displayed.

·         1 or rfEnableErrorandWarnings – only error and warning events are displayed.

·         2 or rfEnableErrorsOnly – Only errors are displayed.

·         3 or rfDisableAll – No event are displayed in results.

Reporter.ReportEvent Method

Reports an event to the results

Syntax: Reporter.reportEvent  eventstatus, reportStepName, Details,[Image]

EventStatus can have value 0, 1,2,3 or micPass,micFail, micDone, and micwarning.

reportStepName – Name of the steps

Details – Details of execution.

Image – Path of imagefile to be attached for event.

Reporter.ReportPath property

 This retrieves the folder Path in which current test results are saved.

Reporter.RunStatus property

Retrieves the run status at the current point of the run session. For tests, it returns the status of current test during the test run.

Working with DataBase in QTP - Understanding Connection and Recordset object

QTP Interacts with database creating an instance of ADODB object using ADO. ADO is a Microsoft technology and stands for ActiveX Data Objects and is a programming interface to interact and access data from a database.

Connection Object

Before a database can be accessed by QTP, An object for database connection has to be established.

Set adocon = createobject(“Adodb.connection”)

Once the object is created, we need to set connection string to connect to the database. We can define a connection string with details of database including database provider, database, and user credentials for access.

Some useful methods and properties of connection object are as follows:

Properties:

adocon.connectionstring – This property sets or returns the details used to create a connection to a data source. Includes details of driver, database,username and password.

Strconnection = "Driver=… "Server=svrnme;uid=username;pwd=password;"

For e.g to connect to a database for excel the strConnection would be like:

 strcon = "Driver={Microsoft Excel Driver (*.xls)};Dbqls="+xfilename +";ReadOnly=0;"

For details of connection strings, see www.connectionstrings.com

adocon.ConnectionTimeout – this defines the time  to wait for a connection to be established.

adocon.provider – This sets or gets connection provider name.

adocon.state – gives status whether connection is on or off.

Methods

adocon.open – opens a database connection based on the connection string provided. 

adocon.Open connectionstring,userID,password,options

adocon.Execute – execute the sql statement provided

adocon.execute “Select * from table”

adocon.close – This closes the adodb connection.

RecordSet Object:

 Once a connection has been established, we can create recordset object to hold a set of record from database. A recorset consists of records and column

Set rs = createobject(“”Adodb.recordset”)

Some useful methods and properties of RecordSet Objects are as follows:

Properties:

BOF property returns True  if the current record position is before the first record in the Recordset,

EOF property returns True if the current record position is after the last record in the Recordset, otherwise it returns False. For a empty recordset,i.e no records in the recordset or empty recordset, value of BOF and EOF is false. So the property can be used in a loop to validate RecordSet does not return any records.

MaxCount Property returns the maximum value of records to be returned in a recordset.
rs.MaxCount = 20 will return 20 rows of data in recordset.

Methods:

rs.cancel – cancels an existing execution.

rs.clone – returns a clone of existing recorset and assigns to an object

set rsclone = rs.clone

rs.Close - closes instance of recordset

rs.open – opens a recordset based on query specified.

rs.open sqlquery, adocon

where sqlquery is query executed and adocon is connection object.

rs.move – moves the pointer in a recordset by specified count as defined in numrec
rs.move numrec, start.

Also  movenext,moveprevious, movefirst, movelast can be used to move to specified location in recordset.

rs.fields.count gives number of items in the fields collection.

rs.field.item(i) returns specified item from the collection.