Executing Stored Procedure and returning row count back to code via output and sql parameters

Question

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.

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.

Even though the program works the intRecCount variable is always zero rather than the row count.

Stored Procedure:

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

Method/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;

... then a variable to hold the row count via an output variable ...

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;
}

Solution

You need to access the value of output parameter after executing the query not before. So move this line after execution of query, and before clearing parameters, like:

//VARIABLE TO HOLD ROW COUNT VIA OUTPUT VIARABLE 
Int32 intRecCount = Convert.ToInt32(cmdSQL.Parameters["@RecordCount"].Value);

So your code for method would be:

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;



SqlDataAdapter da = new SqlDataAdapter(cmdSQL);
DataSet ds = new DataSet();
da.Fill(ds);
Int32 intRecCount = 0;
try
{
    Connection.Open();
    cmdSQL.ExecuteNonQuery();
    //VARIABLE TO HOLD ROW COUNT VIA OUTPUT VIARABLE
    intRecCount = Convert.ToInt32(cmdSQL.Parameters["@RecordCount"].Value);

}
catch (Exception ex)
{
    dbMsg = ex.Message;
}
finally
{
    Connection.Close();
    cmdSQL.Dispose();
    cmdSQL.Parameters.Clear();
}


return ds;
}

Comments

[Serge]

Not after "cmdSQL.Parameters.Clear();". You have to declare Int32 intRecCount before the try catch and make the assignment before the clear.

[Asynchronous]

Thanks Habib, and Serge thank you also. I really appreciate your help and knowledge. Guy!