Microsoft KB Archive/308621

= 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 &quot;More Information&quot; 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.



Steps to Reproduce the Behavior
  Run the following query in SQL Server Query Analyzer to create a stored procedure named &quot;myProc&quot; in the Pubs database: CREATE proc MyProc @out smallint OUTPUT AS Select * from Titles Select @out = count(*) from titles GO  Start Visual Studio .NET. Create a new Windows Application in Visual C# .NET. Form1 is created by default. Make sure that your project contains a reference to the System.Data namespace, and add a reference to this namespace if it does not. 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.</li>  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; </li>  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 = &quot;User ID=<UID>;password= ;Initial Catalog=pubs;Data Source=(local)&quot;; SqlConnection myConnection = new SqlConnection(myConnString); SqlCommand myCommand = new SqlCommand;

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

myCommand.CommandType = CommandType.StoredProcedure; myCommand.Connection = myConnection; myCommand.CommandText = &quot;MyProc&quot;; myCommand.Parameters.Add(&quot;@out&quot;, OleDbType.Integer); myCommand.Parameters[&quot;@out&quot;].Direction = ParameterDirection.Output; try {       myConnection.Open;

myReader = myCommand.ExecuteReader;

//Uncomment this line to return the proper output value. //myReader.Close; MessageBox.Show(&quot;Return Value : &quot; + myCommand.Parameters[&quot;@out&quot;].Value); }   catch (Exception ex) {       MessageBox.Show(ex.ToString); }   finally {       myConnection.Close; }                    </li> Modify the connection string (myConnString) in each of the preceding code samples as appropriate for your environment.</li> Save your project.</li> On the Debug menu, click Start to run your project.</li> Click Direction. Notice that the wrong value is returned for the output parameter.</li> 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.</li> Click Reader. Notice that the wrong value is returned for the output parameter.</li> 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.</li></ol>

<div class="references_section">