Microsoft KB Archive/308621

Knowledge Base

Output parameters are not returned when you run an ADO.NET command in Visual C#

Article ID: 308621

Article Last Modified on 12/11/2006

---

APPLIES TO

• Microsoft ADO.NET 2.0
• Microsoft ADO.NET 1.0
• Microsoft Visual C# 2005
• Microsoft Visual C# .NET 2003 Standard Edition
• Microsoft Visual C# .NET 2002 Standard Edition

---

This article was previously published under Q308621

For a Microsoft Visual Basic .NET version of this article, see 308051.

For a Microsoft Visual C++ .NET version of this article, see 308624.

This article refers to the following Microsoft .NET Framework Class Library namespaces:

• System.Data.SqlClient
• System.Data.OleDb

SYMPTOMS

Output parameters do not appear to be initialized or return a wrong value when you run an ADO.NET command.

CAUSE

This problem can occur for two reasons:

• Output parameters are returned at the end of the data stream when you use a DataReader object.
• The Direction property of the parameter is set not set properly.

RESOLUTION

To resolve this problem, use one of the following methods:

• When you use a DataReader object, you must close it or read to the end of the data before you can view the output parameters.
• Make sure that the direction of the parameter is set to Output or InputOutput (if the parameter is used in the procedure to both send and receive data).

For more information about how to implement these solutions, see the "More Information" section.

NOTE: The parameter object for the return value must be the first item in the Parameters collection. In addition, make sure that the parameter's data type matches the expected return value.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce the Behavior

1. Run the following query in SQL Server Query Analyzer to create a stored procedure named "myProc" in the Pubs database:

   CREATE proc MyProc
   @out smallint OUTPUT
   AS
   Select * from Titles
   Select @out = count(*) from titles
   GO
                       

2. Start Visual Studio .NET.
3. Create a new Windows Application in Visual C# .NET. Form1 is created by default.
4. Make sure that your project contains a reference to the System.Data namespace, and add a reference to this namespace if it does not.
5. Place two Command buttons on Form1. Change the Name property of the first button to btnDirection, and change the Text property to Direction. Change the Name property of the second button to btnReader, and change the Text property to Reader.

6. Use the using statement on the System and System.Data namespaces so that you are not required to qualify declarations in those namespaces later in your code.

   using System;
   using System.Data;
   using System.Data.SqlClient;
   using System.Data.OleDb;
                       

7. Return to Form view, and double-click Direction to add the click event handler. Add the following code to the handler.
   
   Note You must change User ID =<UID> and password = to the correct values before you run this code. Make sure that <UID> has the appropriate permissions to perform this operation on the database.

       String myConnString  = 
           "User ID=<UID>;password=<strong password>;Initial Catalog=pubs;Data Source=(local)";
       SqlConnection myConnection = new SqlConnection(myConnString);
       SqlCommand myCommand = new SqlCommand();
   
       myCommand.CommandType = CommandType.StoredProcedure;
       myCommand.Connection = myConnection;
       myCommand.CommandText = "MyProc";
       myCommand.Parameters.Add("@out", OleDbType.Integer);
       //Uncomment this line to return the proper output value.
       //myCommand.Parameters["@out"].Direction = ParameterDirection.Output;
       try
       {
           myConnection.Open();
           myCommand.ExecuteNonQuery();
           MessageBox.Show("Return Value : " + myCommand.Parameters["@out"].Value);
       }
       catch (Exception ex)
       {
           MessageBox.Show(ex.ToString());
       }
       finally
       {
           myConnection.Close();
       }
                       

8. Return to Form view, and double-click Reader to add the click event handler. Add the following code to the handler.
   
   Note You must change User ID =<UID> and password = to the correct values before you run this code. Make sure that <UID> has the appropriate permissions to perform this operation on the database.

       String myConnString  = 
           "User ID=<UID>;password=<strong password>;Initial Catalog=pubs;Data Source=(local)";
       SqlConnection myConnection = new SqlConnection(myConnString);
       SqlCommand myCommand = new SqlCommand();
       SqlDataReader myReader;
   
       myCommand.CommandType = CommandType.StoredProcedure;
       myCommand.Connection = myConnection;
       myCommand.CommandText = "MyProc";
       myCommand.Parameters.Add("@out", OleDbType.Integer);
       myCommand.Parameters["@out"].Direction = ParameterDirection.Output;
       try
       {
           myConnection.Open();
   
           myReader = myCommand.ExecuteReader();
   
           //Uncomment this line to return the proper output value.
           //myReader.Close();
           MessageBox.Show("Return Value : " + myCommand.Parameters["@out"].Value);
       }
       catch (Exception ex)
       {
           MessageBox.Show(ex.ToString());
       }
       finally
       {
           myConnection.Close();
       } 
                       

9. Modify the connection string (myConnString) in each of the preceding code samples as appropriate for your environment.
10. Save your project.
11. On the Debug menu, click Start to run your project.
12. Click Direction. Notice that the wrong value is returned for the output parameter.
13. To resolve this problem, uncomment the line of code that sets the Direction property for the output parameter. Run the project, and then click Direction. Notice that the output parameter is returned correctly.
14. Click Reader. Notice that the wrong value is returned for the output parameter.
15. To resolve this problem, uncomment the line of code that closes the Reader object. Run the project, and then click Reader. Notice that output parameter is returned correctly.

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

308049 HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic .NET

Additional query words: empty zero blank available parameter

Keywords: kbtshoot kbnofix kbprb kbsqlclient kbstoredproc kbsystemdata KB308621

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.