QTP -Testcomplete-Testing-Loadrunner-Selenium-Blog

QTP DATABASE Quering And Storing the result in Excel

Posted in QTP by Sandy runner on November 27, 2009

Here are few Steps to Remember How to work around with Database

1 Create a DATABASE OBJECT ->ADODB OBJECT and connect it to the DSn(Connection String) using  Open mEthod

2 Create  Recordset object,then write a Query String then attach the RecordSet Object  and connect it to the DATABASE OBJECT ->ADODB OBJECT

3. Use the Record Set object and loop the Data in the Database

Below I have used to Connect the QTP FLight Application Database and retrieve all the data  and dump all the data in Excel Sheet .

just Copy the code into QTP editor  & run your Script .


'Create object of  Excel
Set oExcel=CreateObject("Excel.Application")

'Create a Work Book
oExcel.workbooks.add

'Make it visible
oExcel.visible=True

'Create a New Sheet Name

Set NewSheet = oExcel.Sheets.Item(1)
NewSheet.Name = "QT Flight  DATABASE DETAILS"

'Create a Database Connection
Set oConn = CreateObject("ADODB.Connection")
oConn.Open "QT_Flight32"
set oRec = CreateObject("ADODB.recordset")
oRec.open "Select * from Orders",oConn
row=1
Do until oRec.EOF
For each i in oRec.Fields
NewSheet.Cells(row,1) = i.Name
NewSheet.Cells(row,2) = i.Value
row = row + 1
Next
NewSheet.Columns("A:A").ColumnWidth = 40
NewSheet.Columns("A:A").Font.Bold = True
NewSheet.Columns("B:B").ColumnWidth = 60
NewSheet.Columns("B:B").HorizontalAlignment = -4108 '
NewSheet.Range("A:CC").Columns.AutoFit
oRec.movenext
Loop
oExcel.ActiveWorkbook.SaveAs "C:\database.xls"
oExcel.Quit
Set oExcel = Nothing
oRec.close

Advertisements

4 Responses

Subscribe to comments with RSS.

  1. Rekka said, on May 24, 2010 at 1:59 pm

    Santosh

    Your blog is very useful for us to work. The above script is not working for me it shows
    Error msg on Line 13: NewSheet.Name = “QT Flight  DATABASE DETAILS”

    It shows the Excel sheet and then popup the error message.I have one doubt incase of the flight application can i change to Calculator will this script work?

    Help me out!

    Thanks in Advance
    Rekka

  2. Akansha said, on August 4, 2010 at 7:41 am

    Hi Santhosh,
    This is Akansha. Browsed your site, very good site with lot of information especially for testing professionals. Have a question, Do u give training on Test Partner and Selenium tool? Pls let me know.

    Thanks,
    Akansha

    • SANTOSH BABY said, on August 14, 2010 at 2:20 pm

      Hi Akasha not as a professiional trainer ,but can help and guide you


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: