• 0

C# - SqlTransaction has completed


Question

Hi,

 

I'm having trouble with the code below used in a .Net 4.5.2 website. The error I get is that the sql transaction is no longer useable. I'm guessing the first insert is failing and the app isn't rolling back the transaction properly.

 

Secondly - the bizarre thing I can pass the exact same values from one machine that will work but from another machine it will not.

 

if you need more info ask away.

 

thanks.

using (SqlConnection connection = new SqlConnection(cs))
            {
                connection.Open();
                SqlCommand cmd = connection.CreateCommand();
                SqlTransaction tran;

                tran = connection.BeginTransaction("sampleTransaction");

                cmd.Connection = connection;
                cmd.Transaction = tran;

                try
                {
                    cmd.CommandText = @"INSERT INTO dbo.sk_srf_headm(order_ref, customer, reason, returningSample, comments, submitted_by, dateReq, requested_by)
                                                     VALUES(@order_ref, @customer, @reason, @returning_sample, @comments, @submitted_by, @date_req, @requested_by)";
                    cmd.Parameters.AddWithValue("@order_ref", order.orderRef);
                    cmd.Parameters.AddWithValue("@customer", order.customer);
                    cmd.Parameters.AddWithValue("@date_req", SqlDbType.DateTime).Value = order.dateReq;
                    cmd.Parameters.AddWithValue("@reason", order.reason);
                    cmd.Parameters.AddWithValue("@returning_sample", order.returningSample);
                    cmd.Parameters.AddWithValue("@comments", order.comments);
                    cmd.Parameters.AddWithValue("@submitted_by", order.salesExec);
                    cmd.Parameters.AddWithValue("@requested_by", order.salesExecFor);
                    cmd.ExecuteNonQuery();

                    int line_no = 1;
                    foreach (orderItem item in order.Items.getItems())
                    {
                        cmd.CommandText = @"INSERT INTO dbo.sk_srf_detm(order_ref, line_no, product, description, quantity, oc_qty, freeStock, locations)
                                VALUES(@srfRef, @line_no, @product, @description, @quantity, @oc_qty, @freeStock, @locations)";
                        cmd.Parameters.AddWithValue("@srfRef", order.orderRef);
                        cmd.Parameters.AddWithValue("@line_no", line_no);
                        cmd.Parameters.AddWithValue("@product", item.productNumber);
                        cmd.Parameters.AddWithValue("@description", item.description);
                        cmd.Parameters.AddWithValue("@quantity", item.quantity);
                        cmd.Parameters.AddWithValue("@oc_qty", item.oc_qty);
                        cmd.Parameters.AddWithValue("@freeStock", item.freeStock);
                        cmd.Parameters.AddWithValue("@locations", item.locations);
                        cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        line_no++;
                    }
                    tran.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    try
                    {
                        tran.Rollback();                        
                    }
                    catch
                    {

                    }
                    return false;
                }
            }

 

Link to comment
https://www.neowin.net/forum/topic/1295266-c-sqltransaction-has-completed/
Share on other sites

Recommended Posts

  • 0

not clear if that is the exact code you are using because you are not closing the connection and you are not printing the exception in either catch

 

if the exception throws before the commit for example, the rollback is stupid

 

so it doesn't look like real code you would actually use...

 

and a return inside a catch just hurts my eyeballs...

 

 

 

  • 0

Which exact line is throwing the exception? 

 

  Quote

I'm guessing the first insert is failing and the app isn't rolling back the transaction properly.

Expand  

If the rollback is failing it will be throwing another exception, what exception is it throwing? At the moment you're just swallowing the exception with an empty catch block. 

 

  On 19/04/2016 at 18:30, DevTech said:

not clear if that is the exact code you are using because you are not closing the connection 

Expand  

He doesn't need to close the connection. It's created inside a using block and SqlConnection implements IDisposable with the Dispose method ensuring the connection is automatically closed. 

  • 0
  On 19/04/2016 at 19:16, ZakO said:

He doesn't need to close the connection. It's created inside a using block and SqlConnection implements IDisposable with the Dispose method ensuring the connection is automatically closed. 

Expand  

Yeah but that won't happen until garbage collection, making it a possible reason why the code works on one machine and not the other.

 

(which I should have pointed out as the reason for that comment, but there is only so much time in the universe)

 

  • 0
  On 19/04/2016 at 19:19, DevTech said:

