Thread: Parameters.Refresh

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.

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)

"Jim Heavey" <JimHeavey@discussions.microsoft.com> wrote in message
news:FD772613-CA3F-41F6-BFD4-617970A15BE3@microsoft.com...
> 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.

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


--
Regards,
Tom Ogilvy






"Jim Heavey" <JimHeavey@discussions.microsoft.com> wrote in message
news:FD772613-CA3F-41F6-BFD4-617970A15BE3@microsoft.com...
> 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.

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

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.

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.

--
Regards,
Tom Ogilvy



"Jim Heavey" <JimHeavey@discussions.microsoft.com> wrote in message
news:5FDA35E9-04AE-45BC-8057-700E1A0826EE@microsoft.com...
> 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
>

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" <JimHeavey@discussions.microsoft.com> wrote in message
news:CFD6C821-A1E6-41A4-B896-C576A5FF6D5C@microsoft.com...
> 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.
>
>

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.

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/...Recordsets.php

Tim

--
Tim Williams
Palo Alto, CA


"Jim Heavey" <JimHeavey@discussions.microsoft.com> wrote in message
news:3F2B05DE-131D-4332-A9DD-3B693D3B7B82@microsoft.com...
> 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.
>