We help IT Professionals succeed at work.

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

hk308
hk308 asked
on
Medium Priority
394 Views
Last Modified: 2010-04-17
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?
Comment
Watch Question

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();

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.