output clause returns value but executescalar returns null in c#

Viewed 1381


I need to get the updated row's first column value. However when I run query Update ClaimDetails set sStatus='False' OUTPUT inserted.slno as Slno where inVoiceNo='******' and sStatus='True'
in Management Studio it returns the right value. But when I try to get the value using Executescalar() it returns null

My code:

 bool isupdated = false;
        int modified=0;
        try
        {

            string updateqry = "Update ClaimDetails set sStatus=@sStatus OUTPUT inserted.slno as Slno where inVoiceNo=@inVoiceNo and sStatus='True'";
            SqlCommand cmd = new SqlCommand(updateqry, con);
            cmd.Parameters.AddWithValue("@sStatus", sStatus);
            cmd.Parameters.AddWithValue("@inVoiceNo", inVoiceNo);
            connect();
            if (cmd.ExecuteNonQuery() > 0)
            {
                isupdated = true;
                 //modified = (int)cmd.ExecuteScalar();
                object a = cmd.ExecuteScalar();
                if (a != null)
                    modified = Convert.ToInt32(a);

            }
        }
        catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); }
        finally { disconnect(); }
        return modified;

When I used modified = (int)cmd.ExecuteScalar(); it gave me an exception error so I used object

3 Answers

in case someone want the actual number of records on update not just handling the null value ,, in the stored procedure our DB developer set return @@ROWCOUNT [typo] instead SELECT @@ROWCOUNT

Related