Qtp database Problems

Access SQL database through qtp

Dim conn,res

Set conn = createobject("adodb.connection")
connstr =  "Provider=SQLOLEDB.1;Password=Password01!;Persist Security Info=True;User ID=sa;Initial Catalog=PCRM2;Data Source=ws-gisgtssts-03\paypaltestserver"
conn.Open connstr
Set res = createobject("adodb.recordset")

res.CursorLocation = 3 'adUserClient
res.CursorType = 2  'adOpenDynamic

strSQL = "select count(CreatedOn) as Expr1 from TaskBase"
res.Open strSQL,conn

While not res.EOF
msgbox "val1: " & res("Expr1")     ' res.Fields("Expr1").Value 
    res.MoveNext
Wend

res.Close
Set res = nothing
conn.Close
Set conn = nothing

 

Dim conn,rowsAffected

Set conn = createobject("adodb.connection")

connstr =  "Provider=SQLOLEDB.1;Password=Password01!;Persist Security Info=True;User ID=sa;Initial Catalog=student;Data Source=HRC-OV9RO6JPTEH"
conn.Open connstr

delStr = "delete from stuScore where stuNum = '1001'"

conn.Execute delStr,rowsAffected

If rowsAffected = 0 Then
msgbox "no rows were deleted"
else
msgbox  rowsAffected & " row(s) were deleted"
End If

conn.Close
Set conn = nothing

Connect to excel using ADO automation components and run query statements

'Force variable Declaration
Option explicit
'Declare the conn database link object and RS result set object
Dim Conn, RS
'Declare the database link string
Dim connectionstring
Connectionstring = "DSN = Excel files; DBQ = c: \ readxml \ book1.xls ;"
'Declare SQL Execution statements
Dim sqlstring
Sqlstring = "select * from [sheet1 $]"
'Create an ADO component object
Set conn = Createobject ("ADODB. Connection ")
'Open the database
Conn. Open connectionstring
'Execute the SQL statement and obtain the result set.
Set rs = conn. Execute (sqlstring)
'Traverse the username field data in the result set
While not Rs. EOF
Msgbox (Rs. Fields ("username"). value)
Rs. movenext

Wend
'Close the result set
Rs. Close
'Close the database
Conn. Close

'Release the object
Set sqlstring = nothing
Set connectionstring = nothing
Set rs = nothing
Set conn = nothing

Qtp accessing Oracle Database

Dim RS, SQ, pkeyset conn = Createobject ("ADODB. connection ") set rs = Createobject (" ADODB. recordset ") 'you need to install the Oracle client connstr =" provider = oraoledb. oracle.1; persist Security info = false; user id = bpo30; Data Source = bpo30; Password = bpo30; host = 172.17.5.6; Port = 1521; "Conn. open connstr SQL = "select * from staff" Rs. open SQL, connrs. movefirstdo while Rs. EOF <> truemsgbox Rs. fields (0) rs. movenextlooprs. closeset rs = nothingconn. closeset conn = nothing

 

Related:

Dim res,cmd,sqlSet res = createobject("adodb.recordset")Set cmd = createobject("adodb.command")Set conn = createobject("adodb.connection")connStr = "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=bpo30;Data Source=bpo30;Password=bpo30;Host=172.17.5.6;Port=1521;"cmd.ActiveConnection = connStrconn.Open connStrcmd.CommandType = 1sql = "select count(*) from staff"cmd.CommandText = 1Set res = conn.Execute(sql)msgbox res(0)res.CloseSet res = nothingSet cmd.ActiveConnection = nothingSet cmd = nothing