Citation:
Subject: Using .Parameters.Refresh method fails with runtime error -2147217839 (80040e51)
Doc ID: Note:267370.1
Type: PROBLEM
Last Revision
Date: 03-NOV-2006
Status: PUBLISHED
Problem Description
-------------------
With the Oracle OLEDB provider 9.2.0.2.0 or higher,
Using Visual Basic, you would like to return a list of the parameters for a
stored procedure.
Here, under a sample code :
Private Sub Command1_Click()
On Error GoTo err:
Dim ConnString As String
Dim Conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim Params As ADODB.Parameters
Dim Param As ADODB.Parameter
sConnString = "Provider=OraOLEDB.Oracle;User ID=username;Password=password;Data Source=Data_Source;PLSQLRSet=1;"
Set Conn = New ADODB.Connection
Set cmd = New ADODB.Command
Conn.open sConnString
cmd.ActiveConnection = Conn
cmd.CommandText = "pkg.proc"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
Set Params = cmd.Parameters
For Each Param In Params
Text1.Text = Text1.Text & Param.Name & vbCrLf
Next
err:
If Err.Number <> 0 Then
Text1.Text = "err number: " & Err.Number & " : " & Err.Source & " : " &
Err.Description
End If
End Sub
You get the error number: -2147217839(80040e51) : OraOLEDB : Provider cannot derive parameter
information and SetParameterInfo has not been called.
Solution Description
--------------------
The error is due to the .Parameters.Refresh method.
This is currently a limitation of our driver.
Explanation
-----------
This bug relates to ADO .Refresh property which should be used only
at the application design time and not in the production application.
The use of .Refresh property causes one or more round trips to the
database per call.
Using this fonctionality in production applications can cause
serious performance bottlenecks.
Based on these feedbacks, we had decided not to support this property.
References
----------
Bug 2156923
Partager