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

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

Back to the top

Steps to reproduce the problem

1. Create a stored procedure named "MyProc" in the Pubs database by running the following query in SQL Server Query Analyzer:

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

   The stored procedure (MyProc) returns one output parameter ("@out").
2. Start Microsoft Visual Studio .NET.
3. Create a new Managed C++ project. Name the project "outParam."
4. Copy and paste the following code in the outParam.cpp file, overwriting the existing code generated by Visual Studio .NET:

   #include "stdafx.h"
   
   #using <mscorlib.dll>
   #using <System.dll>
   #using <System.Data.dll>
   
   using namespace System;
   using namespace System::Data;
   using namespace System::Data::SqlClient;
   
   int direction(void);
   int reader (void);
   
   // This is the entry point for this application
   #ifdef _UNICODE
   int wmain(void)
   #else
   int main(void)
   #endif
   {
   	Console::WriteLine("Running the parameter direction method 'direction()' to check the return parameter value");
   	direction();
   	
   	Console::WriteLine("\nRunning the data reader method 'reader()' to check the return parameter value");
   	reader();
   
   	return 0;   
   }
   
   
   int direction(void)
   {
           String *myConnString = "Data Source=sql_server_name;User ID=your_user_id;password=your_password;Initial Catalog=pubs;";
           SqlConnection *myConnection = new SqlConnection(myConnString);
           SqlCommand *myCommand = new SqlCommand();
   
   		myCommand->CommandType = CommandType::StoredProcedure;
           myCommand->Connection = myConnection;
           myCommand->CommandText = "MyProc";
           myCommand->Parameters->Add("@out", SqlDbType::Int);
           
   
           SqlParameter *myParam = myCommand->Parameters->Item["@out"];
   	//Uncomment the following line to return proper output value
   	//myParam->Direction = ParameterDirection::Output;
           
   		try
   		{
               myConnection->Open();
               myCommand->ExecuteNonQuery();
   	    Console::Write("Return Value : {0} \n\n", myCommand->Parameters->Item["@out"]->Value->ToString());
   		}
           catch(Exception *ex) 
   		{
   			Console::Write(ex->ToString());
   		}
           __finally
   		{
               myConnection->Close();
   		}
   		
   		return 0;
   }
   
   int reader (void)
   {
           String *myConnString = "Data Source=sql_server_name;User ID=your_user_id;password=your_password;Initial Catalog=pubs;";
           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", SqlDbType::Int);
           SqlParameter *myParam = myCommand->Parameters->Item["@out"]; 
   	myParam->Direction = ParameterDirection::Output;
   
           try
   	{
               myConnection->Open();
               myReader = myCommand->ExecuteReader();
   
               //Uncomment the following line to return proper output value
               // myReader->Close();
   	    Console::Write("Return Value : {0} \n\n", myCommand->Parameters->Item["@out"]->Value->ToString());
   	}
           catch(Exception *ex)
   		{
   			Console::Write(ex->ToString());
   		}
           __finally
   		{
               myConnection->Close();
   		}
           return 0;
   
   }

5. Modify the Connection string (myConnString), as appropriate for your environment.
6. Save your project. On the Debug menu, click Start Without Debugging to run your project.
7. Notice that the direction method appears to be uninitialized and to have thrown an exception:
   System.Data.SqlClient.SqlException: Procedure 'MyProc' expects parameter '@out', which was not supplied.
   Uncomment the line of code that sets the direction property for the output parameter, and then run the project again to produce the proper behavior.
8. Now, notice that the reader method appears to be uninitialized and to have thrown an exception:
   System.NullReferenceException: Value null was found where an instance of an object was required.
   Uncomment the line of code that closes the reader object, and then re-run the project to produce the proper behavior.

Back to the top