Please post your Web Driver questions in official Web Driver forum

Thursday, April 1, 2010

VB Script and ADO DB

VB Script can be used to establish db connection. This is very useful in QTP for writing db test cases. DB connection can be established for both SQL Server Authentication as well as Windows Authentication -

#########################################
Function Func_dbConnect

On Error Resume Next

'Creating the Connection Object
set cn = CreateObject("ADODB.Connection")

'Setting the Data Source
strCS = "Driver={SQL Server}; Server=1.1.1.1; Database=GSBREG_021010; UID=us; PWD=us;"

'Open Connection
cn.Open strCS

If (Err.Number<>0) Then
MsgBox("Login Failed")
End If

Set Func_dbConnect = cn

End Function


Sub ExecuteSQLQuery

Set cn = Func_dbConnect()

'Creating RecordSet Object
Set rs = CreateObject("ADODB.recordset")
rs.CursorType = 2

'SQL query
FetchAcademicLevels = "Select even_id from allocation_event"_
&" where allocation_name = 'myname'"

Set rs = cn.Execute(FetchAcademicLevels)

If(rs.EOF) Then
MsgBox "No Records"
Else

For Each x in rs.Fields
MsgBox x.name
MsgBox x.value
Next
rs.MoveNext

End If

rs.Close
cn.Close

End Sub


ExecuteSQLQuery()
#########################################

For Windows Authentication Connection String should be -

#########################################
strCS = "Driver={SQL Server}; Server=171.64.216.197; Database=copernicus_gsbreg_b2b_new; Trusted_Connection=Yes"
#########################################
Fork me on GitHub