The release number of this article has been CHS308621
For Microsoft Visual Basic .NET versions of this article, see
308051.
For Microsoft Visual C .NET versions of this article, see
308624.
This article references the following Microsoft .NET Framework Class Bank Name Space:
System.data.sqlclient system.data.oledb
When the symptoms run the ADO.NET command, the output parameters are not initialized or returned.
There are two reasons for this issue:
When using the DataReader object, the output parameter returns at the end of the data stream. The Direction property setting of the parameter is incorrect.
Solution To resolve this issue, use one of the following methods:
When using the DataReader object, you must turn it off or read to the end of the data to view the output parameters. Make sure the direction of the parameter is set to Output or InputOutput (if this parameter is used to send and receive data). For more information on how to implement this solution, see the "More Information" section.
Remarks: The parameter object returned value must be
The first item of the parameters collection. In addition, ensuring that the data type of the parameters is matched to the expected return value.
This phenomenon is designed to make.
More information
Steps to reproduce the phenomenon
Run the following query in the SQL Server query analyzer, create a stored procedure called "MyProc" in the PUBS database. Create proc myproc
@out Smallint Output
AS
Select * from Titles
SELECT @out = count (*) from Titles
Go starts Visual Studio .NET. Create a Windows application in Visual C # .NET. FORM1 will be created by default. Make sure your project contains a reference to System.Data namespace, if not included, add a reference to this namespace. Place two command buttons in Form1. Change the Name property of the first button to BTNDIRECTION to change the Text property to Direction. Change the Name property of the second button to BTNReader to change the Text property to Reader. Using the USING statement in System and System.Data namespace, this is not required to limit the declarations in these namespaces in the code. Using system;
Using system.data;
Using system.data.sqlclient;
Using system.data.oledb; Return to Forms view, then double-click Direction Add Click the event handler. Add the following code to the handler: String myConnString =
"User ID = sa; password = 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 property 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.totring ());
}
Finally
{
MyConnection.Close ();
} Return to Forms view, then double-click Reader Add Click the event handler. Add the following code to the handler: String myConnString =
"User ID = sa; password = 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 property output value.
//myreader.close ();
Messagebox.show ("Return Value:" mycommand.parameters ["@ out"]. Value);
}
Catch (Exception EX)
{
Messagebox.show (ex.totring ());
}
Finally
{
MyConnection.Close ();
} Modify the connection string (MyConnString) accordingly in the sample code of the above segment according to your environment. Save the project. On the Debug menu, click Start to run your project. Click Direction. Note that the return value of the output parameter is returned. To resolve this issue, cancel the annotation of the code line to set the output parameter Direction property. Run the project and click Direction. Note that the output parameters are now returned correctly. Click Reader. Note that the return value of the output parameter is returned. To resolve this issue, cancel the comment on the code line to close the Reader object. Run the project, and then click Reader. Note that the output parameters are now returned correctly. Refer to additional information, click the following article number to see the article in the Microsoft Knowledge Base:
308049 HOW TO: Using ADO.NET and Visual Basic .NET calls the stored procedure with parameters
The information in this article applies to:
Microsoft ADO.NET (provided with .NET Frame) Microsoft Visual C # .NET (2002)
Recent Updated: 2002-6-19 (1.0) Keyword KBDsupport KBGRPDSMDAC KBGRPDSVBDB KBOLEDB KBPRB KBSQLCLIENT KBSTOREDPROC KBSYSTEMDATA KB308621
