SQLHelper STored Procedure Output Parameter

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
 
C

Cowboy \(Gregory A. Beamer\)

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
 
R

rockdale.green

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
 
M

Matt Noonan

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.
 
J

Jeff Dillon

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
 
R

rockdale.green

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?
 
J

Jeff Dillon

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQLHelper Stored Procedure Output Parameter 0
Output param set to DBNull 4
Caching Parameters Performance Issue When Using SQLHELPER 2
Calling a stored procedure with a output parameter 1
Using a Timestamp field to implement optimistic concurrency 2
Dynamically Populating a SqlParameter object 5
Output SqlParameter Returns NULL Value When Procedure Assigns a Va 6
Return parameter probl with DAAB 3

Top