Avatar of PSERS BIT
PSERS BIT
Flag for United States of America

asked on 

Passing date parameter to oracle stored Procedure worked on vb6 stopped working after upgrade to vb.net 2005

the following code worked on vb6.   I believe the problem is in the conversion of the date.  I have a stored procedure written in oracle that accepts a date.  It is defined as a date.  In vb6 i used the following code to call the procedure and it worked fine.  
con.ConnectionString = "Provider=MSDAORA;Data Source=PPRD;User Id=imaging;Password=imaging;"
con.Open

con.CursorLocation = adUseNone

With cmd
 .ActiveConnection = con
 .CommandText = "DBO.P_PROCESS_DOC_IMAGE_REQUEST"
 .CommandType = adCmdStoredProc

Set param = .CreateParameter("Piv_ExternalImageId", adVarChar, adParamInput, 18)      'object id
.Parameters.Append param
Set param = .CreateParameter("Piv_ExternalImageName", adVarChar, adParamInput, 25) 'object name
.Parameters.Append param
Set param = .CreateParameter("Piv_PrimaryIdentifierType", adChar, adParamInput, 1)
.Parameters.Append param
Set param = .CreateParameter("Piv_PrimaryIdentifier", adVarChar, adParamInput, 9)
.Parameters.Append param
Set param = .CreateParameter("Piv_DocTypeCode", adVarChar, adParamInput, 4)       'doc type
.Parameters.Append param
Set param = .CreateParameter("Piv_WorkflowInstanceId", adVarChar, adParamInput, 10)
.Parameters.Append param
Set param = .CreateParameter("Pid_ScanDate", adDate, adParamInput, 200)           'scan date
.Parameters.Append param
Set param = .CreateParameter("Pio_rtn_code", adVarChar, adParamOutput, 20)
.Parameters.Append param
Set param = .CreateParameter("Pio_rtn_text", adVarChar, adParamOutput, 200)
.Parameters.Append param
End With

the Pid_ScanDate param was filled as follows :
 yyyy = Mid(lCALformfield.Value, 1, 4)
            mm = Mid(lCALformfield.Value, 5, 2)
            dd = Mid(lCALformfield.Value, 7, 2)
       cmd("Pid_ScanDate") = CDate(mm & "/" & dd & "/" & yyyy)

this code works find.
Then i converted to vb.net.   Code to follow

 con.ConnectionString = "Provider= OraOLEDB.Oracle;Data Source=PINT;User Id=imaging;Password=imaging;"
                    con.Open()

                    con.CursorLocation = ADODB.CursorLocationEnum.adUseNone


                    With cmd
                        .let_ActiveConnection(con)
                        .CommandText = "DBO.P_PROCESS_DOC_IMAGE_REQUEST"
                        .CommandType = ADODB.CommandTypeEnum.adCmdStoredProc

                        param = .CreateParameter("Piv_ExternalImageId", ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamInput, 18) 'object id
                        .Parameters.Append(param)
                        param = .CreateParameter("Piv_ExternalImageName", ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamInput, 25) 'object name
                        .Parameters.Append(param)
                        param = .CreateParameter("Piv_PrimaryIdentifierType", ADODB.DataTypeEnum.adChar, ADODB.ParameterDirectionEnum.adParamInput, 1)
                        .Parameters.Append(param)
                        param = .CreateParameter("Piv_PrimaryIdentifier", ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamInput, 9)
                        .Parameters.Append(param)
                        param = .CreateParameter("Piv_DocTypeCode", ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamInput, 4) 'doc type
                        .Parameters.Append(param)
                        param = .CreateParameter("Piv_WorkflowInstanceId", ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamInput, 10)
                        .Parameters.Append(param)
                        param = .CreateParameter("Pid_ScanDate", ADODB.DataTypeEnum.adDate, ADODB.ParameterDirectionEnum.adParamInput, 10) 'scan date
                        .Parameters.Append(param)
                        param = .CreateParameter("Pio_rtn_code", ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamOutput, 20)
                        .Parameters.Append(param)
                        param = .CreateParameter("Pio_rtn_text", ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamOutput, 200)
                        .Parameters.Append(param)
                    End With
scandate param is filled like this.  
cmd.Parameters("Pid_ScanDate").Value = CDate(mm & "/" & dd & "/" & yyyy)

i get the following error
PLS-00306: wrong number or types of arguments in call to 'P_PROCESS_DOC_IMAGE_REQUEST'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Pid_scandate is the 7th param in the stored procedute.

Can anyone help..... I can not change the stored procedure.    

thanks  rb
Oracle Database.NET ProgrammingVisual Basic.NET

Avatar of undefined
Last Comment
PSERS BIT

8/22/2022 - Mon