Parameters.Refresh

[Jim Heavey]

I was looking for documentation as to what the Parameters.Refresh method
does. I suspect that it is somehow initializing the Parameter oject within
the Command with the number of parameters which are in my stored procedure.

Anyway, when I get to that line of code I get a "-2147217900 (80040e14)" and
that message says, more or less that my SQL statement is invalid. The only
SQL statement I have is what is calling the stored procedure. In the message
box I seem to be getting message back from Oracle indicating that I have the
wrong number of paramters and/or the wrong types. At this juncture, I have
not loaded my parameters.

Here is my code....

Dim cnn As New ADODB.Connection
Dim Rs As ADODB.Recordset
cnn.ConnectionString = "Provider=MSDAORA.1;Password=YYYYYYYY;User
ID=XXXXXX;Data Source=ZZZZZZZZZZ;Persist Security Info=True"
cnn.Open

Dim cmd As New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "OPS_UI_QUERIES.QRY_PROD_Data"
cmd.Parameters.Refresh

cmd.Parameters(1).Name = "i_User_ID"
cmd.Parameters(1).Type = adInteger
cmd.Parameters(1).Value = 1
cmd.Parameters(2).Name = "i_Time_ID"
cmd.Parameters(2).Type = adInteger
cmd.Parameters(2).Value = 136

Set Rs = cmd.Execute

Any ideas as to what is wrong?

Thanks in advance for your assistance.

 

[Bob Phillips]

Jim,

I don't think that is what you want.

I don't have a database on this machine, so I cannot test it, but I think
you want code along these lines

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim parm1 As ADODB.Parameter
Dim parm2 As ADODB.Parameter
Dim rs As ADODB.Recordset

Set cnn = New ADODB.Connection
Set cmd = New ADODB.Command
cnn.ConnectionString = "Provider=MSDAORA.1;" & _
"Password=YYYYYY;" & _
"UserID=XXXXX;" & _
"Data Source=ZZZZZZZ;" & _
"Persist Security Info=True"
cnn.Open cnn.ConnectionString
With cmd
.CommandText = "mySP"
.CommandType = adCmdStoredProc
.ActiveConnection = cnn.ConnectionString
End With

Set parm1 = cmd.CreateParameter( _
Name:="i_User_ID", _
Type:=adInteger, _
Direction:=adParamInput, _
Value:=1)
Set parm2 = cmd.CreateParameter( _
Name:="i_Time_ID", _
Type:=adInteger, _
Direction:=adParamInput, _
Value:=136)

cmd.Parameters.Append (parm1)
cmd.Parameters.Append (parm2)

Set rs = cmd.Execute


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

See if this gives a clue:

http://support.microsoft.com/kb/240205/EN-US/
PRB: ADO Parameters.Refresh Fails with MSDAORA Provider and Parameterized
Query

 

[Jim Heavey]

Thanks Tom for the article. But the article says that the indicates that the
possible reason for the error is that the wrong provided is being used. I am
using the "MSDAORA" identified in the article (actually I was using
"MSDAORA.1", but neither works). So I am still stuck

 

[Jim Heavey]

Bob, I have tried several versions of what you had suggested. Seems
perfectly logical, but it does not work. When the append command is used, I
get an error message about requiring an object.

I have gone and seen KB article from Microsoft which shows the use of
refresh method, and once that is used, the code begins to reference the
parameters, so I am guessing that the Refresh initialized those parameters in
the command object.

But neither work for me.

 

[Tom Ogilvy]

To me it says MSDAORA does not work (I assume MSDAORA.1 does not either)
with this command. Further it says you have use a different provider which
it suggests which one to use. Your only stuck if you continue to use the
OLE DB provider above. Then again, maybe I can't read.

 

[Bob Phillips]

So you are not getting the error that is shown in the article? Do you get
that error if you use Refresh?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Jim Heavey]

I found a form of the Parameters.Append command which does work. That being:
cmd.Parameters.Append cmd.CreateParameter("i_User_ID", adInteger,
adParamInput, 8, 1)

Why this works and the other does not, beats me.

I am guessing that I only need to use the "Refresh" method if I did not want
to create my parms manually.

So now I get through the code which allows me to add my parameters. My
current problem is my last parameter "type" is an Oracle Ref Cursor. I'm not
sure what type this translates to in ADO terms.... So I guess I will try a
few and see if any work.

 

[Tim Williams]

Are you using a ref cursor as an OUT parameter from a procedure, or as the
return value from a function?

I know that if a PLSQL function returns a ref cursor then in VBA you would
set the return value to an ADO recordset...

Example here...
http://www.oracle-base.com/articles/8i/UsingRefCursorsToReturnRecordsets.php

Tim