Yeah but that won't happen until garbage collection, making it a possible reason why the code works on one machine and not the other.

 

(which I should have pointed out as the reason for that comment, but there is only so much time in the universe)

 

Expand  

Dispose will be called and the connection closed immediately as the code exits the using block, it doesn't wait for a GC. 

  • 0
  On 19/04/2016 at 19:32, ZakO said:

Dispose will be called and the connection closed immediately as the code exits the using block, it doesn't wait for a GC. 

Expand  

I've seen all sorts of weird garbage collection behaviour and he has not specified his machines, O/S, patch level etc so I'd be inclined to run tests or a debugger and see what is going on

 

Can't find a shred in interest within me in doing that kind of thing here. I just do UWP these days which now generates a C++ backend executable for all sorts of new mangled runtime oddities.

  • 0
  On 19/04/2016 at 19:27, Riva said:

put a using on your transaction. Commit stays inside.

You said this value works from one machine but not the other. Can you expand a bit more? Does this code work on another machine?

Expand  

This has been published on IIS so we can test with a few users. Out of around 5 users the application throws the sqltransaction error for two of them and intermittently works for them sometime. Oddly enough if I submit the same information as them it always works for me and for other users,

 

  On 19/04/2016 at 19:45, DevTech said:

I've seen all sorts of weird garbage collection behaviour and he has not specified his machines, O/S, patch level etc so I'd be inclined to run tests or a debugger and see what is going on

 

Can't find a shred in interest within me in doing that kind of thing here. I just do UWP these days which now generates a C++ backend executable for all sorts of new mangled runtime oddities.

Expand  

 How would I attach a debugger to a users machine or rather how could I debug whilst the user in using the web app.

 

  On 19/04/2016 at 19:16, ZakO said:

Which exact line is throwing the exception? 

 

If the rollback is failing it will be throwing another exception, what exception is it throwing? At the moment you're just swallowing the exception with an empty catch block. 

 

He doesn't need to close the connection. It's created inside a using block and SqlConnection implements IDisposable with the Dispose method ensuring the connection is automatically closed. 

Expand  

I'm not sure where error is being thrown - I might add a label on the web page and print the error out on the catch statements.

  • 0
  On 19/04/2016 at 20:11, limok said:

This has been published on IIS so we can test with a few users. Out of around 5 users the application throws the sqltransaction error for two of them and intermittently works for them sometime. Oddly enough if I submit the same information as them it always works for me and for other users,

 

 How would I attach a debugger to a users machine or rather how could I debug whilst the user in using the web app.

 

I'm not sure where error is being thrown - I might add a label on the web page and print the error out on the catch statements.

Expand  

I wasn't taking this very seriously.

 

If that is real code that you are making for real people then pick one of thousand logging libaries on GitHub and log everything.

 

Every single catch block anywhere should log the exception.

 

And don't put a return statement inside a catch.

 

  • 0

If the error is data entry dependent then good chance one of your data fields doesn't convert correctly to one of the SQL fields for the user getting an error

 

So log all the data too...

 

For debugging, you can remote attach to the iis machine or just run the web app on your dev machine using the bad data sample.

 

 

  • 0
  On 19/04/2016 at 20:29, DevTech said:

If the error is data entry dependent then good chance one of your data fields doesn't convert correctly to one of the SQL fields for the user getting an error

 

So log all the data too...

 

For debugging, you can remote attach to the iis machine or just run the web app on your dev machine using the bad data sample.

 

 

Expand  

Like I said, I'm using the exact same data on a few machines, a couple of machines refuse to work.

 

A quick google returns ELMAH as a good debug logger on live asp.net web apps. I might use that.

  • 0

Logging

 

Most Starred:

 

https://github.com/search?l=C%23&o=desc&p=1&q=log&s=stars&type=Repositories&utf8=%E2%9C%93

 

Most Recently Updated:

 

https://github.com/search?l=C%23&o=desc&p=1&q=log&s=stars&type=Repositories&utf8=%E2%9C%93

 

When trying to quickly find a lib I try to balance those two searches so I have a very active but quality result.

 

 

  • 0
  On 19/04/2016 at 21:20, DevTech said:

Logging

 

Most Starred:

 

https://github.com/search?l=C%23&o=desc&p=1&q=log&s=stars&type=Repositories&utf8=%E2%9C%93

 

