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.
.NET Programming

Avatar of undefined
Last Comment
DaveKid

8/22/2022 - Mon
SOLUTION
HugoHiasl

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
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
Carl Tawn

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
DaveKid

ASKER
Thanks everyone!
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes