VBScript Database Objects

VBScript Database Objects

1) Database Connection Object

It is used to connect to Databases.
(* Database Connection String only varies from One Database to another)

Create Database Connection Object

Set variable = CreateObject("Adodb.Connection")
-------------------------
2) Database Recordset Object

It is used to perform operations on Database tables (records)

Create Database Recordset Object

Set variable = CreateObject("Adodb.Recordset")
-----------------------------------------------
Database Scripting Examples

1) Read test data from a database and perform Data driven Testing for Login Functionality.

Dim objConnection, objRecordset
'Creating Database Connection object, it is used to connect to databases
Set objConnection = CreateObject("Adodb.Connection")
'Creating Database Recordset Object, It is used to perform operations on Database Tables.
Set objRecordset = CreateObject("Adodb.Recordset")
'Create Connection String for MS Access Database
objConnection.Provider =("Microsoft.ACE.OLEDB.12.0")
objConnection.Open "C:\Users\G C REDDY\Desktop\testdb.accdb"
'Fetch ttest data using SQL statements
objRecordset.Open "Select Agent, Password from Login", objConnection

Do Until objRecordset.EOF = True
SystemUtil.Run "C:\Program Files\HP\Unified Functional Testing\samples\flight\app\flight4a.exe"
Dialog("Login").Activate
Dialog("Login").WinEdit("Agent Name:").Set objRecordset.Fields("Agent")
Dialog("Login").WinEdit("Password:").Set objRecordset.Fields("Password")
Wait 2
Dialog("Login").WinButton("OK").Click
Window("Flight Reservation").Close
objRecordset.MoveNext
Loop
objRecordset.Close
objConnection.Close

Set objRecordset = Nothing
Set objConnection = Nothing
-------------------------------------------
2) Read test data(Range of Records) from a database and perform Data driven Testing for Login Functionality.

Dim objConnection, objRecordset, RecordCount
'Creating Database Connection object, it is used to connect to databases
Set objConnection = CreateObject("Adodb.Connection")
'Creating Database Recordset Object, It is used to perform operations on Database Tables.
Set objRecordset = CreateObject("Adodb.Recordset")
'Create Connection String for MS Access Database
objConnection.Provider =("Microsoft.ACE.OLEDB.12.0")
objConnection.Open "C:\Users\G C REDDY\Desktop\testdb.accdb"
'Fetch ttest data using SQL statements
objRecordset.Open "Select Agent, Password from Login", objConnection

RecordCount = 1
Do Until objRecordset.EOF = True
If RecordCount > 1 And RecordCount < 5 Then
SystemUtil.Run "C:\Program Files\HP\Unified Functional Testing\samples\flight\app\flight4a.exe"
Dialog("Login").Activate
Dialog("Login").WinEdit("Agent Name:").Set objRecordset.Fields("Agent")
Dialog("Login").WinEdit("Password:").Set objRecordset.Fields("Password")
Wait 2
Dialog("Login").WinButton("OK").Click
Window("Flight Reservation").Close
End If
objRecordset.MoveNext
RecordCount = RecordCount + 1
Loop
objRecordset.Close
objConnection.Close

Set objRecordset = Nothing
Set objConnection = Nothing
-------------------------------------------------
3) Export data from a Database to Excel file.
 

Dim objConnection, objRecordset, objExcel, objWorkbook, objWorksheet
Set objConnection = CreateObject("Adodb.Connection")
Set objRecordset = CreateObject("Adodb.Recordset")

Set objExcel = CreateObject("Excel.Application")
Set objworkbook = objExcel.Workbooks.Open ("C:\Users\G C REDDY\Desktop\xyz.xlsx")
Set objWorksheet = objWorkbook.Worksheets(1)

objConnection.Provider =("Microsoft.ACE.OLEDB.12.0")
objConnection.Open "C:\Users\G C REDDY\Desktop\testdb.accdb"

objRecordset.Open "Select Agent, Password from Login", objConnection

