Link to home
Start Free TrialLog in
Avatar of DaveKid
DaveKid

asked on

TransactionScope With Conditional Deletes, Selects

When using TransactionScope, I'm checking for success this way:

Dim Success as Boolean = False

...code, code,code
success = oCom.ExecuteNonQuery()

I saw it in many examples provided by Microsoft.   However, I'm having an issue with a conditional delete.   I have a delete statement that doesn't always find rows to delete.  So sometimes it returns 0 and therefore success would be set to False.

Additional, the method above cannot be used with a select statement at all.  Do I even really need it for a select?  

If someone could give me an elegant way to handle my delete I would appreciate it.  And if you could tell me whether I really even need to check the select that would be appreciated as well.
SOLUTION
Avatar of HugoHiasl
HugoHiasl

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of DaveKid
DaveKid

ASKER

What I did for right now is Dim a variable called "RowsAffected" and set it = -1.   Then I reset it for the conditional delete.  If it ends up being 0 then I know that SOMETHING happened (i.e. it was able to connect to the db and run the sql without error).

I hate the name RowsAffected though as sometimes that value is 0

Question, if the number of rows inserted or modified is gt 1 will success automatically set to true?

D
Avatar of jitendra patil
hi davekid,

i could not fully understand your question here, but my guess is you are trying to delete some rows  and you want to know how many rows were deleted.

then please try the below hack.

create a stored procedure with transactions and with SET NOCOUNT OFF.

and try the below query ( for example)

delete from mytable
where datefield = '8-Aug-2014'
output datefield into #Myval

select count(*) from #Myval

if you can please post the complete code here on which you are working, so that we can get you to the correct directions.

Hope this helps.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of DaveKid

ASKER

Thanks everyone!