getoledbschematable requires an open and available connection.The connection's current state is closed

Question

My code below gives me the following error as i typed in the title

What am I doing wrong here?

OpenFileDialog openFileDialog1 = new OpenFileDialog();

if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
    string huru = openFileDialog1.FileName;
    this.textBox1.Text = huru;
    string pathConn;
    OleDbConnection conn;
    DataTable spreadSheetData;
    string sheetName = "";
    OleDbCommand onlineConnection;
    OleDbDataAdapter myDataAdapter;
    DataTable dt = new DataTable();

    if (huru.Substring(huru.Length - 3) == "lsx")
    {

        pathConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + huru
            + ";Extended Properties = \"Excel 12.0 Xml;HDR=YES\"; ";
    }
    else
    {
        pathConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + huru
            + ";Extended Properties=\"Excel 8.0;HDR=yes;\";";
    }
    conn = new OleDbConnection(pathConn);
    spreadSheetData = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    foreach (DataRow dr in spreadSheetData.Rows)
    {
        sheetName = dr["TABLE_NAME"].ToString();
        sheetName = sheetName.Replace("'", "");
        if (sheetName.EndsWith("$"))
        {
            onlineConnection = new OleDbCommand("SELECT * FROM [" + sheetName + "]", conn);
            myDataAdapter = new OleDbDataAdapter(onlineConnection);
            dt = new DataTable();
            dt.TableName = sheetName;
            myDataAdapter.Fill(dt);
            ds.Tables.Add(dt);
        }
    }
}

spreadSheetData starts falling null

my codes refer to Excel to DataGridView 1st answered by JohnG

and this video https://www.youtube.com/watch?v=CfNMPDJVjPI

Thanks for any help!

You should describe what are you trying to do with your code, insert the errors as text, not as links to images (there's many reasons for that) and also show us some debugging efforts of your own — YakovL, Nov 02 '18 at 13:35

score 0 · Answer 1 · answered Nov 02 '18 at 12:48

Your code should be something like this: I have encapsulated your connection inside a using, so we are sure freeing resources. Also it's necessary to open the connection.

 OpenFileDialog openFileDialog1 = new OpenFileDialog();
        if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
        {
            //Prepare things

            using(OleDbConnection conn = new OleDbConnection(pathConn))
            {
                conn.Open(); //Added this line
                spreadSheetData = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                foreach (DataRow dr in spreadSheetData.Rows)
                {
                      //Do staff
                }
            }
        }

score 0 · Answer 2 · answered Nov 02 '18 at 12:55

You must open your connection before using it :

conn = new OleDbConnection(pathConn);
conn.Open();

You should also use the "Using" statement to properly dispose the connection when not used anymore.

OpenFileDialog openFileDialog1 = new OpenFileDialog();
            if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                string huru = openFileDialog1.FileName;
                this.textBox1.Text = huru;
                string pathConn;
                //OleDbConnection conn;
                DataTable spreadSheetData;
                string sheetName = "";
                OleDbCommand onlineConnection;
                OleDbDataAdapter myDataAdapter;
                DataTable dt = new DataTable();

                if (huru.Substring(huru.Length - 3) == "lsx")
                {

                    pathConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + huru
                        + ";Extended Properties = \"Excel 12.0 Xml;HDR=YES\"; ";
                }
                else
                {
                    pathConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + huru
                        + ";Extended Properties=\"Excel 8.0;HDR=yes;\";";
                }

                using(OleDbConnection conn = ew OleDbConnection(pathConn))
                {
                    //conn = new OleDbConnection(pathConn);
                    conn.Open();
                    spreadSheetData = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    foreach (DataRow dr in spreadSheetData.Rows)
                    {
                        sheetName = dr["TABLE_NAME"].ToString();
                        sheetName = sheetName.Replace("'", "");
                        if (sheetName.EndsWith("$"))
                        {
                            onlineConnection = new OleDbCommand("SELECT * FROM [" + sheetName + "]", conn);
                            myDataAdapter = new OleDbDataAdapter(onlineConnection);
                            dt = new DataTable();
                            dt.TableName = sheetName;
                            myDataAdapter.Fill(dt);
                            ds.Tables.Add(dt);
                        }
                    }
                }
            }

it said, "System.Data.OleDb.OleDbException: 'External table is not in the expected format.' " refer to myDataAdapter.Fill(dt) why is that? — Shakatu, Nov 02 '18 at 12:57
Refer to https://stackoverflow.com/questions/1139390/excel-external-table-is-not-in-the-expected-format — KMarron, Nov 02 '18 at 13:00

getoledbschematable requires an open and available connection.The connection's current state is closed

2 Answers2