Error message with C# APP

Hello Experts,

I am geting the following error message when trying to run an SQL stored procedure (C# 2005) which ultimately opens up a Crystal report in my C# Application...

Can anyone see what is going on here?

Thanks!

Error MMessage Text:
:  Unhandled exception has occurred in your application.  If you click Continue, then application will ignore this error and attempt to continue. If you click quit, the application will close immediately.

This SqlTransaction has been completed and is no longer usable

Details (button)       Continue(button)     Quit (button)

Details:

See the end of this message for details on invoking just-in-time (JIT) debugging instead of this dialog box.

*******Exception Test*********

System Invalid Operation Exception. This SqlTransaction has completed and is no longer usable.

     At System.Data SqlClient. Sql Transaction.Zombie Check()
     At System.Data SqlClient. Sql Transaction.Rollack()
     At E-Harvest_Groundfish.Tripinfo Form.RunXMLUpdateExport Corrections
TALLSPROC()
    At E-Harvest_Groundfish.Tripinfo Form.LogReport()
    At E-Harvest_Groundfish.Tripinfo Form.EvaluateStatus()
    At E-Harvest_Groundfish.Tripinfo Form.InitializeLog()
    At E-Harvest_Groundfish.Tripinfo Form.lbFAreaSort.SelectIndexChanged(Objectsender, Event Args e)
    At System.Windows.Forms.ListBox.OnSelected Index Changed(Event Args e)
    At System.Windows.Forms.ListBox.WmReflectComamnd (Message & m)  
    At System.Windows.Forms.ListBox.Wind Proc(Message & m)
    At System.Windows.Forms.Control.ControlNative Window.On Message(Message & m)
    At System.Windows.Forms.Control.Wnd Proc (Message & m)
    At System.Windows.Forms.NativeWindow.Callback (IntPtrnWnd,IW32msg, IntPtrwparam, IntPtrIparam)

**********Loaded Assemblies*********
SaxitalisAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lee SavidgeCommented:
Looks as if the sp has a problem. Post the sp here. What happens if you run the sp manually with the same parameters? Also, can you output the sql command passed to SQL? Either output it in the application or run SQL profiler to see what it is receiving. Then try running the command passed to SQL.

Lee
0
SriVaddadiCommented:
This is your error

"This SqlTransaction has completed and is no longer usable"

Check how you are using SqlTransaction in your code. You might be trying to use it even after its scope or trying commit or rollback a SqlTransaction which never existed.
0
SaxitalisAuthor Commented:
This application is installed on several machines and has been running for quite some time. This unhandled exception just happened on one of the machines.

SO... on the machine this is hapening on... When I restore from a backup on a different machine with the same application file it works fine.

I do have a transaction roll back statement in my catch blocks but that should never be run here since this exception is unhandled.

The only difference between the two machines are OS and SQL Server SErvice Packs.

The one that works: Windows 7 and SQL 2005 SP3
The one that throws  Exception: Windows XP and no SSQL 2005 SP 3

Thanks!
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

OrcbighterCommented:
SriVaddadi is correct.
Your problem is that you are trying to use the SQLTransaction object after it has been closed (through error or completion?)
Without seeing how you use it in the code, further explanation is somewhat problematical.
If you can, you should post a code snippet up here for us to review.
cheers

0
SaxitalisAuthor Commented:
Here is my code...

Does this make sense?

Thanks!
private void RunMySPROC()
        {
            string lastDay = txtDCPLDate.Text.Trim();
            //access sproc and pass date as parameter
            string ConStr = LoginFirstSQLClient.Connection;
            SqlConnection Conn = new SqlConnection(ConStr);
            Conn.Open();
            SqlTransaction trans = Conn.BeginTransaction();
            try
            {

                SqlCommand cmd = new SqlCommand("spXMLUpdateExportCorrectionsTALL", Conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Transaction = trans;
                //send params in
                cmd.Parameters.Add("@ReportDate", SqlDbType.DateTime).Value = lastDay.Trim();
                
                cmd.ExecuteNonQuery();
                trans.Commit();
                Conn.Close();

                lastDay = "";
                fArea = "";


            }
            catch (DBConcurrencyException ex2)
            {
                trans.Rollback();
                Conn.Close();
                Conn.Dispose();
                //message here
                
            }//end catchDBConcurrencyException ex2)
            catch (DataException ex2)
            {
                trans.Rollback();
                Conn.Close();
                Conn.Dispose();
		    //message here
                
            }//end catch(DataException ex2)
            catch
            {
                trans.Rollback();
                Conn.Close();
                Conn.Dispose();
                //message here
            }//end last catch
            finally
            {
                //Close and Dispose called explicitly inside the "finally" block here
                //trans.Commit();
                Conn.Close();
                Conn.Dispose();
            }//end finally

Open in new window

0
SriVaddadiCommented:
Transaction is associated with connection. Closing the connection, your transaction object would no more be there.

You dont need to close the connection in each and every catch block. Finally block executes regardless of whether an exception occurs or not.

so having it in finally is sufficient.  Below code should resolve your issue. one more thing is you missed to set the connection property of Command object


private void RunMySPROC()
        {
            string lastDay = txtDCPLDate.Text.Trim();
            //access sproc and pass date as parameter
            string ConStr = LoginFirstSQLClient.Connection;
            SqlConnection Conn = new SqlConnection(ConStr);
            SqlTransaction trans =  null;
            SqlCommand cmd =  null;
            try
            {

                SqlCommand cmd = new SqlCommand("spXMLUpdateExportCorrectionsTALL", Conn);
                cmd.CommandType = CommandType.StoredProcedure;
                conn.Open();
                trans = Conn.BeginTransaction();
               cmd.Connection = conn;
                cmd.Transaction = trans;
                //send params in
                cmd.Parameters.Add("@ReportDate", SqlDbType.DateTime).Value = lastDay.Trim();
                cmd.ExecuteNonQuery();
                trans.Commit();
                Conn.Close();

                lastDay = "";
                fArea = "";
      

            }
            catch (DBConcurrencyException ex2)
            {
                trans.Rollback();
      //Conn.Close();
          //Conn.Dispose();
                //message here
               
            }//end catchDBConcurrencyException ex2)
            catch (DataException ex2)
            {
                trans.Rollback();
                //Conn.Close();
                //Conn.Dispose();
                //message here
               
            }//end catch(DataException ex2)
            catch
            {
                trans.Rollback();
                //Conn.Close();
                //Conn.Dispose();
                //message here
            }//end last catch
            finally
            {
                //Close and Dispose called explicitly inside the "finally" block here
                //trans.Commit();
                Conn.Close();
                Conn.Dispose();
                conn = null;
            }//end finally
0
SaxitalisAuthor Commented:
Hello Moderators,

I have been traveling with no Internet access... I just resolved this question. Is it possible for you to grant me access to ask new questions as son as possible?

Thanks,
Saxitalis (John Porter)
0
SriVaddadiCommented:
Hi John, You requested the question be closed by accepting my solution. I am wondering why i am getting no points though.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SaxitalisAuthor Commented:
are therte points now?
0
SriVaddadiCommented:
Yep. Thanks John. Appreciate it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.