[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2006-03-23
2
Medium Priority
?
377 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?
0
Comment
Question by:hk308
2 Comments
 
LVL 1

Accepted Solution

by:
rakshithks earned 500 total points
ID: 16277636
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
 
LVL 8

Expert Comment

by:nepostojeci_email
ID: 16278211
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
If you are a mobile app developer and especially develop hybrid mobile apps then these 4 mistakes you must avoid for hybrid app development to be the more genuine app developer.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Six Sigma Control Plans

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question