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

Follow me on social media: