SQLHelper STored Procedure Output Parameter

  • Thread starter Thread starter rockdale.green
  • Start date Start date
R

rockdale.green

Hi, All
How to get the output parameter's value when you use the SQLHelper
(Microsoft Data Access Block)?

When I try to access my ourput parm I got the following error.


System.NullReferenceException: Object reference not set to an instance
of an object.


I thought it is because there is a statement cmd.Parameters.Clear() in
ExecuteNonQuery function, but even I comment this parameter clear
statement, it still caught the same error.


Before ExecuteNonQuery return, The output value is still in the output
parm. but when return to SqlHelper.ExecuteNonQuery(), the output
parameter value got lost while the vlaue of input parameter is still
there.


Did I miss something? Or SQLHelper can not return output parameter?


Help Please


---------------------------------------------------------------------------­------------------

public string TestOutput(int pFlag){
SqlParameter[] parm = new SqlParameter[3];


parm[0] = new SqlParameter("@flag",
SqlDbType.Int,4);
parm[0].Value = pFlag;
parm[1] = new SqlParameter("@result_txt",
SqlDbType.VarChar,32);
parm[1].Direction =
ParameterDirection.InputOutput;
parm[2] = new SqlParameter("@result_txt",
SqlDbType.Int,4);
parm[2].Direction =
ParameterDirection.InputOutput;
// parm[2].Direction = ParameterDirection.Output;


SqlHelper.ExecuteNonQuery(ConnectionString,
"UDSP_OUTPUT_TEST",
parm);


return parm[1].Value.ToString();


}


---------------------------------------------------------------------------­-----------------



CREATE PROCEDURE UDSP_OUTPUT_TEST
@flag int,
@result_txt varchar (32) output,
@result_code int output
AS
begin


set nocount on
if @flag=1
begin
select @result_txt = 'ERROR RETURNED FROM SP'
select @result_code = -1
return
end
else
begin
select @result_txt = 'SUCCEED RETURNED FROM SP'
select @result_code = 0


end


set nocount off


end
 
It seems one of the overloads does not call the Clear on the parameters. If
so, run at that level and not where you are entering the helper. If not, you
can remove that particular line of code and have it NOT clear the params
before passing back.

If you are using stored procs, Microsoft has a new project SPOIL that allows
you to set up methods that have the same signature as your sproc.
Interesting work. Not sure I am completely thrilled, but it is an
interesting direction and gives an option to the normal Data Access block.

The new Enterprise library moves away from the DAB direction, so it is
another option. If the project is due out next year, LINQ is another option.

--
Gregory A. Beamer

*************************************************
Think Outside the Box!
*************************************************
Hi, All
How to get the output parameter's value when you use the SQLHelper
(Microsoft Data Access Block)?

When I try to access my ourput parm I got the following error.


System.NullReferenceException: Object reference not set to an instance
of an object.


I thought it is because there is a statement cmd.Parameters.Clear() in
ExecuteNonQuery function, but even I comment this parameter clear
statement, it still caught the same error.


Before ExecuteNonQuery return, The output value is still in the output
parm. but when return to SqlHelper.ExecuteNonQuery(), the output
parameter value got lost while the vlaue of input parameter is still
there.


Did I miss something? Or SQLHelper can not return output parameter?


Help Please


---------------------------------------------------------------------------­------------------

public string TestOutput(int pFlag){
SqlParameter[] parm = new SqlParameter[3];


parm[0] = new SqlParameter("@flag",
SqlDbType.Int,4);
parm[0].Value = pFlag;
parm[1] = new SqlParameter("@result_txt",
SqlDbType.VarChar,32);
parm[1].Direction =
ParameterDirection.InputOutput;
parm[2] = new SqlParameter("@result_txt",
SqlDbType.Int,4);
parm[2].Direction =
ParameterDirection.InputOutput;
// parm[2].Direction = ParameterDirection.Output;


SqlHelper.ExecuteNonQuery(ConnectionString,
"UDSP_OUTPUT_TEST",
parm);


return parm[1].Value.ToString();


}


---------------------------------------------------------------------------­-----------------



CREATE PROCEDURE UDSP_OUTPUT_TEST
@flag int,
@result_txt varchar (32) output,
@result_code int output
AS
begin


set nocount on
if @flag=1
begin
select @result_txt = 'ERROR RETURNED FROM SP'
select @result_code = -1
return
end
else
begin
select @result_txt = 'SUCCEED RETURNED FROM SP'
select @result_code = 0


end


set nocount off


end
 
Thanks for your reply.
It seems one of the overloads does not call the Clear on the parameters. If
so, run at that level and not where you are entering the helper. If not, you
can remove that particular line of code and have it NOT clear the params
before passing back.
Click to expand...

I remove this parameter clear code but still get the same error.

The new Enterprise library moves away from the DAB direction, so it is
another option.
Click to expand...

I am changing current system which was coded by somebody else, I can
not implement the new Enterprise library 'cause It will affect other
parts.

Thanks
 
I am changing current system which was coded by somebody else, I can
not implement the new Enterprise library 'cause It will affect other
parts.
Click to expand...

That's not entirely true...

The Enterprise Library and the original SqlHelper class are in different
namespaces, and they use completely different approaches, so you *could* add
the EntLib without affecting the other parts of the application.

But that is probably overkill just to solve this one problem.
 
Remember to set NOCOUNT ON. You need to cycle through any results, before
getting output params.


Hi, All
How to get the output parameter's value when you use the SQLHelper
(Microsoft Data Access Block)?

When I try to access my ourput parm I got the following error.


System.NullReferenceException: Object reference not set to an instance
of an object.


I thought it is because there is a statement cmd.Parameters.Clear() in
ExecuteNonQuery function, but even I comment this parameter clear
statement, it still caught the same error.


Before ExecuteNonQuery return, The output value is still in the output
parm. but when return to SqlHelper.ExecuteNonQuery(), the output
parameter value got lost while the vlaue of input parameter is still
there.


Did I miss something? Or SQLHelper can not return output parameter?


Help Please


---------------------------------------------------------------------------­------------------

public string TestOutput(int pFlag){
SqlParameter[] parm = new SqlParameter[3];


parm[0] = new SqlParameter("@flag",
SqlDbType.Int,4);
parm[0].Value = pFlag;
parm[1] = new SqlParameter("@result_txt",
SqlDbType.VarChar,32);
parm[1].Direction =
ParameterDirection.InputOutput;
parm[2] = new SqlParameter("@result_txt",
SqlDbType.Int,4);
parm[2].Direction =
ParameterDirection.InputOutput;
// parm[2].Direction = ParameterDirection.Output;


SqlHelper.ExecuteNonQuery(ConnectionString,
"UDSP_OUTPUT_TEST",
parm);


return parm[1].Value.ToString();


}


---------------------------------------------------------------------------­-----------------



CREATE PROCEDURE UDSP_OUTPUT_TEST
@flag int,
@result_txt varchar (32) output,
@result_code int output
AS
begin


set nocount on
if @flag=1
begin
select @result_txt = 'ERROR RETURNED FROM SP'
select @result_code = -1
return
end
else
begin
select @result_txt = 'SUCCEED RETURNED FROM SP'
select @result_code = 0


end


set nocount off


end
 
I set NOCOUNT ON within my stored procedure (see my source code) if
that is what you mean.

Guess nobody used SQLHelper to retrieve stored procedure's output
parameter before?
 
I use it all the time

I set NOCOUNT ON within my stored procedure (see my source code) if
that is what you mean.

Guess nobody used SQLHelper to retrieve stored procedure's output
parameter before?
 
Back
Top