OleDbConnection Source Variable in C#

Question

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

whats the exact error and where you are getting at the Connection String — Sai Kalyan Kumar Akshinthala
– Sai Kalyan Kumar Akshinthala, Commented Apr 25, 2011 at 6:00

Tieson T. · Accepted Answer · 2011-04-25 06:00:28Z

3

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

answered Apr 25, 2011 at 6:00

Tieson T.

21.2k6 gold badges83 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tieson T. Over a year ago

NP. You have to remember the C# doesn't do automatic variable substitution, unlike, say, PHP.

Collectives™ on Stack Overflow

OleDbConnection Source Variable in C#

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related