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.

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.

Cowboy (Gregory A. Beamer) wrote:
I remove this parameter clear code but still get the same error.


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

rockdale.gr***@gmail.com wrote:
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.


<rockdale.gr***@gmail.com> wrote in message
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?


Jeff Dillon wrote:
Show quoteHide quote

I use it all the time

<rockdale.gr***@gmail.com> wrote in message
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?


Jeff Dillon wrote:
Show quoteHide quote

Why choose SQL Express over Access?
Suddenly slow ado.net connections
Moving Data with DataSets/ADO.NET - looking for examples
Easiest Way To Point To New Database, I Created Data Source And Connection to It, Then Dragged Table
ADO to ADO.NET 2.0