title: Executing Stored Procedure and returning row count back to code via output and sql parameters
body: <p>I have the following code that matches user input via session variables. The Stored Procedure returns the Row Count if the data in the Session Variable matches the data in the database.</p>
  
  <p>Everything works except I want to return the Row Count which will always be a single row:
  In a nutshell, you visit a form, add info and hit submit. The data is stored in session and the stored procedure returns the data when matched.</p>
  
  <p>Even though the program works the <strong><em>intRecCount</em></strong> variable is always zero rather than the row count.</p>
  
  <p><em><strong>Stored Procedure:</em></strong></p>
  
  <pre><code>CREATE PROCEDURE [dbo].[uspConfirmation]
  
      @RecordID CHAR(36),
      @LName VARCHAR(30),
      @FName VARCHAR(30),
      @MInit CHAR(1),
      @RecordCount INT OUTPUT
  AS
  
  SELECT * FROM Registration
  WHERE RecordID = @RecordID AND
        LName = @LName AND
        FName = @FName AND
        MInit = @MInit
  
  SET @RecordCount = @@ROWCOUNT
  
  RETURN
  </code></pre>
  
  <p><em><strong>Method/Code:</em></strong></p>
  
  <pre><code>public static DataSet Confirmation()
  { 
  SqlCommand cmdSQL = new SqlCommand("uspConfirmation", Connection);
  cmdSQL.CommandType = CommandType.StoredProcedure;
  
  cmdSQL.Parameters.Add(new SqlParameter("@RecordID", SqlDbType.VarChar, 36));
  cmdSQL.Parameters["@RecordID"].Direction = ParameterDirection.Input;
  cmdSQL.Parameters["@RecordID"].Value = RecordIDSession;
  
  cmdSQL.Parameters.Add(new SqlParameter("@LName", SqlDbType.VarChar, 30));
  cmdSQL.Parameters["@LName"].Direction = ParameterDirection.Input;
  cmdSQL.Parameters["@LName"].Value = LNameSession;
  
  cmdSQL.Parameters.Add(new SqlParameter("@FName", SqlDbType.VarChar, 30));
  cmdSQL.Parameters["@FName"].Direction = ParameterDirection.Input;
  cmdSQL.Parameters["@FName"].Value = FNameSession;
  
  cmdSQL.Parameters.Add(new SqlParameter("@MInit", SqlDbType.Char, 1));
  cmdSQL.Parameters["@MInit"].Direction = ParameterDirection.Input;
  cmdSQL.Parameters["@MInit"].Value = MNameSession;
  
  cmdSQL.Parameters.Add(new SqlParameter("@RecordCount", SqlDbType.Int));
  cmdSQL.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
  </code></pre>
  
  <p>... then a variable to hold the row count via an output variable ...</p>
  
  <pre><code>Int32 intRecCount = Convert.ToInt32(cmdSQL.Parameters["@RecordCount"].Value);
  
  SqlDataAdapter da = new SqlDataAdapter(cmdSQL);
  DataSet ds = new DataSet();
  da.Fill(ds);
  
  try {
      Connection.Open();
      cmdSQL.ExecuteNonQuery();
  }
  catch (Exception ex) {   
      dbMsg = ex.Message; 
  }
  finally {
      Connection.Close();
      cmdSQL.Dispose();
      cmdSQL.Parameters.Clear();
  }
  return ds;
  }
  </code></pre>