c# ExecuteNonQuery需要一个打开的可用连接.连接的当前状态是关闭的

本文关键字：连接 状态 ExecuteNonQuery 一个 | 更新日期: 2023-09-27 18:12:47

有人能找到为什么我得到这个错误?我已经标记了我得到错误的地方

public string ExportRecords(string query, string sheetname)
    {
        string filename = "";
        DataSet ds = new DataSet("New_DataSet");
        DataTable dt = new DataTable(sheetname);
        ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
        dt.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["KMFConnectionString"].ToString());
        con.Open();
        string sql = query;
        SqlCommand cmd = new SqlCommand(sql, con);
        SqlDataAdapter adptr = new SqlDataAdapter();
        adptr.SelectCommand = cmd;
        adptr.Fill(dt);
        con.Close();
        ds.Tables.Add(dt);
        string connstr = connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + orgrepository.OrganizationMetaValueByKey("KMFFileDownloadPath") + filename + "; Extended Properties=Excel 8.0";

        OleDbConnection connection = new OleDbConnection(connstr);

        using (OleDbCommand commands = connection.CreateCommand())
        {
            commands.CommandText = "CREATE TABLE [Sheet20] (F1 number, F2 char(255), F3 char(128))";
            commands.ExecuteNonQuery();     ****getting error here****
            for (int i = 1; i <= 20; i++)
            {
                commands.CommandText = "INSERT INTO [Sheet20] (F1, F2, F3) VALUES(1,'"Fake Record'",'"Fake Record'")";
                commands.ExecuteNonQuery();
            }
        }
        if (dt.Rows.Count > 0)
        {
            //filename = sheetname + DateTime.Today.Day.ToString() + DateTime.Today.Month.ToString() + DateTime.Today.Year.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString() + ".xlsx";
            filename = sheetname + "-output" + ".xls";                
            ExcelLibrary.DataSetHelper.CreateWorkbook(orgrepository.OrganizationMetaValueByKey("KMFFileDownloadPath") + filename, ds);

            //    connection.Close();
       }
        return filename;
    }

c# ExecuteNonQuery需要一个打开的可用连接.连接的当前状态是关闭的

您需要像这样打开和关闭连接

 using (OleDbConnection connection = new OleDbConnection(connstr))
 {
     connection.Open();
     using (OleDbCommand commands = connection.CreateCommand())
     {
         //snip
     }
 }

您还没有调用connection.Open()来打开您试图使用的会话

您的连接已关闭，请尝试打开命令并处理错误。

using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
        // The insertSQL string contains a SQL statement that
        // inserts a new row in the source table.
        OleDbCommand command = new OleDbCommand(insertSQL);
        // Set the Connection to the new OleDbConnection.
        command.Connection = connection;
        // Open the connection and execute the insert command.
        try
        {
            connection.Open();
            command.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        // The connection is automatically closed when the
        // code exits the using block.
    }
相关文章：