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

• 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.

Back to the top

• 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).

Back to the top

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 =<strong 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 =<strong 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.

Back to the top