User Form with QTP – Learn2Automate

If you’re wondering why the hell would anyone use QTP to create such an Application ? You’re correct !!! But recently I came across a situation wherein I wanted to receive input from Multiple Users. Approaches I thought of :-

1. Using Input Box – This proved to be a tedious task as there were around 10 parameters to be fetched and displaying an input box every time didn’t seem to be a good approach.

2. Creating a Excel Sheet with all the Parameters – This might have worked for 2-3 people but I wanted multiple people to access the application simultaneously.

So I thought why not create an Client-Server Application which the users can access and give input that will be stored in a centralized database. Here are the two things I used for this :-

1. DotNetFactory Object

2. ADODB Connection to connect MySQL Database.

Part 1 – Creating a User Form to Accept Input

Set oPoint = DotNetFactory.CreateInstance(“System.Drawing.Point”, “System.Drawing”, x, y) ‘ Used to store x,y coordinates

Set dResult = DotNetFactory.CreateInstance(“System.Windows.Forms.DialogResult”, “System.Windows.Forms”)

Set lblDummy = DOTNetFactory.CreateInstance(“System.Windows.Forms.Label”) ‘Creates a Label
oPoint.x = 50 : oPoint.y = 31
Set lblDummy.Location = oPoint
lblDummy.Text=”Dummy Label :” ‘Setting the Caption for the Label

Set cmbDummy = DOTNetFactory.CreateInstance(“System.Windows.Forms.ComboBox”) ‘Creates Combo Box
oPoint.x = 150 : oPoint.y = 29
Set cmbDummy.Location = oPoint
cmbDummy.Name = “cmbDummy ”
cmbDummy.Width = 350 : cmbDummy.Height = 21
cmbDummy.Items.Add “Value 1”
cmbDummy.Items.Add “Value 2” ‘Adding Values to the Combo Box

Set txtDummy = DOTNetFactory.CreateInstance(“System.Windows.Forms.TextBox”)
oPoint.x = 190 : oPoint.y = 350
Set txtDummy.Location = oPoint
txtDummy.Width = 250 : txtDummy.Height = 200

Set GroupBox1 = DOTNetFactory.CreateInstance(“System.Windows.Forms.GroupBox”) ‘ Using Group Box as a Container Object

GroupBox1.Controls.Add lblDummy
GroupBox1.Controls.Add cmbDummy
GroupBox1.Controls.Add txtDummy

Set btnSubmit = DotNetFactory.CreateInstance(“System.Windows.Forms.Button”) ‘Creating a Submit Button
oPoint.x = 150 : oPoint.y = 550
Set btnSubmit.Location = oPoint
btnSubmit.Width = 104 : btnSubmit.Height = 38
btnSubmit.Text = “Submit”
btnSubmit.DialogResult=dResult.Ok

Set btnCancel = DotNetFactory.CreateInstance(“System.Windows.Forms.Button”) ‘Creating a Cancel Button
oPoint.x = 350 : oPoint.y = 550
Set btnCancel.Location = oPoint
btnCancel.Width = 104 : btnCancel.Height = 38
btnCancel.Text = “Quit”
btnCancel.DialogResult=dResult.Cancel

Set Form = DotNetFactory.CreateInstance(“System.Windows.Forms.Form”) ‘Creating the Form to Display all the Objects
Form.Show()
Form.Controls.Add btnSubmit
Form.Controls.Add btnCancel
Form.Controls.Add GroupBox1

Form.Width = 600 : Form.Height = 650
Form.Text = “Client Server Application using QTP” ‘Setting the Title of the Form

Part 2 – Creating a Back-End Database to Store the Results

In this case, we’ll be using MySQL as the Database to store the user input.

Prerequisites :

On the Server Machine, install MySQL Database Server from the Official Website.Also, install any 3rd party tool (like Navicat) to access the Database.
On the Client Machines, its mandatory to have My SQL ODBC Connector 5.1 (available from the Official Website) in order to interact with the Database present in the Server Machine.

Dim con, rs

Set con=CreateObject(“adodb.connection”)
Set rs=CreateObject(“adodb.recordset”)

con.open “DRIVER={MySQL ODBC 5.1 Driver};SERVER=hostname;DATABASE=dbname;USER=user1;PASSWORD=password1;OPTION=3;”

‘Here User1 is the user that I have created explicitly in the MySQL Database and I have assigned the appropriate Priviledges to the user in order to enter data in the Table.

‘Please note : If you are running the code in the Server Machine itself, then use “localhost” as the Server name, other the Hostname of the Server machine.

rs.Open “SELECT * FROM Table1”,con,3,3 ‘Table 1 is the Table where I need to Insert the Data
rs.AddNew ‘Adding a New Record

rs(“Dummy1”)=cmbDummy.Text
rs(“Dummy2”)=txtDummy.Text ‘Inserting the Values(obtained from the form) into the Database

rs.Update
rs.Close
con.Close ‘Closing the Connection with the Database

Here is the Full Code :-

Set con=CreateObject(“adodb.connection”)

Set rs=CreateObject(“adodb.recordset”)

con.open “DRIVER={MySQL ODBC 5.1 Driver};SERVER=hostname;DATABASE=dbname;USER=user1;PASSWORD=password1;OPTION=3;”

Set oPoint = DotNetFactory.CreateInstance(“System.Drawing.Point”, “System.Drawing”, x, y) ‘ Used to store x,y coordinates

Set txtDummy = DOTNetFactory.CreateInstance(“System.Windows.Forms.TextBox”)
oPoint.x = 190 : oPoint.y = 350
Set txtDummy.Location = oPoint
txtDummy.Width = 250 : txtDummy.Height = 200

Set GroupBox1 = DOTNetFactory.CreateInstance(“System.Windows.Forms.GroupBox”) ‘ Using Group Box as a Container Object

GroupBox1.Controls.Add lblDummy
GroupBox1.Controls.Add cmbDummy
GroupBox1.Controls.Add txtDummy

Form.Width = 600 : Form.Height = 650
Form.Text = “Client Server Application using QTP” ‘Setting the Title of the Form

Form.Hide() ‘Form needs to be Hidden in order to Call the ShowDialog Function

Form.ShowDialog() ‘Enables the Form to accept input from the User

On Error Resume Next

If Form.DialogResult=dResult.Ok Then ‘If user presses Submit Button

rs.Open “SELECT * FROM Table1”,con,3,3 ‘Table 1 is the Table where I need to Insert the Data
rs.AddNew ‘Adding a New Record

rs(“Dummy1”)=cmbDummy.Text
rs(“Dummy2”)=txtDummy.Text ‘Inserting the Values(obtained from the form) into the Database

rs.Update
rs.Close

If Err.Number <>0 Then ‘Showing the Error Incase there is any

Msgbox “Error Number = ” &Err.Number

Msgbox “Error Description = ” &Err.Description

End If

Else ‘User pressed the Cancel Button

Msgbox “Bye !!!”

End If

con.Close() ‘Closing the Connection to the DB

Form.Dispose() ‘Destroying the Form

Tag: User Form with QTP

How to Create a Client-Server Application with QTP

Share this: