How to retrieve output parms from a stored procedure in a C# Windows application.

I have a SQL Server 2000 stored procedue with the following header:

      @BatchId          varchar(10),
      @EnablePrint      varchar(1)  OUTPUT,
      @EnableRePrint    varchar(1)  OUTPUT

The  proc is a bit involved, but works correctly when I execute it in Query Analyzer: Sample output

Running [dbo].[usp_EnableVendorWarrantButtons] ( @BatchId = DEBBIE, @EnablePrint = "", @EnableRePrint = "" ).

No rows affected.
(0 row(s) returned)
@EnablePrint = Y
@EnableRePrint = N
@RETURN_VALUE = 0
Finished running [dbo].[usp_EnableVendorWarrantButtons].
****
I want to retrieve the output parms in a C# method:

        private void EnablePrintButtons()
        {
            SqlConnection financeConnect = new SqlConnection(BuildConnectionString());
            SqlCommand financeCmd = new SqlCommand("usp_EnableVendorWarrantButtons", financeConnect);
            financeCmd.CommandType = CommandType.StoredProcedure;
            // input parameter
            SqlParameter batchId = financeCmd.Parameters.Add("@BatchId", SqlDbType.Char, 10);
            batchId.Value = txtBatchId.Text;
            // output parameters
            SqlParameter enablePrintBtn = financeCmd.Parameters.AddWithValue("@EnablePrint", "");
            enablePrintBtn.Direction = ParameterDirection.InputOutput;
            SqlParameter enableRePrintBtn = financeCmd.Parameters.AddWithValue("@EnableRePrint", "");
            enableRePrintBtn.Direction = ParameterDirection.InputOutput;

            financeConnect.Open();

            SqlDataReader printBtnReader = financeCmd.ExecuteReader();

            string enablePrint = financeCmd.Parameters["@EnablePrint"].Value.ToString();
            string enableRePrint = financeCmd.Parameters["@EnableRePrint"].Value.ToString();

            MessageBox.Show("Print " + enablePrint + "\n"
                            + "RePrint " + enableRePrint);

            printBtnReader.Close();
            financeConnect.Close();
        }
***
I need to retrieve the values of  @EnablePrint and @EnableRePrint.
The EnablePrintButtons()  method executes just fine, but the C# variables enablePrint and enableRePrint are empty strings when I display them in the MessageBox. What am I missing?
hk308Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rakshithksCommented:
try using this i have changed your code a little am using add method instead of AddWithValue(..)
----------------------------------------------------------------------
      SqlConnection financeConnect  = new SqlConnection(BuildConnectionString());
                        SqlCommand financeCmd = new SqlCommand("usp_EnableVendorWarrantButtons", financeConnect);
                        financeCmd.CommandType = CommandType.StoredProcedure;
                        // input parameter
                        SqlParameter batchId = financeCmd.Parameters.Add("@BatchId", SqlDbType.VarChar, 10);
                        batchId.Value = txtBatchId.Text;
                        // output parameters
                        SqlParameter enablePrintBtn = financeCmd.Parameters.Add("@EnablePrint",SqlDbType.VarChar,1);
                        enablePrintBtn.Direction = ParameterDirection.Output;
                        SqlParameter enableRePrintBtn = financeCmd.Parameters.Add("@EnableRePrint",SqlDbType.VarChar,1);
                        enableRePrintBtn.Direction = ParameterDirection.Output;

                        
                        financeConnect.Open();

                        SqlDataReader printBtnReader = financeCmd.ExecuteReader();

                        string enablePrint = financeCmd.Parameters["@EnablePrint"].Value.ToString();
                        string enableRePrint = financeCmd.Parameters["@EnableRePrint"].Value.ToString();

                        MessageBox.Show("Print " + enablePrint + "\n"
                              + "RePrint " + enableRePrint);

                        printBtnReader.Close();
                        financeConnect.Close();

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nepostojeci_emailCommented:
change all lines that look like:
  <variable>.Direction = ParameterDirection.InputOutput;
into
  <variable>.Direction = ParameterDirection.Output;


that's the catch ;)

Also, it is _highly_ recommended to check for NULL values in the
output parameters, in the very body of the stored procedure,
cause .NET cannot handle the NULL result values as output params.

I suggest you make all output params non-null or create a default
value for any NULL param.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.