title: Strongly-typed dataset not retrieving return value from stored procedure
body: <p>I have a strongly-typed dataset that is using DBDirectMethods to insert data into a database using calls to stored procedures. The stored procedures return the primary key of the newly-created record. Here's a sample stored procedure:</p>
  
  <pre><code>CREATE PROCEDURE dbo.CreateUser
  (
      @UserName   VARCHAR(50)
      @Password   VARCHAR(50)
  )
  AS
  
  SET NOCOUNT OFF
  
  DECLARE @UserID INT
  
  BEGIN TRANSACTION
  
  INSERT INTO dbo.Users (UserName, Password) VALUES (@UserName, @Password)
  
  SET @UserID = SCOPE_IDENTITY()
  
  INSERT INTO dbo.Users_History (UserID, Status, TimeStamp) VALUES (@UserID, 'C', GETUTCDATE())
  
  COMMIT TRANSACTION
  
  RETURN @UserID
  
  GO
  </code></pre>
  
  <p>If I execute the stored procedure from SSMS, then the user account is created, the history table updated, and the primary key returned. If I run my application using the strongly-typed dataset and have SQL Profiler ticking away, I can see the same code being executed; however, the dataset returns -1 as the primary key and breaks the app.</p>
  
  <p>Inside the VS-generated code for the table adapter, the relevant lines are:</p>
  
  <pre><code>this._adapter.InsertCommand.CommandText = "dbo.CreateUser";
  this._adapter.InsertCommand.CommandType = global::System.Data.CommandType.StoredProcedure;
  this._adapter.InsertCommand.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@RETURN_VALUE", global::System.Data.SqlDbType.Int, 4, global::System.Data.ParameterDirection.ReturnValue, 10, 0, null, global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
  this._adapter.InsertCommand.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@UserName", global::System.Data.SqlDbType.VarChar, 50, global::System.Data.ParameterDirection.Input, 0, 0, "UserName", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
  this._adapter.InsertCommand.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@Password", global::System.Data.SqlDbType.VarChar, 50, global::System.Data.ParameterDirection.Input, 0, 0, "Password", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
  </code></pre>
  
  <p>and</p>
  
  <pre><code>try {
      int returnValue = this.Adapter.InsertCommand.ExecuteNonQuery();
      return returnValue;
  }
  finally {
      if ((previousConnectionState == global::System.Data.ConnectionState.Closed)) {
          this.Adapter.InsertCommand.Connection.Close();
      }
  }
  </code></pre>
  
  <p>which is all just the standard boiler-plate code that VS usually generates - nothing has been edited by hand. It just doesn't pick up the return value.</p>
  
  <p>I am running Windows 7 with SQL 2008 R2 SP1 Express.</p>