insert query via connection.execute(sql,records) returning a record count even though query doesn't insert records

[bgrushin]

Hi All,

I have a process that gathers data and inserts into 3 tables via 3 insert queries in a ms sql server database. Recently the user made a mistake and left in data that was already in the database, which is a primary key violation and will fail the query. All of the queries are wrapped in

begin transaction [Tran1]
begin try
...sql here...
commit transaction [Tran1]
end try begin catch rollback transaction [Tran1] end catch

code to prevent some records updating without others.

In the execute command I have a records affected variable parameter which since the query fails I expect to be 0 but its returning the number of records that were attempted to be inserted but weren't.

When I try to run the full query in SQL server I just get "Command(s) completed successfully." returned however I confirmed by checking that the query did not work.

What modification do I need to make to make the recordsaffected 0 in such a case? I use that check to either stop the process and throw an error or continue, so the process finished without the user realizing that records didn't go in.

Thanks in advance,
Boris

 

[bgrushin]

I should add that I'm trying to avoid having to run a select statement after the fact to double check unless I absolutely have to due to either a bug or software limitation.

Thanks