• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 381
  • Last Modified:

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?
0
hk308
Asked:
hk308
1 Solution
 
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();
0
 
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now