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

Article translations Article translations
Article ID: 308051 - View products that this article applies to.
This article was previously published under Q308051
This article has been archived. It is offered "as is" and will no longer be updated.
For a Microsoft Visual C# .NET version of this article, see 308621.

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
  • System.Data.OleDb
  • System.Data.SqlClient
Expand all | Collapse all

On This Page

SYMPTOMS

Output parameters do not appear to be initialized or return a wrong value when executing an ADO.NET command.

CAUSE

This problem can occur for the following reasons:
  • Output parameters are returned at the end of the data stream when using a DataReader object.
  • The Direction property of the parameter is not set properly.

RESOLUTION

  • When using a DataReader, you must close it or read to the end of the data before the output parameters are visible.
  • Make sure that the Direction property of the parameter is set to Output, or InputOutput if the parameter is used in the procedure to both send and receive data.
NOTE: The parameter object for the return value must be the first item in the Parameters collection. Also make sure that the parameter's data type matches the expected return value.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Create a stored procedure "myProc" in the Pubs database by executing the following query in SQL Server Query Analyzer:
    CREATE proc MyProc
    @out integer OUTPUT
    AS
    Select @out = count(*) from authors
    GO
    					
  2. Start Microsoft Visual Studio .NET.
  3. Create a new Windows Application in Visual Basic .NET.
  4. Make sure that your project contains a reference to the System.Data namespace.
  5. Place two Command buttons on Form1. Change the Name property of the first button to btnDirection and the Text property to Direction. Change the Name property of the second button to btnReader and the Text property to Reader.
  6. Use the Imports statement on the System and System.Data namespaces so that you are not required to qualify declarations in those namespaces later in your code. Add this to the General Declarations section of Form1.
    Imports System
    Imports System.Data.OleDb
    Imports System.Data.SqlClient
    					
  7. Paste the following code in the code window after the region "Windows Form Designer generated code."

    Note You must replace User ID <user name> with an account that has appropriate permissions to perform these operations on the database.
        Private Sub btnDirection_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDirection.Click
            Dim myConnString As String = _
                    "User ID=<username>;password=<strong password>;Initial Catalog=pubs;Data Source=myServer"
            Dim myConnection As New SqlConnection(myConnString)
            Dim myCommand As New SqlCommand()
    
            myCommand.CommandType = CommandType.StoredProcedure
            myCommand.Connection = myConnection
            myCommand.CommandText = "MyProc"
            myCommand.Parameters.Add("@out", OleDbType.Integer)
            'Uncomment this line to return proper output value.
            'myCommand.Parameters("@out").Direction = ParameterDirection.Output
            Try
                myConnection.Open()
                myCommand.ExecuteNonQuery()
                MessageBox.Show("Return Value : " & myCommand.Parameters("@out").Value)
            Catch ex As Exception
                MessageBox.Show(ex.ToString())
            Finally
                myConnection.Close()
            End Try
        End Sub
    
        Private Sub btnReader_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReader.Click
            Dim myConnString As String = _
                        "User ID=<username>;password=<strong password>;Initial Catalog=pubs;Data Source=myServer"
            Dim myConnection As New SqlConnection(myConnString)
            Dim myCommand As New SqlCommand()
            Dim myReader As SqlDataReader
    
            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 proper output value.
                'myReader.Close()
                MessageBox.Show("Return Value : " & myCommand.Parameters("@out").Value)
    
            Catch ex As Exception
                MessageBox.Show(ex.ToString())
            Finally
                myConnection.Close()
            End Try
        End Sub
    					
  8. Modify the Connection string (myConnString) as appropriate for your environment.
  9. Save your project. On the Debug menu, click Start to run your project.
  10. Click the Direction button and you can see that an incorrect value is returned for the output parameter.
  11. Uncomment the line of code that sets the Direction property for the output parameter and then run the project. Now you can see that output parameter is returned correctly when the Direction button is clicked.
  12. Click the Reader button and you may see that an incorrect value is returned for the output parameter.
  13. Uncomment the line of code that closes the DataReader object and then run the project. Now you can see that output parameter is returned correctly when the Reader button is clicked.

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
308049 HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic .NET

Properties

Article ID: 308051 - Last Review: February 27, 2014 - Revision: 2.7
APPLIES TO
  • Microsoft ADO.NET 2.0
  • Microsoft ADO.NET (included with the .NET Framework)
  • Microsoft Visual Basic .NET 2002 Standard Edition
  • Microsoft Visual Basic .NET 2003 Standard Edition
  • Microsoft Visual Basic 2005
Keywords: 
kbnosurvey kbarchive kbtshoot kbnofix kbprb kbsqlclient kbsystemdata KB308051

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com