Int Result Doesnt Increment using Cmd.ExecuteScalar PRoblem?

Question

Now this is my Code on Updating Records.

Data Access:

    public int UpdateBatch(FillinEntity fin)
    {
        int result = 0;
        using (SqlConnection conn = new SqlConnection(connStr))
        {
            conn.Open();

            using (SqlCommand dCmd = new SqlCommand("UpdatebyBatch", conn))
            {
                dCmd.CommandType = CommandType.StoredProcedure;
                try
                {
                    dCmd.Parameters.AddWithValue("@Batch", fin.Batch);
                    dCmd.Parameters.Add("@Date", SqlDbType.DateTime).Value = DateTime.Now.ToString();
                    dCmd.Parameters.AddWithValue("@User", fin.ModifiedBy);

                    result = Convert.ToInt32(dCmd.ExecuteScalar());

                    return result;

                }
                catch (SqlException ee)
                {
                    throw ee;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();

                }
            }
        }
    }

BUSINESS LOGIC:

    public int UpdateBatch(FillinEntity fin)
    {
        DAL pDAL = new DAL();
        try
        {
            return pDAL.UpdateBatch(fin);
        }
        catch
        {
            throw;
        }
        finally
        {
            pDAL = null;
        }
    }

UI:

         FillinEntity fin = new FillinEntity();
         BAL pBAL = new BAL();
         try
         {
            fin.Batch = txtBACTHCODE.Text.Trim();
            fin.ModifiedBy = lblUser.Text;

            int result = pBAL.UpdateBatch(fin);
            if (result > 0)
            {
                MessageBox.Show("Make Sure Batch is All Kitted!");

            }
            else
            {
                MessageBox.Show("Record Updated Successfully.");
            }

SQL:

UPDATE dbo.FG_FILLIN  SET 
    Status='SHIPPED'
    ,DateModified=@Date 
    ,ModifiedBy=@User
WHERE Batch = @Batch and (Status='KITTED')

My Problem is It always return 0 result so my Message Box always Prompt Successfull even my Status is NOT KITTED.

Thanks in Regards!

Answer 1

In order for ExecuteScalar to return a value, you have to return one. You can modify your SQL to look like this:

UPDATE dbo.FG_FILLIN  SET 
    Status='SHIPPED'
    ,DateModified=@Date 
    ,ModifiedBy=@User
WHERE Batch = @Batch and (Status='KITTED')

SELECT @@ROWCOUNT

or you can use the ExecuteNonQuery method instead.

Answer 2

Your stored procedure isn't returning a value thatExecuteScalar can make use of. You can use the ExecuteNonQuery method instead to return the number of affected records.

result = dCmd.ExecuteNonQuery();