Microsoft KB Archive/308624

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

Article ID: 308624

Article Last Modified on 3/13/2006

-

APPLIES TO


 * Microsoft .NET Framework 1.1 Service Pack 1
 * Microsoft ADO.NET 2.0
 * Microsoft Visual C++ .NET 2002 Standard Edition
 * Microsoft Visual C++ .NET 2003 Standard Edition

-



This article was previously published under Q308624



Note Microsoft Visual C++ .NET (2002) supports both the managed code model that is provided by the Microsoft .NET Framework and the unmanaged native Microsoft Windows code model. The information in this article applies only to unmanaged Visual C++ code.



For a Microsoft Visual C# .NET version of this article, see 308621.

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

This article refers to the following Microsoft .NET Framework Class Library namespace:
 * System::Data::SqlClient



SYMPTOMS
When you run an ADO.NET command, the output parameters may not be initialized, or they may return an incorrect value.



CAUSE
This behavior occurs if the direction of the parameter is not set properly. Output parameters are returned at the end of the data stream when you use a DataReader object.



RESOLUTION
To resolve this issue, do both of the following:
 * 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).

Note Ensure that the parameter object for the return value is the first item in the Parameters collection. Additionally, ensure that the parameter's data type matches that of the expected return value.



STATUS
This behavior is by design.



Steps to reproduce the problem
  Create a stored procedure named &quot;MyProc&quot; 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 (&quot;@out&quot;).  Start Microsoft Visual Studio .NET. Create a new Managed C++ project. Name the project &quot;outParam.&quot;  Copy and paste the following code in the outParam.cpp file, overwriting the existing code generated by Visual Studio .NET:
 * 1) include &quot;stdafx.h&quot;


 * 1) using <mscorlib.dll>
 * 2) using <System.dll>
 * 3) 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 int wmain(void) int main(void) {   Console::WriteLine(&quot;Running the parameter direction method 'direction' to check the return parameter value&quot;); direction; Console::WriteLine(&quot;\nRunning the data reader method 'reader' to check the return parameter value&quot;); reader;
 * 1) ifdef _UNICODE
 * 1) else
 * 1) endif

return 0; }

int direction(void) {       String *myConnString = &quot;Data Source=sql_server_name;User ID=your_user_id;password=your_password;Initial Catalog=pubs;&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;, SqlDbType::Int);

SqlParameter *myParam = myCommand->Parameters->Item[&quot;@out&quot;]; //Uncomment the following line to return proper output value //myParam->Direction = ParameterDirection::Output; try {           myConnection->Open; myCommand->ExecuteNonQuery; Console::Write(&quot;Return Value : {0} \n\n&quot;, myCommand->Parameters->Item[&quot;@out&quot;]->Value->ToString); }       catch(Exception *ex) {           Console::Write(ex->ToString); }       __finally {           myConnection->Close; }       return 0; }

int reader (void) {       String *myConnString = &quot;Data Source=sql_server_name;User ID=your_user_id;password=your_password;Initial Catalog=pubs;&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;, SqlDbType::Int); SqlParameter *myParam = myCommand->Parameters->Item[&quot;@out&quot;]; myParam->Direction = ParameterDirection::Output;

try {           myConnection->Open; myReader = myCommand->ExecuteReader;

//Uncomment the following line to return proper output value // myReader->Close; Console::Write(&quot;Return Value : {0} \n\n&quot;, myCommand->Parameters->Item[&quot;@out&quot;]->Value->ToString); }       catch(Exception *ex) {           Console::Write(ex->ToString); }       __finally {           myConnection->Close; }       return 0;

} </li> Modify the Connection string (myConnString), as appropriate for your environment.</li> Save your project. On the Debug menu, click Start Without Debugging to run your project.</li> 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.</li> 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.</li></ol>

<div class="references_section">