ExecuteScalar on SQL Server row update

Question

I'm having trouble getting a return value from SQL Server with the ID of the row it just updated.

I want to get the id of the row I just updated to use in the following query, but when I execute this method the first T-SQL statements update the row fine but the object activeRecord does not get the row number returned (no value)

If I run the T-SQL statement in SQL directly, I do get a return value. I've used ExecuteScalar elsewhere in my code without issue.

What am I missing here?

if (Regex.IsMatch(Encoding.ASCII.GetString(udpMessageReceived), "Valitating"))
{
   //a device is validating itself following endpoint registration
   string[] splitaction = Encoding.ASCII.GetString(udpMessageReceived).Split(deliminator);

   using (var conn = new SqlConnection(Globals.ConnectionString))
   {
       conn.Open();
       object activeRecord;

       using (var comm = new SqlCommand("UPDATE oView_MachineRegister SET validated= 1 where hostname= @hostname; SELECT ID = SCOPE_IDENTITY()", conn))
       {
           comm.Parameters.AddWithValue("datenow", DateTime.Now);
           comm.Parameters.AddWithValue("hostname", splitaction[1]);

           activeRecord = comm.ExecuteScalar();
       }

       using (var comm2 = new SqlCommand("DELETE from oView_OutBoundMessageQ WHERE machines = @activerecord AND command = 'Validate'", conn))
       {
           comm2.Parameters.AddWithValue("activerecord", activeRecord);
           comm2.ExecuteNonQuery();
       }

       conn.Close();
    }
}

scope_identity is for last insert in same scope. Do a select statement to get the key by date and host, then use that for the update and return it. — Tony Hopkinson, Sep 05 '12 at 21:39
@TonyHopkinson thanks, I didn't realise scope_identity for only the last insert. I only ever used it on inserts previously — Damo, Sep 05 '12 at 21:46

score 4 · Accepted Answer · edited May 23 '17 at 12:14

4

found the answer on SQL: Update a row and returning a column value with 1 query

Changed the query to:

UPDATE oView_MachineRegister SET validated = 1 OUTPUT INSERTED.id WHERE hostname = @hostname

Now it return the last updated ID

edited May 23 '17 at 12:14

Community

1
1

answered Sep 05 '12 at 21:46

Damo

1,898
7
38
58

Have a plus, I've been having to maintain sql 2000 compatibility for so long, I'd forgotten that feature had been introduced. – Tony Hopkinson Sep 06 '12 at 16:43

ExecuteScalar on SQL Server row update

1 Answers1