VB & QTP : Fetching Data from DB and putting it into a 2D array

Fetching Data from DB and putting it into a 2D array

http://www.gcreddy.com/2010/03/database-connections.html

http://www.qtpschool.com/2011/05/database-connection-using-qtp.html

http://qtpvbs.blogspot.in/2010/06/function-to-retrieve-query-result-into.html 

Public Function Func_GetQueryResult(sQuery)

Dim con,rs ,loopCounter,var,counter

counter=0

Dim resArr()

Set con=CreateObject("ADODB.Connection")

Set rs=CreateObject("ADODB.recordset")

con.open"provider=SQLOLEDB;Server=servername;Databae=dbname;Userid= ;Password= :"

rs.open sQuery,con

While not rs.EOF 'Count no of rows in your result

 loopCounter=loopCounter+1

 rs.MoveNext

Wend

If loopCounter = 0 Then

 Reporter.ReportEvent micWarning,"Empty Result Set","Query is: " & sQuery

 Func_GetQueryResult=False

 Exit Function

End If

rs.MoveFirst 'move the recordset object to first row again

ReDim resArr(loopCounter,rs.fields.count-1)

Do until rs.EOF 'Rows

  For var=0 to rs.fields.count-1 'cols

    If counter=0 Then

     resArr(counter,var) =rs.fields(var).name

     else

     resArr(counter,var) =rs.fields(var).value

    end if

  Next

 rs.MoveNext

 counter=counter+1

Loop

rs.Close

con.Close

Func_GetQueryResult=resArr

End Function

'////Connection string can also be -"DRIVER=SQL Server; DATABASE=" & DBName & ";APP=QuickTest Professional;SERVER=" & DBServer &";Description=Testconnection"