[RESOLVED] Error when to call Stored procedure

[mutley]

Hi

I trying to call a procedure passing only parameter, but return me error when stat.
The parameter is value of DTpickcer in format AAAAMM . plese see:

     Set cmd = New ADODB.Command
      nAno_mes = Format(dtpAnoMes.Value, "yyyymm")
      
      Screen.MousePointer = vbHourglass
      With cmd
             .ActiveConnection = cn
             .CommandText = "PROCEDURE_ONE"
             .CommandType = adCmdStoredProc
            .Parameters.Append .CreateParameter("@P_ANO_MES", adNumeric, adParamInput, nAno_mes)
            .Execute
      End With

mY Procedure in oracle begin as:

create or replace procedure PROCEDURE_ONE ( P_ANO_MES in number ) is

Error in exception VB

 ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'PROCEDURE_ONE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

The procedure work fine when executed only, What is wrong in my code ?

Tia

[techgnome]

well, the thing that stands out the most is that your sproc isn't called _PROCEDURE_ONE... but P6MPR_CARGA_S901_PREVISAO - I'm hoping that's just a typo...

The other thing that stood out to me was your parameters to the CreateParameter seems to be short one....

http://msdn.microsoft.com/en-us/libr...8VS.85%29.aspx

The parameter following the direction is the SIZE... THEN the value...

-tg

[mutley]

Sorry , The name of procedure is PROCEDURE_ONE , but in the code VB and Oracle are equal , but not worked

but

    with cmd     
    .Parameters.Append .CreateParameter("@P_ANO_MES", adNumeric, adParamInput, nAno_mes)

    end with

Is other mode to create and append parameter

[techgnome]

Did you look at the link? The value that follows the direction is THE SIZE PARAMETER... you're passing in your value as the SIZE... not as the VALUE...
I'll line them up for you:

command.CreateParameter (Name,                Type,           Direction,         Size,            Value)
               .CreateParameter("@P_ANO_MES", adNumeric, adParamInput, nAno_mes)  'See? No value here....

Yes, I understand the documentation says that the parameters are Optional, but you can't skip a parameter in the middle... how does VB know that nAno_mes is the value? It doesn't. It's a number, so it assumes, "hey, must be the size of the parameter, cool" and uses it.

Try this:

.Parameters.Append .CreateParameter("@P_ANO_MES", adNumeric, adParamInput)
.Parameters("@P_ANO_MES").Value = nAno_mes)

-tg

[mutley]

Sorry, Sorry, 10000 Sorry