Affected rows in SQL Server not being returned after ExecuteScalar

Question

0.00/5 (No votes)

See more:

I have the following stored procedure:

C#

SET XACT_ABORT ON

BEGIN TRAN

INSERT INTO SystemUsers (usr_us_id)
     SELECT us_pk
       FROM SFSUsers LEFT OUTER JOIN SystemUsers ON us_pk = usr_us_id
      WHERE usr_us_id IS NULL

INSERT INTO Addresses (add_usr_id)
     SELECT usr_id
       FROM SystemUsers LEFT OUTER JOIN Addresses ON usr_id = add_usr_id
      WHERE add_usr_id IS NULL

COMMIT TRAN


END

Being called from a business object which has an ExecuteScalar() method defined in the parent class ...

C#

public int SynchronizeWithSecurity()
{
    sqlCommandBuilderApp oSQLCmd = new sqlCommandBuilderApp("SystemUsers_SynchronizeWithSecurity",CommandType.Text);

    int numberSynched = Convert.ToInt32(ExecuteScalar(oSQLCmd.SqlCmd));

    return (int)numberSynched;
}

Even though three user records were synchronized ... ExecuteScalar always returns 0. I even tried to use SELECT @@ROWCOUNT at the end of the proc. but no dice. Does the Transaction processing block the number of affected rows from being returned to the invoking method?

Thanks

Posted 31-Mar-14 10:17am

Member 10544915

Add a Solution

Comments

ZurdoDev 31-Mar-14 16:41pm

ExecuteScalar will return the first column of the first record so to get a record count you definitely will have to return it from the stored proc. You likely need to store the row count into a variable between your insert statement and the commit tran. Then return that value.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2014-03-31T11:34:00

Have a look here: SqlCommand.ExecuteScalar Method [^] plus @@ROWCOUNT[^]

If you would like to return the count affected records, you need to write SP in that way:

SQL

DECLARE @inserted INT
SET @inserted = 0

INSERT ....
SET @inserted = @@ROWCOUNT


INSERT ...
SET @inserted = @inserted + @@ROWCOUNT 

SELECT @inserted AS ReturnedValue