objWorksheet.cells(1, 1) = "Agent"
objWorksheet.cells(1, 2) = "Password"

row = 2
While objRecordset.EOF = False
    objWorksheet.cells(row, 1) = objRecordset.Fields("Agent")
    objWorksheet.cells(row, 2) = objRecordset.Fields("Password")
    objRecordset.MoveNext
    row = row+1
Wend
objworkbook.Save
objExcel.Quit

Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
objRecordset.Close
objConnection.Close

Set objRecordset = Nothing
Set objConnection = Nothing
--------------------------------------------
4) Export data from a Database to Text file


Dim objConnection, objRecordset, objFso, objTextstream
Set objConnection = CreateObject("Adodb.Connection")
Set objRecordset = CreateObject("Adodb.Recordset")

Set objFso = CreateObject("Scripting.FileSystemObject")
Set objTextstream = objFso.OpenTextFile("C:\Users\G C REDDY\Desktop\abcd.txt", 2)

objConnection.Provider =("Microsoft.ACE.OLEDB.12.0")
objConnection.Open "C:\Users\G C REDDY\Desktop\testdb.accdb"

objRecordset.Open "Select Agent, Password from Login", objConnection

objTextstream.WriteLine "Agent Password"
objTextstream.WriteLine "---------"

While objRecordset.EOF = False
    objTextstream.WriteLine objRecordset.Fields("Agent") & ", "& objRecordset.Fields("Password")
    objRecordset.MoveNext
Wend
objTextstream.Close
Set objTextstream = Nothing
Set objFso = Nothing

objRecordset.Close
objConnection.Close

Set objRecordset = Nothing
Set objConnection = Nothing
------------------------------------------------
5) Export data from an Excel File to a Text file


Dim objExcel, objWorkbook, objWorksheet, objFso, objTextstream, RowsCount
Set objExcel = CreateObject("Excel.Application")
Set objworkbook = objExcel.Workbooks.Open ("C:\Users\G C REDDY\Desktop\xyz.xlsx")
Set objWorksheet = objWorkbook.Worksheets(1)

Set objFso = CreateObject("Scripting.FileSystemObject")
Set objTextstream = objFso.OpenTextFile("C:\Users\G C REDDY\Desktop\abcd.txt", 2)

RowsCount = objWorksheet.UsedRange.Rows.count

objTextstream.WriteLine "Agent Password"
objTextstream.WriteLine "---------"

For i = 2 To RowsCount Step 1
    objTextstream.WriteLine objWorksheet.Cells(i, 1) &", "& objWorksheet.Cells(i, 2)
Next
objTextstream.Close
Set objTextstream = Nothing
Set objFso = Nothing

objExcel.Quit
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
------------------------------------
6) Export data from a Text File to an Excel file.


Dim objExcel, objWorkbook, objWorksheet, objFso, objTextstream, LineCount, i
Dim myLine, myField
Set objExcel = CreateObject("Excel.Application")
Set objworkbook = objExcel.Workbooks.Open ("C:\Users\G C REDDY\Desktop\abc.xlsx")
Set objWorksheet = objWorkbook.Worksheets(3)

Set objFso = CreateObject("Scripting.FileSystemObject")
Set objTextstream = objFso.OpenTextFile("C:\Users\G C REDDY\Desktop\abcd.txt", 1)

RowsCount = objWorksheet.UsedRange.Rows.count

objWorksheet.cells(1, 1) = "Agent"
objWorksheet.cells(1, 2) = "Password"
LineCount=1
i=2
While objTextstream.AtEndOfStream = False
    myLine = objTextstream.ReadLine
    If LineCount > 2 Then
   
        myField = Split(myLine, ", ")
        objWorksheet.cells(i, 1) = myField(0)
        objWorksheet.cells(i, 2) = myField(1)
        i=i+1
    End If
    LineCount=LineCount + 1
Wend
objTextstream.Close
Set objTextstream = Nothing
Set objFso = Nothing

objWorkbook.Save
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
-----------------------------------------------

0 comments:

Post a Comment