Click here to Skip to main content
15,885,919 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Error:=Exception Details: System.Data.OleDb.OleDbException: Unexpected error from external database driver ().



My following Code:-
C#
public ActionResult ExportToExcel(int MID)

{

        MModule objModule = new MModule();
        objModule.LoadModule(MID);
        DatabaseObject objData = new DatabaseObject();
        string file = DateTime.Now.Ticks + "_" + objModule.Title + ".xlsx";
        System.IO.File.Copy(Server.MapPath("/Excel/Export/Atemplate.xlsx"), Server.MapPath("/Excel/Export/" + file));
        string filename = Server.MapPath("/Excel/Export/" + file);
        objData.Query = "SELECT * FROM " + objModule.TableName + "";
        DataTable dtReport = objData.GetTable();

        System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Mode=ReadWrite;Extended Properties=\"Excel 12.0 Xml;Readonly=False;IMEX=0;\"");

        con.Open();
        System.Data.OleDb.OleDbCommand cmd = con.CreateCommand();
        string tableString = "";
        string ColString = "";
        for (int i = 0; i < dtReport.Columns.Count; i++)
        {
            tableString += "[" + dtReport.Columns[i].Caption + "] varchar(255),";
            ColString += "[" + dtReport.Columns[i].Caption + "],";

        }
        tableString = tableString.Substring(0, tableString.Length - 1);
        try
        {
            ColString = ColString.Substring(0, ColString.Length - 1);

            //***************** Create Excel Sheet Table *********************//

            cmd.CommandText = "CREATE TABLE [" + objModule.Title + "] ( " + tableString + ");";
            cmd.ExecuteNonQuery();



            string ValString = "";
            for (int j = 0; j < dtReport.Rows.Count; j++)
            {
                ValString = "";
                for (int i = 0; i < dtReport.Columns.Count; i++)
                {
                    ValString += "'" + dtReport.Rows[j][i] + "',";

                }
                ValString = ValString.Substring(0, ValString.Length - 1);
                cmd.CommandText = "Insert Into [" + objModule.Title + "] (" + ColString + ") Values (" + ValString + ");";

                cmd.ExecuteNonQuery();
            }
            cmd.Dispose();
            con.Dispose();
            con.Close();




            Microsoft.Office.Interop.Excel.Application excelApplication = new Microsoft.Office.Interop.Excel.Application();


            Microsoft.Office.Interop.Excel.Workbook workbook = excelApplication.Workbooks.Open(filename,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, Type.Missing);


            foreach (Excel.Worksheet wrkst1 in workbook.Worksheets)
            {

                Excel.Range usedrange = wrkst1.UsedRange;
                usedrange.Columns.AutoFit();
                wrkst1.Tab.Color = 255;


                for (int i = 0; i < dtReport.Rows.Count; i++)
                {
                    for (int j = 0; j < dtReport.Columns.Count; j++)
                    {
                        Microsoft.Office.Interop.Excel.Range range = wrkst1.Cells[i + 2, j + 1];
                        range.Interior.Color = System.Drawing.
                        ColorTranslator.ToOle(System.Drawing.Color.YellowGreen);
                    }
                }
                Excel.Range rng1 = (Excel.Range)wrkst1.Rows[1];
                rng1.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);


                Excel.Range rng = (Excel.Range)wrkst1.Cells[1, 1];
                Excel.Range row = rng.EntireRow;
                row.Insert(Excel.XlInsertShiftDirection.xlShiftDown, false);


                Excel.Range chartRange;
                chartRange = wrkst1.get_Range("E1");
                chartRange.FormulaR1C1 = objModule.Title;
                chartRange.HorizontalAlignment = 3;
                chartRange.VerticalAlignment = 3;
                chartRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green);
                chartRange.Font.Size = 20;
            }


            excelApplication.DisplayAlerts = false;
            ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]).Delete();
            workbook.Close(true, filename, null);
            excelApplication.DisplayAlerts = true;


        }

        catch (Exception ex)
        {
        }


        //return File(filename, "application/msexcel");
        return File(filename, "application/x-excel", DateTime.Now.Ticks + "_Report.xlsx");
    }
Posted
Updated 24-Nov-14 20:27pm
v2
Comments
Nelek 25-Nov-14 3:39am    
Have you tried with the debugger? Which line is giving problems?
Nishant.Chauhan80 25-Nov-14 4:19am    
System.Data.OleDb.OleDbCommand cmd = con.CreateCommand();

1 solution

Instead of
C#
System.Data.OleDb.OleDbCommand cmd = con.CreateCommand();

use:
C#
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();


See: OleDbCommand Class[^]
 
Share this answer
 
Comments
Nishant.Chauhan80 26-Nov-14 0:07am    
Actually Sir.. Error Found after Con.Open(); Line

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900