<%
' Global variables to set up connection
Dim Conn 'As ADODB.Connection
Dim AllCmd 'As ADODB.Command
Dim OneCmd 'As ADODB.Command
Dim RecSet 'As ADODB.Recordset
Private Sub PageLoad()
Dim ConnStr 'As String
Dim SQLStr 'As String
ConnStr = "UID=scott;PWD=tiger;DSN=mszeal;"
' Make connection
Set Conn = Server.CreateObject("ADODB.Connection")
With Conn
.ConnectionString = ConnStr
' .CursorLocation = adUseClient
.CursorLocation = 3
.Open
End With
' Get cursor from Stored proc containing all employees
' the number in resultset xx must be higher or equal to rows
' in cursor
SQLStr = "{call getrecordset.getallemp({resultset 20, " _
& "empno, ename, job})}"
Set AllCmd = Server.CreateObject("ADODB.Command")
With AllCmd
Set .ActiveConnection = Conn
.CommandText = SQLStr
' .CommandType = adCmdText
.CommandType = 1
End With
' Sets up to get one emp from cursor with specific empno
SQLStr = "{call getrecordset.getoneemp(?,{resultset 2, " _
& " empno, ename, job})}"
Set OneCmd = Server.CreateObject("ADODB.Command")
With OneCmd
Set .ActiveConnection = Conn
.CommandText = SQLStr
' .CommandType = adCmdText
.CommandType = 1
' .Parameters.Append .CreateParameter(, adInteger, adParamInput)
.Parameters.Append .CreateParameter(, 3, &H0001)
End With
' Set up recordset
Set RecSet = Server.CreateObject("ADODB.Recordset")
With RecSet
' .CursorType = adOpenStatic
' .LockType = adLockReadOnly
.CursorType = 3
.LockType = 1
End With
End Sub
Private Sub GetAllEmp()
' Display each row of record set in Message box
Set RecSet.Source = AllCmd
RecSet.Open
While Not RecSet.EOF
response.write "Employee: " & RecSet(0) & ", " & RecSet(1) & ", " & RecSet(2)
response.write "
"
RecSet.MoveNext
Wend
RecSet.Close
End Sub
Private Sub GetOneEmp()
Dim inputssn 'As Long
Set RecSet.Source = OneCmd
'Get Employee detail for employee Number 7902
inputssn = 7902
OneCmd(0) = inputssn
RecSet.Open
response.write "Employee: " & RecSet(0) & ", " & RecSet(1) & ", " & RecSet(2)
RecSet.Close
End Sub
Private Sub PageUnload()
' Clean up
Conn.Close
Set Conn = Nothing
Set AllCmd = Nothing
Set OneCmd = Nothing
Set RecSet = Nothing
End Sub
%> <%
'*******************************
' Start Main Part
'*******************************
PageLoad
%>
MS ODBC for ORACLE .
Get All employee Data via PL TABLE type
<% GetAllEmp %>
Get only one employee Data via PL TABLE type
<% GetOneEmp %>
<% PageUnload
'*******************************
' End Main Part
'*******************************
%>