Connecting to Oracle using OLE and a DSNLess Connection - Using Libraries

Source Code

Typically if you are connecting to a database on a regular basis, it makes more sense to make commonly used code into a function, such as connecting to the database, which can be reused by many scripts. The following example uses a libary file called ASPLib.asp to include a function to connect to the database -getdBConnection, plus the procedure query2table found in the Access examples, that can take the results from any query and convert it to table output.

The example below uses the OLE connection used before. It includes the library file: ASPLib.asp and the ASP file that uses both these functions.

Library File - ASPLib.asp


Function getdBConnection()
  Dim dbConn
  SET dbConn=SERVER.createobject("adodb.connection")
    dbConn .ConnectionString="Provider=MSDAORA; Data Source=ora12c;" & _
      "User ID=myOracleUsername;" & "Password=myOraclePassword"

  SET getdBConnection=dbConn
End Function



SUB query2table(inputquery, conntemp)

DIM rstemp
SET rstemp=conntemp.execute(inputquery)
howmanyfields=rstemp.fields.count -1%>
<table border="2" bgcolor="#E1E2B4"><tr>
<% 'Put headings On The table of Field Names
for i=0 to howmanyfields %>
<% NEXT %>

<% ' Now lets print the records
DO WHILE NOT rstemp.EOF %>

<% FOR i=0 TO howmanyfields
  ' hardcode a space if value blank
  IF ISNULL(thisvalue) THEN
  END IF%>
  <td VALINE=TOP><%=thisvalue %></td>
<% NEXT %>


<% ' close the connections
SET rstemp=nothing


ASP File

The ASP file must include the library file at the start. Note the use of the ActiveConnection property when passing the results to query2table.

<title>Connecting to Oracleusing OLE and DSNLess connection and a Library</title>

<H2>Connecting to an Oracle database using OLE and DSNLess connection</H2>
<!--#include file="_lib\ASPLib.asp"-->

DIM objCommand
SET objCommand=Server.CreateObject("ADODB.Command")
SET objCommand.ActiveConnection=getdBConnection()
response.write("<H3>Department table</H3>")
SQLStr="SELECT * from Dept"
CALL query2table(SQLStr, objCommand.ActiveConnection)

response.write("<H3>Employee table</H3>")
SQLStr="SELECT * from Emp"
CALL query2table(SQLStr, objCommand.ActiveConnection)

' close the database connection


Click here to run the program