CODEWITHSUNDEEP
c#sqloledbc#-2.0
Nano HE
Nano HE

Reputation: 1971

OleDbConnection Source Variable in C#

How can I replace D:\temp\test.xls with filePath in OleDbConnection statement.

I can get the exactly filePath (with OpenFileDialog, then I can located my .xls file conveniently, no more hardcoded), but when I insert the variable filePath as Style2, it doesn't work. How can I fix this ? Thanks.

Style1

OleDbConnection dbConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\temp\test.xls;Extended Properties=""Excel 8.0;HDR=Yes;""");

Style2

OleDbConnection dbConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=filePath;Extended Properties=""Excel 8.0;HDR=Yes;""");

[Updated] Some part of my code as this,

DataTable fooData = new DataTable();

            OleDbConnection dbConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=filePath;Extended Properties=""Excel 8.0;HDR=Yes;""");

            dbConnection.Open ();
            try
            {
                OleDbCommand dbCommand = new OleDbCommand("SELECT * FROM [maleSheet$]", dbConnection);
                OleDbDataReader dbReader = dbCommand.ExecuteReader();

                int RankIndex = dbReader.GetOrdinal("Rank");

                while (dbReader.Read())
                {
                    string rank = dbReader.GetValue(RankIndex).ToString();
                    ////....
                }
           }

Error as below at line OleDbDataReader dbReader = dbCommand.ExecuteReader();

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Upvotes: 0

Views: 1503

Answers (1)

Tieson T.
Tieson T.

Reputation: 21191

OleDbConnection dbConnection = new OleDbConnection( String.Format( @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=Yes;""", filePath ) );

Upvotes: 3

Related Questions