problem with the Stored Procedure..

Question

User-1623675128 posted
In my C# code i am calling my Stored Proc using 3 params(2 are input type & one is  out param)
the two input params are int32 type  and out param is String type in C#.


In My Stored Proc recieving three params as (SQLSERVER)number(10),number(10),varchar
(10).

this stroed procedure returns one varchar value version number  like ("1.0" or "3.1")

when i am executing this SP in SQLSERVER its returns value correctly("1.0" or "3.1").
But the Same SP returns always null value  to the C# code.Really i am amazing....

can tell me the problem where  it is actually..

Answer 1

User1456366495 posted
How do you catch the output value in your C# code?

Answer 2

User-1623675128 posted

in C#  code declare on string Array with length 2.getting the Values from the SP into this Array ..
Can you see the Following Code...

try

{

string []versionNo = new string[2];
char[]splitChar = new char[1];
splitChar[0]='.';
IDataParameter[] paramArray = {
dh.CreateParameter("IN_intEntityTypeCode", DbType.Int32),

dh.CreateParameter("IN_intEntityUniqueId", DbType.Int32),
dh.CreateParameter("OUT_strVersion", DbType.String,,ParameterDirection.Output)
};

paramArray[0].Value = (int)entityType;
paramArray[1].Value = entityUniqueId;
paramArray[2].Value = string.Empty ;

dh.ExecuteNonQuery(CommandType.StoredProcedure,"cfg_inventory_max_ver_sel",paramArray);
versionNo = paramArray[2].Value.ToString().Split(splitChar);
if(versionNo.Length == 1)
{
paramArray[2].Value = paramArray[2].Value.ToString()+".0";
}

this is the code.After Execution of ExecuteNonQuery i seen that ParamArray Values through QuickWatch paramArray[2].Value  always null string.But SP return in SQLSERVER some version number.

Answer 3

User2131024838 posted

In my C# code i am calling my Stored Proc using 3 params(2 are input type & one is  out param)
the two input params are int32 type  and out param is String type in C#.


In My Stored Proc recieving three params as (SQLSERVER)number(10),number(10),varchar
(10).

this stroed procedure returns one varchar value version number  like ("1.0" or "3.1")

when i am executing this SP in SQLSERVER its returns value correctly("1.0" or "3.1").
But the Same SP returns always null value  to the C# code.Really i am amazing....

can tell me the problem where  it is actually..

mind posting your SP code?

Answer 4

User-1623675128 posted

CREATE PROCEDURE DBO.cfg_inventory_max_ver_sel(
    @IN_intEntityTypeCode VARCHAR(10),
    @IN_intEntityUniqueID INT,
    @OUT_strVersion  VARCHAR (10) = NULL OUTPUT
)

WITH ENCRYPTION
AS
BEGIN

   SELECT @OUT_strVersion  = CONVERT(VARCHAR,E.MAJORVERSION)+ '.'+ CONVERT(VARCHAR,E.MINORVERSION)
  FROM CFG_ENTITYINVENTORY E, CFG_MAX_VERSION_ENTITIES M
  WHERE
    E.ENTITYTYPE_CODE = M.ENTITYTYPE_CODE
    AND E.ENTITYUNIQUEID = M.ENTITYUNIQUEID
    AND ( (E.MAJORVERSION = M.MAJORVERSION) OR ( E.MAJORVERSION IS NULL AND M.MAJORVERSION IS NULL) )
    AND (( E.MINORVERSION = M.MINORVERSION) OR  (E.MINORVERSION IS NULL AND M.MINORVERSION IS NULL) )
    AND E.ENTITYTYPE_CODE = @IN_intEntityTypeCode
     AND E.ENTITYUNIQUEID = @IN_intEntityUniqueID;
END


this is the SP code what i hav...

Answer 5

User2131024838 posted
change this dh.CreateParameter("OUT_strVersion", DbType.String,,ParameterDirection.Output) to dh.CreateParameter("OUT_strVersion", DbType.String)

and change this paramArray[2].Value = string.Empty; to paramArray[2].Direction = ParameterDirection.Ouput;

Answer 6

User-1623675128 posted

Hi cao,  Then also it doesnt return a version

That output variable not updated it consists only null value.

one more thing i need to add here when i written a function instead of the procedure then its fine returning versio number correctly ....its failing in the czase of procddure why pls..

 

where the wrong is going on

thanks in advance..

rams 123