Database Connection using QTP

Access 97/2000/XP/2003 Database Connection String using Microsoft Access Driver

Dim strConnection, conn, rs, strSQL

strConnection = "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=" & Server.MapPath(".\db\db.mdb") & ";Uid=admin;Pwd=password;"

Set conn = Server.CreateObject("ADODB.Connection")

conn.Open strConnection

Set rs = Server.CreateObject("ADODB.recordset")

strSQL = "SELECT * FROM myTable"

rs.open strSQL, conn, 3,3

rs.MoveFirst

WHILE NOT rs.EOF

     Response.Write(rs("myField") & "<br/>")

rs.MoveNext

WEND

rs.Close

Set rs = Nothing

conn.Close

Set conn = Nothing

 

Excel 97/2000/XP/2003 Database Connection String using Microsoft Excel Driver

Dim strConnection, conn, rs, strSQL

strConnection = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;" & _
"Dbq=" & Server.MapPath(".\excel\myExcelFile.xls") & ";"

Set conn = Server.CreateObject("ADODB.Connection")

conn.Open strConnection

Set rs = Server.CreateObject("ADODB.recordset")

strSQL = "SELECT * FROM [sheet1$]"

rs.open strSQL, conn, 3,3

rs.MoveFirst

WHILE NOT rs.EOF

     Response.Write(rs("myColumn") & "<br/>")

rs.MoveNext

WEND

rs.Close

Set rs = Nothing

conn.Close

Set conn = Nothing

 

Comma Separated Values (.csv) Connection String using Microsoft Text Driver

Dim strConnection, conn, rs, strSQL

strConnection = "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"Dbq=" & Server.MapPath(".\csv\") & ";"Extensions=asc,csv,tab,txt;Persist Security Info=False"

Set conn = Server.CreateObject("ADODB.Connection")

conn.Open strConnection

Set rs = Server.CreateObject("ADODB.recordset")

strSQL = "SELECT * FROM myFile.csv"

rs.open strSQL, conn, 3,3

rs.MoveFirst

WHILE NOT rs.EOF

     Response.Write(rs("myColumn") & "<br/>")

rs.MoveNext

WEND

rs.Close

Set rs = Nothing

conn.Close

Set conn = Nothing

 

Oracle Database Connection String using Microsoft ODBC for Oracle

Dim strConnection, conn, rs, strSQL

strConnection = "Driver={Microsoft ODBC for Oracle};" & _
"Server=OracleServerName;uid=username;pwd=password;"

Set conn = Server.CreateObject("ADODB.Connection")

conn.Open strConnection

Set rs = Server.CreateObject("ADODB.recordset")

strSQL = "SELECT * FROM myTable"

rs.open strSQL, conn, 3,3

rs.MoveFirst

WHILE NOT rs.EOF

     Response.Write(rs("myField") & "<br/>")

rs.MoveNext

WEND

rs.Close

Set rs = Nothing

conn.Close

Set conn = Nothing

 

SQL Server Database Connection String using SQL Server Driver

Dim strConnection, conn, rs, strSQL

strConnection = "Driver={SQL Server};Server=SQLServerName;" & _
"Database=DBaseName;Uid=sa;Pwd=password;"

Set conn = Server.CreateObject("ADODB.Connection")

conn.Open strConnection

Set rs = Server.CreateObject("ADODB.recordset")

strSQL = "SELECT * FROM myTable"

rs.open strSQL, conn, 3,3

rs.MoveFirst

WHILE NOT rs.EOF

     Response.Write(rs("myField") & "<br/>")

rs.MoveNext

WEND

rs.Close

Set rs = Nothing

conn.Close

Set conn = Nothing