Most Recently Updated:

 

https://github.com/search?l=C%23&o=desc&p=1&q=log&s=stars&type=Repositories&utf8=%E2%9C%93

 

When trying to quickly find a lib I try to balance those two searches so I have a very active but quality result.

 

 

Expand  

thanks - will take a look 

  • 0
  On 19/04/2016 at 21:22, limok said:

thanks - will take a look 

Expand  

In the case of logging, I think every programmer gets the urge to make their own logging libary and too many give in to that itch and upload something to GitHub. The noise level for logging is rarher high - there are simply too many great logging libraries and too many crap ones and well just too many.... So the searches I gave you are not as helpful as for something more specialized...

 

So I will try to point out a few things.

 

(The most efficient low overhead logging is ETW which is built into every Windows computer and was originally  used only inside of device drivers. A while back Microsoft released the API for user level usage. A logging lib that targets ETW is worth looking at if you are confident of always running on Windows.)

 

 

 

  •  

 

 

  •  

 

 

  • 0

Hi,

 

I'm debugging the code on my machine and the debugger is doing crazy things jumping backwards and forwards and not going in the correct order.

 

Looks like the reason I'm getting this error is that it's trying to insert this data more than once causing a duplicate insert in sql with the same primary key.

 

Also getting the process or thread has changed since the last step. on the debugging arrow.

 

How do I correct this?

  • 0

Solved it - I was using an HTML button that had an event handler in the codebehind using onserverclick running the above code. For some reason this executed the code twice. I've since changed the button to an asp.net button and code is only executed once.

 

thanks for your help.

  • Like 2
  • 0
  On 20/04/2016 at 23:04, BinaryData said:

As someone who requests help frequently, thanks for not being a jack ass like a lot of people have been. :)

Expand  

We all need lots of elastic material in our skins and lots of tolerance for different styles of communication. The simple fact that wonderful human beings take the time to type something in to keep a forum discussion going even if their text is some sort of negative anti-pattern is still a miracle of the internet community.

 

And well, I'm fairly certain, despites my efforts to the contrary, that I was perceived as a jack ass in the recent thread on PC security...

  • 0
  On 19/04/2016 at 18:30, DevTech said:

not clear if that is the exact code you are using because you are not closing the connection and you are not printing the exception in either catch

 

 

Expand  

not sure why you think this... the using block guarantees that the connection is closed on exit of scope all the using block is is a try catch finally block, it doesn't close on GC like you implied, it closes all the connections in the finally block, there is zero need to close or dispose of the connection when instantiated in a using block, it is guaranteed to be closed when it exits the block immediately

  • 0
  On 21/04/2016 at 00:17, neufuse said:

not sure why you think this... the using block guarantees that the connection is closed on exit of scope all the using block is is a try catch finally block, it doesn't close on GC like you implied, it closes all the connections in the finally block, there is zero need to close or dispose of the connection when instantiated in a using block, it is guaranteed to be closed when it exits the block immediately

Expand  

This point was made and answered earlier in the thread.

 

At the time I was mentally searching for edge cases that could explain why it worked on one computer and not the other one and having seen weird garbage collection issues in the past I was musing to myself on if the compiler might do oddball code gen based on the return statement inside the nested catch creating a stack unwind tree that would lead to defensive codegen for deferred garbage collection. Just one of many thoughts that rambled thru my head at the time...

 

But my premise that safe code generation would have priority over memory management was completely wrong as it turns out objects can get garbage collected while their methods are still executing so I won't hold that thought in my head again!

 

https://blogs.msdn.microsoft.com/oldnewthing/20100810-00/?p=13193

 

as it turns out, his issue had nothing at all to do with his code.

 

  • 0

Off-topic:

 

My previous post linked to a Raymond Chen blog article and I couldn't resist reading a few more of his no-nonsense articles. :)

 

So when I see the same code working on one compter and not the other, I realized that "creepy spooky juju" is a perfect description of the first thought thought that pops into my head and that it is almost always the wrong thought! I need to be slapping myself in the head every time that happens!

 

No connection to the thread, this article is delicious:

 

"Finalizers are a Ouija board, permitting dead objects to operate “from beyond the grave” and affect live objects. As a result, when finalizers are involved, there is a lot of creepy spooky juju going on, and you need to tread very carefully, or your soul will become cursed."

 

