CODEWITHSUNDEEP
c#excelvisual-studio-2010visual-studio
Bob T
Bob T

Reputation: 423

Update Excel file using C# and OLEDB - Network file issue

I don't have a lot of experience using C# to work with Excel. I've had to google a lot of the techniques but have had pretty good luck. What leads me here is a problem I'm having when trying to upgrade an Excel file on my company's network. I don't have any trouble upgrading an Excel file on a local drive but when it is directed to a file on the network it fails with "The Microsoft Jet database engine could not find the object '\\nas1\eng\legacy\XView_Results\Book1-xview-test.xlsx'. Make sure the object exists and that you spell its name and the path name correctly."

I've tried to change the path string to have four slashes and two slashes. "\\nas1\eng\legacy\XView_Results\Book1-xview-test.xlsx" but it fails, interpretted as: \\nas1\eng\legacy\XView_Results\Book1-xview-test.xlsx (Note: stackoverflow won't show my four & two slashes)

I've tried to use the C# technique with @ string FileName = @"\nas1\eng\legacy\XView_Results\Book1-xview-test.xlsx"; and changed the command to read: MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1;';"); But get this error: '\nas1\eng\legacy\XView_Results\Book1-xview-test.xlsx' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

Any ideas would be appreciated.

Here's the code for the routine that is failing:

  private void buttonSendToExcel_Click(object sender, EventArgs e)
    {
        //try
        //{
            string FileName = @"\\nas1\eng\legacy\XView_Results\Book1-xview-test.xlsx";
            System.Data.OleDb.OleDbConnection MyConnection;
            System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
            string sql = null;
            string sql2 = null;
            string sql3 = null;
            //MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='\\\\nas1\\eng\\legacy\\XView_Results\\Book1-xview-test.xlsx';Extended Properties='Excel 8.0;HDR=YES;IMEX=1;';");
            //MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\Book1-xview-test.xlsx';Extended Properties='Excel 8.0;HDR=YES;';");
            //MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='\\\nas1\\eng\\legacy\\XView_Results\\Book1-xview-test.xlsx';Extended Properties='Excel 8.0;HDR=YES;IMEX=1;';");
            MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1;';");
            MyConnection.Open();
            myCommand.Connection = MyConnection;

            sql = "Update [Sheet1$] set result=" + od + " where id=1";
            myCommand.CommandText = sql;
            myCommand.ExecuteNonQuery();

            sql2 = "Update [Sheet1$] set result=" + id + " where id=2";
            myCommand.CommandText = sql2;
            myCommand.ExecuteNonQuery();

            sql3 = "Update [Sheet1$] set result=" + yield + " where id=3";
            myCommand.CommandText = sql3;
            myCommand.ExecuteNonQuery();

            MyConnection.Close();
        //}
        //catch (Exception ex)
        //{
        //    MessageBox.Show(ex.ToString());
        //}
    }

Upvotes: 0

Views: 845

Answers (2)

Bob T
Bob T

Reputation: 423

There were two things that were changed in my code to solve my problem. 1. Switched path to mapped mode, i.e., \server\file mapped to H:\file 2. Removed the component IMEX=1; from MyConnection

Here's the fixed code:

        try
        {            
            //string FileName = @"\\tiwnas1\eng\legacy\XView_Results\Book1-xview-test.xlsx";
            //string theFile = GetPath(@"\\tiwnas1\eng\legacy\Book1-xview-test_v8.xls");
            //string FileName = @"\\tiwnas1\eng\legacy\Book1-xview-test_v8.xls";

            System.Data.OleDb.OleDbConnection MyConnection;
            System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
            string sql = null;
            string sql2 = null;
            string sql3 = null;
            //MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\csharp.net-informations.xls';Extended Properties='Excel 8.0;HDR=YES;IMEX=1;';");
            //MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='\\\\tiwnas1\\eng\\legacy\\XView_Results\\Book1-xview-test.xlsx';Extended Properties='Excel 8.0;HDR=YES;IMEX=1;';");
            //MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\Book1-xview-test.xlsx';Extended Properties='Excel 8.0;HDR=YES;';");
            //MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='\\tiwnas1\eng\legacy\XView_Results\Book1-xview-test.xlsx';Extended Properties='Excel 8.0;HDR=YES;IMEX=1;';");
            //MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='H:\\Book1-xview-test_v8.xls';Extended Properties='Excel 8.0;HDR=YES;IMEX=1;';");
            //MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='h:\\Book1-xview-test_v8.xls';Extended Properties='Excel 8.0;HDR=YES;IMEX=1;';");
            MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='h:\\Book1-xview-test_v8.xls';Extended Properties='Excel 8.0;HDR=YES;';");
            //MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + FileName + "';Extended Properties='Excel 8.0;HDR=YES;IMEX=1;';");
            MyConnection.Open();
            myCommand.Connection = MyConnection;

            //sql = "Update [Sheet1$] set result=5.625 where id=1";
            sql = "Update [Sheet1$] set result=" + od + " where id=1";
            myCommand.CommandText = sql;
            myCommand.ExecuteNonQuery();

            //sql2 = "Update [Sheet1$] set result=5.375 where id=2";
            sql2 = "Update [Sheet1$] set result=" + id + " where id=2";
            myCommand.CommandText = sql2;
            myCommand.ExecuteNonQuery();

            //sql3 = "Update [Sheet1$] set result=110000 where id=3";
            sql3 = "Update [Sheet1$] set result=" + yield + " where id=3";
            myCommand.CommandText = sql3;
            myCommand.ExecuteNonQuery();

            MyConnection.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }

Upvotes: 0

granadaCoder
granadaCoder

Reputation: 27852

Try a super slashed up path.

"\\\\Server\\MyNetworkDrive\\FolderOne\\FolderTwo\\MySuperCoolFile.xlsx"


@"\nas1\eng\legacy\XView_Results\Book1-xview-test.xlsx";

SHould that be?

@"\\nas1\eng\legacy\XView_Results\Book1-xview-test.xlsx";

Other Idea.

Put single quotes around the file name. I actually put single quotes around every "value"..to be consistent.

example:

(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + FileName + "';Extended Properties='Excel 8.0;HDR=YES;IMEX=1;';");

Note the single quote include to wrap the value of "FileName".

Upvotes: 0

Related Questions