QTP访问数据库

1、连接数据库 

rem 创建数据库连接对象
Set conn = createObject("adodb.connection")
rem 指定数据源
conn.ConnectionString = "provider=microsoft.ace.oledb.12.0;Data Source=D:HPQuickTest Professionalsamplesflightappflight32.mdb"
conn.Open
If  conn.state =1 Then
   Set rs=createObject("adodb.recordset")
   sql="select order_number,customer_name from orders"
   rem 3：向前向后的游标   1：只读数据
   rs.Open sql,conn,3,1
      rem 记录行数
  msgbox rs.recordcount
  rem 指向前一行
  rs.movePrevious
   rem  是否位于第一条数据之前
   msgbox rs.bof   
rem  指向下一行    
    rs.MoveNext
   rem  是否位于最后一条数据之前
   msgbox rs.eof
   rem  获取当前第i列的数据
   msgbox rs(0)  
   msgbox rs(1)  
   rem  获取当前第i列的列名
  msgbox  rs.Fields(0).name


else 
    msgbox "连接失败"
End If

2、将数据库中的数据存到Excel表格中

rem 创建数据库连接对象
Set conn = createObject("adodb.connection")
rem 指定数据源
conn.ConnectionString = "provider=microsoft.ace.oledb.12.0;Data Source=D:HPQuickTest Professionalsamplesflightappflight32.mdb"
conn.Open
If  conn.state =1 Then
   Set rs=createObject("adodb.recordset")
   sql="select order_number,customer_name from orders"
   rem 3：向前向后的游标   1：只读数据
   rs.Open sql,conn,3,1
   rem  新建数据表
   dataTable.AddSheet "flight"
   rem  添加参数
   dataTable.GetSheet("flight").AddParameter rs.Fields(0).Name,rs(0)
   dataTable.GetSheet("flight").AddParameter rs.Fields(1).Name,rs(1)
  For i=2 to rs.RecordCount
      rs.MoveNext
      dataTable.GetSheet("flight").GetParameter(1).ValueByRow(i) = rs(0)
      dataTable.GetSheet("flight").GetParameter(2).ValueByRow(i) = rs(1)
  Next
  rem 关闭连接
   rs.close
   conn.close
   Set rs = Nothing
   Set conn = Nothing
else 
    msgbox "连接失败"
End If

3、连接数据库基本步骤

 

 

 4、不同数据库的连接字符串

访问 Access2010

conn.ConnectionString="provider=microsoft.ace.oledb.12.0;Data Source=E:HPQuickTest Professionalsamplesflightappflight32.mdb" 

访问 SQL Server 2008

conn.ConnectionString="Provider=SQLOLEDB.1;Data Source= 服 务 器 地 址 ; Persist Security Info=False;User ID=sa;password=数据库密码;Initial Catalog=数据库名" 

访问 Oracle11g

conn.ConnectionString = "Provider=OraOLEDB.Oracle.1;User ID=system;Password=123456;Data Source=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 服 务 器 IP)(PORT = 1521))) (CONNECT_DATA =(SERVICE_NAME = orcl)));Persist Security Info=False"