https://blogs.msdn.microsoft.com/oldnewthing/20100813-00/?p=13153

 

  • 0

Just out of interest why would an HTML button cause this. the code was

 

<button ID="btnSubmit" runat="server" class="btn-primary btn" onServerClick="btnSubmit_Click">Submit</button>

changed to

<asp:Button ID="btnSubmit" runat="server" Text="Submit" CssClass="btn-primary btn" OnClick="btnSubmit_Click" /> 

the aspx page is inheriting a master page.

 

it was pure luck that this fixed. what kind of debugging/tools would you guys have done to find the culprit?

  • 0
  On 21/04/2016 at 14:29, limok said:

Just out of interest why would an HTML button cause this. the code was

 

<button ID="btnSubmit" runat="server" class="btn-primary btn" onServerClick="btnSubmit_Click">Submit</button>

changed to

<asp:Button ID="btnSubmit" runat="server" Text="Submit" CssClass="btn-primary btn" OnClick="btnSubmit_Click" /> 

the aspx page is inheriting a master page.

 

it was pure luck that this fixed. what kind of debugging/tools would you guys have done to find the culprit?

Expand  

Um, don't forget that if you have critical code in a Javascript button click event it will not execute if the user submits the form using the Enter key...

 

This topic is now closed to further replies.
  • Posts

    • Higher "Social Charges". I think that is a feel good phrase for 'Employee Deferred Compensation Charges". Then again, they use different wording in the "Old World." More precisely in American Accounting Lingo: "Mark to the Market Employee Differed Compensation Charges."
    • TikTok's "Add to Music App" feature gets support for another music streaming service by David Uzondu Image via Depositphotos.com TikTok's "Add to Music App" feature is getting support for another streaming service: YouTube Music. This comes a few months after the short-form video giant brought the feature to SoundCloud. "Add to Music App", if you have not heard of it, is a feature launched back in November 2023, initially for US and UK users, that makes saving music a lot easier. With this feature, users get an "Add Song" button next to the track name at the bottom of a TikTok video. When a customer first uses the feature, they can select their preferred streaming service from the available options, and this choice then becomes the default for all future one-tap saves (this can be changed anytime in the app's settings). The new YouTube Music integration means you can directly save the track to the streaming service with a single press. Users can also add a track from an artist's Sound Detail Page. When "Add to Music App" first appeared, the options were limited to Spotify and Amazon Music. Since then, TikTok has added Apple Music and Deezer. Each service gets a designated spot for the saved tracks, like Spotify's "Liked Songs" playlist. In YouTube Music's case, the song will land in a dedicated "TikTok Songs" playlist, so you do not have to go hunting for it later. TikTok claims that its "Add to Music App" function has resulted in over 1 billion saves globally since its wider rollout in 2024. On a related note, you probably are already aware of the current divest-or-ban issue TikTok's facing in the US. Howard Lutnick, the US Commerce secretary, recently stated in an interview with Fox News Sunday that President Trump loves the platform and sees it as "a good way to communicate with young people," but the platform's US operations must be handled by an American company, not Chinese.
    • 10 years of using end users as free-guineapigs to test half-baked garbage software...what an achievement 🎉
    • For sure! The first family computer when I was a kid didn't even have a hard drive, just dual 5.25" floppy driver. You would boot from the DOS disk in drive A, run your programs from drive B, and maybe sneak a data disk into drive A and just hope it wouldn't try to access something from the DOS folder while you did that. My dad added a 20 MB (yes, megabyte) hard drive to the computer, it was SUCH a quality-of-life improvement, lol!
    • The Settings app was first introduced in Windows 8 and Microsoft continued to add to the settings app in Windows 10.
  • Recent Achievements

    • Week One Done
      andeyhawk65 earned a badge
      Week One Done
    • First Post
      Jake2530 earned a badge
      First Post
    • Explorer
      Deranox went up a rank
      Explorer
    • Week One Done
      John Volks earned a badge
      Week One Done
    • One Month Later
      enric earned a badge
      One Month Later
  • Popular Contributors

    1. 1
      +primortal
      672
    2. 2
      ATLien_0
      251
    3. 3
      Xenon
      176
    4. 4
      neufuse
      138
    5. 5
      +FloatingFatMan
      102
  • Tell a friend

    Love Neowin? Tell a friend!