• 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.

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. 

 

44 minutes ago, DevTech said:

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

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
3 minutes ago, 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. 

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
5 minutes ago, 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)

 

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

  • 0
8 minutes ago, 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. 

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
45 minutes ago, 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?

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,

 

27 minutes ago, 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.

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

 

55 minutes ago, 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. 

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
9 minutes ago, 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.

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
2 minutes ago, 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.

 

 

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
1 minute ago, 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.

 

 

thanks - will take a look 

  • 0
15 minutes ago, limok said:

thanks - will take a look 

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
3 minutes ago, BinaryData said:

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

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 ‎4‎/‎19‎/‎2016 at 2:30 PM, 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

 

 

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
52 minutes ago, 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

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
5 hours ago, 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?

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

    • Poll: Grand Theft Auto VI price predictions, cast your vote by Pulasthi Ariyasinghe After years of waiting, Rockstar will be solidifying the launch date of Grand Theft Auto VI with the launch of pre-orders next week. While the studio has confirmed a date for this occasion, it is yet to attach a price to the highly anticipated game. So let's see what our readers think it will cost at launch. The Grand Theft Auto VI pre-orders will kick off on June 25 for digital and physical editions. Unless some last-minute changes happen, the release date will be November 19, 2026, across Xbox Series X|S and PlayStation 5. Unfortunately, there's still no information about a PC version from the developer or the publisher Take-Two. Now the question becomes, how much will Grand Theft Auto VI cost at launch? The game is predicted by some analysts to be the biggest launch of an entertainment product ever. With the amount of hype that has been built behind it and with ballooning development costs, Take-Two may price this Grand Theft Auto entry differently from other AAA titles. The current price of a AAA game is $69.99. That norm almost rose to $79.99 before calming down. But with such a massive release, Grand Theft Auto VI may be the game that pushes the boundary again. It's also possible that Take-Two keeps the price relatively low to increase the number of players that jump in early and keep them hooked on Grand Theft Auto Online to spend on microtransactions for years to come. Keep in mind that the below poll is asking for a prediction of the standard edition price, not a deluxe or any other special edition that Take-Two will introduce for additional benefits. Also, there is also the chance of the company splitting up the campaign and online portions. If you think that will happen, put your vote on what you think will be the total cost of the two. Poll Poll: How much will Grand Theft Auto VI cost? $59.99 $69.99 $79.99 $89.99 $100 or more Submit Vote If you have a very specific prediction in mind, sound off in the comments below.
    • Would you please fix your graphics. They are outdated and don't fit the article.
    • The Light of Life? We actually do glow till our Death, study finds by Sayan Sen Image by Rafael Rendon via Pexels A study by researchers at the University of Calgary has found that living organisms produce an extremely faint light known as ultraweak photon emission, and that this glow appears to drop significantly after death. The research was published in the Journal of Physical Chemistry in April 2025 and quickly drew widespread attention, leading to more than 200 news stories about the findings. Ultraweak photon emission (or UPE), sometimes called biophoton emission, refers to tiny amounts of light released by living cells as a result of normal biological activity. A photon is the basic particle of light, and researchers say every living system examined so far, including plants and animals, has been found to emit these photons. The glow is far too faint to be seen by the human eye. “I suppose it has a little to do with people being reminded of auras,” says Dr. Christoph Simon, PhD, one of the authors of the study and a professor in the Department of Physics and Astronomy in the Faculty of Science. “It is a fact that living beings glow. It’s a very weak glow, but it’s there and visible with very sensitive cameras.” According to the study, the light involved is extremely weak, ranging from 10 to 1,000 photons per square centimetre per second across a spectral range of 200 to 1,000 nanometres. For comparison, a nanometre is one-billionth of a metre and is commonly used to measure wavelengths of light. Detecting emissions at such low levels requires highly specialized equipment. To study the phenomenon, researchers used electron-multiplying charge-coupled device (EMCCD) and charge-coupled device (CCD) cameras. These imaging systems are designed to detect extremely small amounts of light, including individual photons, while minimizing background noise. The technology allowed researchers to capture signals that would otherwise be impossible to observe. The team worked with the Human Health Therapeutics Research Centre at the National Research Council of Canada (NRC) in Ottawa to examine photon emissions in mice. Researchers took two-hour exposure images of the animals before and after death and compared the results. “We saw that the level of light that they emit – this biophoton glow – is distinctly different between living and dead animals,” says Dr. Daniel Oblak, PhD, an associate professor in Physics and Astronomy and the corresponding author of the study. The images showed a clear decrease in photon emissions after death across the entire body of each mouse. According to the researchers, this provided direct evidence that living and dead tissue produce different levels of ultraweak photon emission. “It’s a very small amount and it’s, of course, very tricky to detect,” Oblak says. The study grew out of discussions between Simon, whose research interests include quantum biology, and Oblak, whose work focuses on detecting light for quantum communication experiments. Quantum biology is a field that explores whether processes described by quantum physics, which studies matter and energy at very small scales, may also play a role in living systems. “Since I work as a quantum physicist on light detection for quantum communication, I thought that experimentally we have a lot of the tools to be able to detect the light,” Oblak explains. The researchers also investigated UPE in plants and found that the light changed in response to stress. When plants were exposed to higher temperatures or physically injured, their photon emissions increased. Chemical treatments also affected the glow. Among the substances tested, the local anesthetic benzocaine produced the strongest emission response when applied to injured plant tissue. These findings suggest that ultraweak photon emission is closely linked to biochemical and metabolic activity inside living organisms. Metabolism refers to the chemical reactions that allow cells and organisms to stay alive and function. Because these reactions change when an organism experiences stress, injury or disease, researchers believe UPE may provide a way to monitor those changes. The researchers stress that the glow is a physical and biological phenomenon, not a metaphysical one. Oblak says more research is needed to understand exactly how the light is produced and what information it may reveal about the condition of living tissue. “We must understand what that is to figure out what’s happening,” he says. “If we can understand how that relates to certain influences on the body – stress, diseases – then that could be used as a diagnostic tool.” The researchers believe the technique could eventually help scientists study health and disease without invasive procedures. Because UPE can be measured without adding dyes, markers or labels, it may offer a way to monitor whether tissue is healthy, damaged or alive. In plants, it could help researchers better understand how organisms respond to injury, heat and other forms of stress. While the work is still in its early stages, the study demonstrates that ultraweak photon emission imaging can provide a non-invasive and label-free way to observe biological activity. Researchers say the approach could become a useful tool for studying vitality, stress responses and other important processes in both animals and plants. Source: University of Calgary, ACS publication This article was generated with some help from AI and reviewed by an editor. Under Section 107 of the Copyright Act 1976, this material is used for the purpose of news reporting. Fair use is a use permitted by copyright statute that might otherwise be infringing.
    • Damn, I loved this show back in the day.  
  • Recent Achievements

    • One Year In
      hhgygy earned a badge
      One Year In
    • One Month Later
      AMV earned a badge
      One Month Later
    • Week One Done
      AMV earned a badge
      Week One Done
    • Collaborator
      ryansurfer98 went up a rank
      Collaborator
    • One Month Later
      Eurosoft10 earned a badge
      One Month Later
  • Popular Contributors

    1. 1
      +primortal
      512
    2. 2
      +Edouard
      171
    3. 3
      PsYcHoKiLLa
      82
    4. 4
      Steven P.
      74
    5. 5
      Michael Scrip
      72
  • Tell a friend

    Love Neowin? Tell a friend!