Question SQL transaction Error

[hewstone]

I am receiving this error message when running the code below

'The transaction associated with the current connection has completed but has not been disposed. The transaction must be disposed before the connection can be used to execute SQL statements.' occurred when attempting to 'Execute query'

Any idea why I get this error?

#region Export_Admissions

    [Promote(PromotionTargets.All)]
    [Category("Export")]
    public static void Export_TONAdmissions(EXPORT_TON_Admissions1 myData,TON_Export_Import.Declarations.TON_Export_ImportData LocalData)
    {
    try{
        
        
        object missing = System.Reflection.Missing.Value;
        object Visible=true;
        object start1 = 0;
        object end1 = 0;
        
        
        string template = @"C:\BPMv9 MailMerge Templates\TON Main Export Template.xlsx";
         Application Excel = new ApplicationClass();
        Workbook wb = Excel.Workbooks.Open(template,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing);
        Microsoft.Office.Interop.Excel.Worksheet mySheet = (Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets["TON_Admissions"];
                    
        int row;
        int col;
        row = 7;
        col = 1;
        
        myData.Dispose();
        DataSet ds = myData.Read();
        
        
        foreach (DataRow theRow in ds.Tables[0].Rows)
        {
                            
            mySheet.Cells[row,1] = theRow["ID"];
            mySheet.Cells[row,2] = theRow["CourseDataID"];
            mySheet.Cells[row,3] = theRow["CourseID"];
            mySheet.Cells[row,4] = theRow["Code"];
            /*mySheet.Cells[row,5] = theRow["Title"];
            mySheet.Cells[row,6] = theRow["Year"];
            mySheet.Cells[row,7] = theRow["StTypeID"];
            mySheet.Cells[row,8] = theRow["StType"];
            mySheet.Cells[row,9] = theRow["ProposedTarget1"];
            mySheet.Cells[row,10] = theRow["ProposedTarget2"];
            mySheet.Cells[row,11] = theRow["ProposedTarget3"];
            mySheet.Cells[row,12] = theRow["ActualIntake1"];
            mySheet.Cells[row,13] = theRow["ActualIntake2"]; 
            mySheet.Cells[row,14] = theRow["ActualIntake3"];
            mySheet.Cells[row,15] = theRow["PreviousTarget1"];
            mySheet.Cells[row,16] = theRow["PreviousTarget2"];
            mySheet.Cells[row,17] = theRow["PreviousTarget3"];
            mySheet.Cells[row,18] = theRow["In_Year_Offers"];
            mySheet.Cells[row,19] = theRow["Intake_Total"];
            mySheet.Cells[row,20] = theRow["Intake_Clearing"];
            mySheet.Cells[row,21] = theRow["Intake_Deffered"];
            mySheet.Cells[row,22] = theRow["Intake_DefferedExpected"];*/        
                    
            row++;
        }
        
    
        
        row = row + 2;
        //mySheet._Protect("ask4report",missing,missing,missing,missing);
        Excel.Visible = false;
        
        string SaveDir;
        SaveDir = LocalData.txtFolderName; 
                
        wb.SaveCopyAs(@"C:\BPMv9 MailMerge Documents\TON\Main Export\" + SaveDir + "_Export.xlsx");
        
        
        
        System.Threading.Thread.Sleep(30000);
        myData.Dispose();
        Excel.Quit();
        System.GC.Collect();
        
                foreach (Process process in Process.GetProcessesByName("Excel"))

           {

               process.Kill();

           }
    }
    catch {
        //System.Threading.Thread.Sleep(3000);
        foreach (Process process in Process.GetProcessesByName("Excel"))

           {

               process.Kill();

           }}

        
    }
   
    
    #endregion Export_Admissions

 

[jmcilhinney]

You haven't shown us any code that relates to transactions so I'm not sure how you expect us to give any specific advice. Generally, if you are using a SqlTransaction then you need to call either Commit or RollBack once you're done moving data. It makes no sense not to do one of those two things.

Also, what's up with this?

        myData.Dispose();
        DataSet ds = myData.Read();

You dispose an object and then immediately call a method on it. Disposing an object should the last thing you do with it when you don't need it any more so that code is just wrong.