不允许更改'connectionstring'属性。连接的当前状态是打开的。 [英] Not allowed to change the 'connectionstring' property. The connection's current state is open.

问题描述

大家好，



i将我的值检索到它成功检索的访问数据库中但是我想如果值不存在于数据库中我的文本框自动为此获取空白我写了代码，但它给了我不允许更改ConnectionString属性。连接当前状态是打开的。异常如果我把else条件然后只有这个异常

否则代码运行良好为什么会发生这种情况？我在这里粘贴我的检索代码



我尝试过：

hi all,

i tring to retrieve my value into access database it retrieve successfully also but i want if value not exist into database my textbox automatically get blank for this i written code but its giving me Not allowed to change the ConnectionString property. The connections current state is open. exception if i put else condition then only this exception
coming otherwise code is running fine why this happening ?? im pasting my retrieving code here

What I have tried:

private void txtformno_TextChanged(object sender, EventArgs e)
{
    string connetionString = null;
    connetionString = ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;
    cnn.ConnectionString = connetionString;
    cnn.Open();

    string sql = "select * from Billing where FormNo=" + txtformno.Text+"";
    cmd = new OleDbCommand(sql, cnn);
    OleDbDataReader dr = cmd.ExecuteReader();

    if (dr.HasRows == true)
    {
        while (dr.Read())
        {

            txtformno.Text = dr.GetValue(0).ToString();
            txtdate.Text = dr.GetValue(1).ToString();
            txttruck.Text = dr.GetValue(2).ToString();
            txtofficecode.Text = dr.GetValue(3).ToString();
            txtpartycode.Text = dr.GetValue(4).ToString();
            txtpartycode1.Text = dr.GetValue(5).ToString();
            txtlocation.Text = dr.GetValue(6).ToString();
            txtsupplier.Text = dr.GetValue(7).ToString();
            txtitem.Text = dr.GetValue(8).ToString();
            txtinvoice.Text = dr.GetValue(9).ToString();
            txtmaskinvoice.Text = dr.GetValue(10).ToString();
            txtpackage.Text = dr.GetValue(11).ToString();
            txtwieght.Text = dr.GetValue(12).ToString();
            txtinvestamount.Text = dr.GetValue(13).ToString();
            txtpercentage.Text = dr.GetValue(14).ToString();
            txtamount.Text = dr.GetValue(15).ToString();
            txtpaymentamount.Text = dr.GetValue(16).ToString();
            txtpaymenttype.Text = dr.GetValue(17).ToString();
            txtbankname.Text = dr.GetValue(18).ToString();
            txtchequeno.Text = dr.GetValue(19).ToString();
            txtmaskchequedate.Text = dr.GetValue(20).ToString();
            txtbillno.Text = dr.GetValue(21).ToString();
            txtmaskbilldate.Text = dr.GetValue(22).ToString();


        }
        dr.Close();

    }

    else   // exeception coming if i add this code (else code)
    {
        txtformno.Text = "";
        txtdate.Text = "";
        txttruck.Text = "";
        txtofficecode.Text = "";
        txtpartycode.Text = "";
        txtpartycode1.Text = "";
        txtlocation.Text = "";
        txtsupplier.Text = "";
        txtitem.Text = "";
        txtinvoice.Text = "";
        txtmaskinvoice.Text = "";
        txtpackage.Text = "";
        txtwieght.Text = "";
        txtinvestamount.Text = "";
        txtpercentage.Text = "";
        txtamount.Text = "";
        txtpaymentamount.Text = "";
        txtpaymenttype.Text = "";
        txtbankname.Text = "";
        txtchequeno.Text = "";
        txtmaskchequedate.Text = "";
        txtbillno.Text = "";
        txtmaskbilldate.Text = "";

    }

    cnn.Dispose();
    cnn.Close();




}

推荐答案

错误消息非常明确：

The error message is pretty explicit:

Not allowed to change the ConnectionString property. The connections current state is open.

连接已打开。当它打开时，你无法改变连接字符串，原因可以理解。

所以看看你的整个代码，而不仅仅是那一点。在某个地方，你已经打开了连接，但没有再次关闭它。

而不是全局连接，每次使用在内创建一个新连接块，你不会有问题：



The connection is open. While it's open you can't change the connection string, for understandable reasons.
So look at the whole of your code, not just that bit. Somewhere, you have opened the connection, but not closed it again.
Instead of a global connection, create a new one each time inside a using block and you won't have a problem:

string connectionString = ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;
using (OleDbConnection cnn = new OleDbConnection(connectionString))
    {
    cnn.Open();
    string sql = "select * from Billing where FormNo=" + txtformno.Text+"";
    using (OleDbCommand cmd = new OleDbCommand(sql, cnn))
        {
        using (OleDbDataReader dr = cmd.ExecuteReader())
            {
            ....
            }
        }
    }

然后系统将自动关闭并为您处理对象。

用该模式替换所有现有代码，问题将消失。 />


当我尝试搜索formno时，它给我的行/列没有数据。例外



嗯...

1）不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。请改用参数化查询。 （我应该在我的回答中提到这一点）

2）你为什么要从你的代码中删除OleDbDataReader.Read命令？

3）帮自己一个忙：列出要在SELECT命令中检索的列而不是usi9ng SELECT * FROM，并在DataReader访问中使用这些名称而不是整数索引。当表定义发生变化时，它可以节省大量的磁头，并且可以更高效，具体取决于数据库中的内容。

Then the system will automatically Close and Dispose the objects for you.
Replace all your existing code with that pattern and problems will disappear.

"when i try to search formno it giving me No data exists for the row/column. exception"

Well...
1) Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead. (I should have mentioned this in my answer)
2) Why did you remove the OleDbDataReader.Read command from your code?
3) Do yourself a favour: list the columns you want to retrieve in the SELECT command instead of usi9ng SELECT * FROM , and use those names in the DataReader access instead of integer indexes. It saves a lot of head scratching when the table definition changes, and can be a lot more efficient, depending on what is in the DB.

这篇关于不允许更改'connectionstring'属性。连接的当前状态是打开的。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！