Option Explicit
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim param As New ADODB.Parameter
Dim strSQL As String
Dim i As Integer
Dim StrTmp As String
Dim errLoop As Error
Dim strError As String
Dim Errs1 As Object
Private Sub cmdCnOracleProvider_Click()
If cn.State = 1 Then
cn.Close
Set cn = Nothing
End If
cn.CursorLocation = adUseClient
cn.Open "Provider=MSDAORA.1;Password=tiger;User ID=scott;Data Source=dseOracle8i;Persist Security Info=True"
If cn.State = 1 Then
MsgBox "OleDb Orcl Connection is Open"
End If
End Sub
Private Sub cmdCnODBC_Click()
If cn.State = 1 Then
cn.Close
Set cn = Nothing
End If
cn.ConnectionString = "dsn=kilburn;uid=scott;pwd=tiger"
cn.Open
If cn.State = 1 Then
MsgBox "ODBC Connection is Open"
End If
End Sub
Private Sub cmd49Params_Click()
'With ssn16 as 49th parameter below, runs without errors:
strSQL = "{call pk_storedProcedure.sp_storedProcedure (?, {resultset 4, ssn, fname, lname," & _
"ssn1, fname1, lname1," & _
"ssn2, fname2, lname2," & _
"ssn3, fname3, lname3," & _
"ssn4, fname4, lname4," & _
"ssn5, fname5, lname5," & _
"ssn6, fname6, lname6," & _
"ssn7, fname7, lname7," & _
"ssn8, fname8, lname8," & _
"ssn9, fname9, lname9," & _
"ssn10, fname10, lname10," & _
"ssn11, fname11, lname11," & _
"ssn12, fname12, lname12," & _
"ssn13, fname13, lname13," & _
"ssn14, fname14, lname14," & _
"ssn15, fname15, lname15," & _
"ssn16} ) }"
With cmd
Set .ActiveConnection = cn
.CommandText = strSQL
.CommandType = adCmdText
Set param = .CreateParameter("sIn", adBSTR, adParamInput, 50, "Sam")
.Parameters.Append param
End With
Set rs = cmd.Execute
Debug.Print rs(1).Value
Debug.Print rs(0).Value
MsgBox "Done 49"
End Sub
Private Sub cmd50Params_Click()
On Error GoTo MyError
'With 50 Output Parameters below, the
'Run-Time Error '-2147467259(80004005)': Unspecified Error
'occurs - See Text Box for more detailed info
strSQL = "{call pk_storedProcedure.sp_storedProcedure (?, {resultset 4, ssn, fname, lname," & _
"ssn1, fname1, lname1," & _
"ssn2, fname2, lname2," & _
"ssn3, fname3, lname3," & _
"ssn4, fname4, lname4," & _
"ssn5, fname5, lname5," & _
"ssn6, fname6, lname6," & _
"ssn7, fname7, lname7," & _
"ssn8, fname8, lname8," & _
"ssn9, fname9, lname9," & _
"ssn10, fname10, lname10," & _
"ssn11, fname11, lname11," & _
"ssn12, fname12, lname12," & _
"ssn13, fname13, lname13," & _
"ssn14, fname14, lname14," & _
"ssn15, fname15, lname15," & _
"ssn16, fname16} ) }"
With cmd
Set .ActiveConnection = cn
.CommandText = strSQL
.CommandType = adCmdText
Set param = .CreateParameter("sIn", adBSTR, adParamInput, 50, "Sam")
.Parameters.Append param
End With
Set rs = cmd.Execute
Debug.Print rs(1).Value
Debug.Print rs(0).Value
MsgBox "Done 50"
Exit Sub
MyError:
i = 1
StrTmp = StrTmp & vbCrLf & "VB Error # " & vbCrLf & Str(Err.Number) & vbCrLf
StrTmp = StrTmp & vbCrLf & " Generated by " & vbCrLf & Err.Source & vbCrLf
StrTmp = StrTmp & vbCrLf & " Description " & vbCrLf & Err.Description
' Display Error Information for each Error object.
Set Errs1 = cn.Errors
For Each errLoop In Errs1
With errLoop
StrTmp = StrTmp & vbCrLf & "Error #" & i & ":"
StrTmp = StrTmp & vbCrLf & " ADO Error #" & .Number
StrTmp = StrTmp & vbCrLf & " Description " & .Description
StrTmp = StrTmp & vbCrLf & " Source " & .Source
i = i + 1
End With
Next
txtErrorInfo.Text = StrTmp
End Sub