Trouble with SP output parameter 
Author Message
 Trouble with SP output parameter

Hi there.  I'm trying to run a stored procedure on an oracle DB, but I'm
having some problems with the output parameter.  The SP has 2 input and 1
output parameter.  If I drop the output parameter from the SP it executes
fine.  This is my code:

oleDbCommand = New System.Data.OleDb.OleDbCommand()
oleDbCommand.Connection = oleDbConnection
oleDbCommand.CommandText = "DBName.SPName"
oleDbCommand.CommandType = CommandType.StoredProcedure

oleDbCommand.Parameters.Add(New System.Data.OleDb.OleDbParameter("pClient",
System.Data.OleDb.OleDbType.VarChar)).Direction = ParameterDirection.Input
oleDbCommand.Parameters.Add(New System.Data.OleDb.OleDbParameter("pProject",
System.Data.OleDb.OleDbType.VarChar)).Direction = ParameterDirection.Input
oleDbCommand.Parameters.Add(New
System.Data.OleDb.OleDbParameter("pRecord_Status",
System.Data.OleDb.OleDbType.VarChar)).Direction = ParameterDirection.Output

' fill out parameter values
oleDbCommand.Parameters("pClient").Value = "61"
oleDbCommand.Parameters("pProject").Value = "TEST"

oleDbCommand.ExecuteNonQuery()

The SP has been tested, and it works.  Here is the code for it:

CREATE OR REPLACE PROCEDURE DBName.SPName
             (pClient in TMP_TESTOPPDAT.CLIENT%TYPE
             ,pProject in TMP_TESTOPPDAT.PROJECT%TYPE
             ,pRecord_Status out varchar2)
is

begin
insert into TMP_TESTOPPDAT(CLIENT, PROJECT)
       values (pClient, pProject);
commit;
pRecord_Status := 'OK';
end OPPDAT_PROSJEKT;
/

This is the error message i'm geitting:

System.InvalidOperationException: Parameter 2: 'pRecord_Status' of type:
String, the property Size has an invalid size: 0
   at System.Data.OleDb.OleDbParameter.ValidateParameter()
   at System.Data.OleDb.OleDbParameter.BindParameter(Int32 i, DBBindings
bindings, tagDBPARAMBINDINFO[] bindInfo)
   at System.Data.OleDb.OleDbCommand.CreateAccessor()
   at System.Data.OleDb.OleDbCommand.InitializeCommand(CommandBehavior
behavior, Boolean throwifnotsupported)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior
behavior, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()

Thanks a lot!
Shawn



Sat, 15 Oct 2005 18:43:36 GMT  
 Trouble with SP output parameter
try to set the size using a complete declaration parameter like:
oleDbCommand.Parameters.Add(New
System.Data.OleDb.OracleParameter("pRecord_Status",
System.Data.OracleClient.OracleType.VarChar, 100,
System.Data.ParameterDirection.Output, False, CType(15, Byte), CType(32,
Byte), "pRecord_Status", DataRowVersion.Current, Nothing))



Quote:
> Hi there.  I'm trying to run a stored procedure on an oracle DB, but I'm
> having some problems with the output parameter.  The SP has 2 input and 1
> output parameter.  If I drop the output parameter from the SP it executes
> fine.  This is my code:

> oleDbCommand = New System.Data.OleDb.OleDbCommand()
> oleDbCommand.Connection = oleDbConnection
> oleDbCommand.CommandText = "DBName.SPName"
> oleDbCommand.CommandType = CommandType.StoredProcedure

> oleDbCommand.Parameters.Add(New

System.Data.OleDb.OleDbParameter("pClient",
Quote:
> System.Data.OleDb.OleDbType.VarChar)).Direction = ParameterDirection.Input
> oleDbCommand.Parameters.Add(New

System.Data.OleDb.OleDbParameter("pProject",
Quote:
> System.Data.OleDb.OleDbType.VarChar)).Direction = ParameterDirection.Input
> oleDbCommand.Parameters.Add(New
> System.Data.OleDb.OleDbParameter("pRecord_Status",
> System.Data.OleDb.OleDbType.VarChar)).Direction =

ParameterDirection.Output
Quote:

> ' fill out parameter values
> oleDbCommand.Parameters("pClient").Value = "61"
> oleDbCommand.Parameters("pProject").Value = "TEST"

> oleDbCommand.ExecuteNonQuery()

> The SP has been tested, and it works.  Here is the code for it:

> CREATE OR REPLACE PROCEDURE DBName.SPName
>              (pClient in TMP_TESTOPPDAT.CLIENT%TYPE
>              ,pProject in TMP_TESTOPPDAT.PROJECT%TYPE
>              ,pRecord_Status out varchar2)
> is

> begin
> insert into TMP_TESTOPPDAT(CLIENT, PROJECT)
>        values (pClient, pProject);
> commit;
> pRecord_Status := 'OK';
> end OPPDAT_PROSJEKT;
> /

> This is the error message i'm geitting:

> System.InvalidOperationException: Parameter 2: 'pRecord_Status' of type:
> String, the property Size has an invalid size: 0
>    at System.Data.OleDb.OleDbParameter.ValidateParameter()
>    at System.Data.OleDb.OleDbParameter.BindParameter(Int32 i, DBBindings
> bindings, tagDBPARAMBINDINFO[] bindInfo)
>    at System.Data.OleDb.OleDbCommand.CreateAccessor()
>    at System.Data.OleDb.OleDbCommand.InitializeCommand(CommandBehavior
> behavior, Boolean throwifnotsupported)
>    at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior
> behavior, Object& executeResult)
>    at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
> behavior, String method)
>    at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()

> Thanks a lot!
> Shawn



Sat, 15 Oct 2005 21:12:43 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. can't get output parameter from SP using ADO

2. HELP: output parameter in a SP ( SQL 2000, ADO 2.6)

3. Q:Execute on SP with Output parameters

4. Not getting Output parameter From SP

5. Output from SQL SP

6. Q: DAO 3.5 w/ODBCDirect call Oracle SP w/Output Params

7. output (sp)

8. pass parameter to SQL SP from VB and return recordset

9. I can't call Oracle SP from VB5 with over 10 parameters

10. Passing parameters to IF-Statement SP

11. Active X control for collecting SP parameters

12. SP Parameters

 

 
Powered by phpBB® Forum Software