QTP Samples

How to set a "randomValue" to  variable and How to run an UPDATE in the Data Base using this variable:

'set a random number - this function will be called to do updates

Function RandomNr

upperbound = 1000-1

lowerbound = i

For i=1 to upperbound

Randomize

RndNr = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)

Next

RandomNr = RndNr

End Function

DB Update using the "RandomNr" function:

updateCol1data = UCASE("update tableName set ColumnHere = '" & RandomNr & "' WHERE columnName='value'")

'querying an "UPDATE" for a fields within DB

Public Function DBUpdate(updateCol1data)

 set conn = createobject("adodb.connection")

 conn.open "DSN=DSN_SERVERhere; UserID=userID; Password=password;"

 conn.Execute updateCol1data

End Function

Now to call this function use next:

DBUpdate updateCol1data

---

How to set a DB connection and get a value from an "adodb" Data Base:

Public Function DBSelect

set conn = createobject("adodb.connection")

conn.open "DSN=DSN_SERVERhere; UserID=userID; Password=password;"

set rs = createobject("adodb.recordset")

'get the date value for an Event from the DB

rs.open "SELECT searchColumnHere FROM tableName WHERE columnName='value'", conn

eventsInDB = rs("searchColumnHere")

'eventsInDB = FormatNumber(eventsInDB, 2) - use this if you need to format the output nr

rs.close

DBSelect = eventsInDB 'here we are defining the output value

End Function

Or, if the query is defined somewhere outside of your function, for e.g.:

getCol1data = UCASE("SELECT 'searchColumnHere' FROM tableName WHERE columnName='value'") '"searchColumnHere" is the code name of  the column which's value should be returned. for e.g: CLBVAS (use from your select), then the function can be written as next (I've included it inside a class):

Public Function DBQuery

 DBQuery = New Query

End Function

Class Query

 Public Function DBSelect(sqlSelect, searchColumnHere)

  set conn = createobject("adodb.connection")

  conn.open "DSN=DSN_SERVERhere; UserID=userID; Password=password;"

  set rs = createobject("adodb.recordset")

  'get the date value from DB for an Event

  rs.open sqlSelect, conn

  eventsInDB = rs(searchColumnHere)

  'eventsInDB = FormatNumber(eventsInDB, 2) - use this if you need to format the output number

  rs.close

  DBSelect = eventsInDB 'here we are defining the output value

 End Function

End Class

now the function is called this way:

DBQuery.DBSelect getCol1data "searchColumnHere"  'the "getCol1data" has the value assigned to it upper

or:

DBSelect getCol1data "searchColumnHere"  'when there is no class defined

---

How to record QTP script - QTP Video:

source: http://motevich.blogspot.com/