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?
 
SriVaddadiCommented:
Hi John, You requested the question be closed by accepting my solution. I am wondering why i am getting no points though.
0
 
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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
 
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
 
SaxitalisAuthor Commented:
are therte points now?
0
 
SriVaddadiCommented:
Yep. Thanks John. Appreciate it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.