Error: The ConnectionString property has not been

Question

            string extension = Path.GetExtension(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + openFileDialog1.FileName + "'; Extended Properties=Excel 8.0;");

        using (System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection())
        {
            switch (extension)
            {
                case ".xls":
                    string xlsconStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + openFileDialog1.FileName + "'; Extended Properties=Excel 8.0;";
                    con.ConnectionString = xlsconStr;
                    break;

                case ".xlsx":
                case ".xlsm":
                    string xlsxconStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + openFileDialog1.FileName + "'; Extended Properties=Excel 12.0;";
                    con.ConnectionString = xlsxconStr;
                    break;
            }
                using (System.Data.OleDb.OleDbCommand oconn = new System.Data.OleDb.OleDbCommand("SELECT * FROM [" + listBox1.SelectedIndex.ToString() + "$]", con))
                {
                    // **There will be an error here**
                    // **The ConnectionString property has not been initialized.**
                    con.Open();

                    System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(oconn);
                    System.Data.DataTable data = new System.Data.DataTable();
                    adapter.Fill(data);
                    dataGridView1.DataSource = data;
                }
        }

What's wrong with the code? My purpose is to draw all database on selectedItem on listBox and whenever I click it will populate the dataGridView

BTW, the selected item was from the MSExcel worksheetName

Answer 1

Your code needs a very little fix:

string extension = Path.GetExtension(openFileDialog1.FileName);

Previously your GetExtension was trying to find an extension on your connection string, witch is impossible for the method, because is a connection string and not a path!

So your code fixed should look like this:

    string extension = Path.GetExtension(openFileDialog1.FileName);

    using (System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection())
    {
        switch (extension)
        {
            case ".xls":
                string xlsconStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + openFileDialog1.FileName + "'; Extended Properties=Excel 8.0;";
                con.ConnectionString = xlsconStr;
                break;

            case ".xlsx":
            case ".xlsm":
                string xlsxconStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + openFileDialog1.FileName + "'; Extended Properties=Excel 12.0;";
                con.ConnectionString = xlsxconStr;
                break;
        }
            using (System.Data.OleDb.OleDbCommand oconn = new System.Data.OleDb.OleDbCommand("SELECT * FROM [" + listBox1.SelectedIndex.ToString() + "$]", con))
            {
                con.Open();

                System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(oconn);
                System.Data.DataTable data = new System.Data.DataTable();
                adapter.Fill(data);
                dataGridView1.DataSource = data;
            }
    }

Now your code will enter the switch case statement in order to initialize you connection string ;)