[RESOLVED] ObjectDisposedException Error

Hello, I'm getting this error:

Quote:

---

An unhandled exception of type 'System.' occurred in System.Data.SQLite.dll

cannot access a disposed object

---

... indicating the line:

VB.NET Code:

sqliteDataAdapter = new SQLiteDataAdapter("SELECT * FROM tbl_programs;", m_dbConnection);

Here's my code:

VB.NET Code:

private void addNew_tsmi_Click(object sender, EventArgs e)
{
    info_lbl.Text = "yeni program ekleniyor";
 
    List<string> list = new List<string>();
    int result;
 
    using (InsertData insertData = new InsertData())
    {
        insertData.ShowDialog();
        list = insertData.GetValues();
    }
 
    using (SQLiteConnection connection = m_dbConnection)
    {
        using (SQLiteCommand command = new SQLiteCommand(
            "INSERT INTO tbl_programs " +
            "(int_isSelected, txt_programName, int_is86, int_is64, txt_preCommand, " +
            "txt_preSwitch, txt_executable, txt_switches, int_timeout, txt_md5, txt_version, " +
            "txt_description, txt_category, dt_insertDate) " +
            "VALUES(" +
            ":isSelected, :programName, :is86, :is64, :preCommand, :preSwitch, :executable, " +
            ":switches, :timeout, :md5, :version, :description, :category, :insertDate);",
             connection))
        {
            command.Parameters.AddWithValue("isSelected", 0);
            command.Parameters.AddWithValue("programName", list[0]);
            ...
 
            connection.Open();
 
            command.ExecuteNonQuery();
        }
    }
 
    InitialiseDataAccessObjects();
    GetData();
 
    info_lbl.Text = "hazır";
}
 
private void InitialiseDataAccessObjects()
{
    sqliteDataAdapter = new SQLiteDataAdapter("SELECT * FROM tbl_programs;", m_dbConnection);
    sqliteCommandBuilder = new SQLiteCommandBuilder(sqliteDataAdapter);
 
    sqliteDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
}
 
private void GetData()
{
    // Retrieve the data.
    sqliteDataAdapter.Fill(dataTable);
 
    // The table can be used here to display and edit the data.
    // That will most likely involve data-binding but that is not a data access issue.
}

Can anyone help me?

That's because you don't completely understand how the using block works. As a result, you're abusing it by trying to use what appears to be a module level connection object.
Typically when you use the using block, you create a NEW object... at the end of that block that object is then DISPOSED for you.... that's what's happening here... you used a global connection (ick) in initializing your using block... so when it got to the end, that object (your global connection) was then closed and disposed, because that's what the using does, as a result you connection object was no longer available to you, so the next time you used it, you got the error message.
Personally, I'd create the connection, create teh command(s) execute it(them) and then close the connection and move on... there's no real good reason to keep the connection object around any longer than necessary.

-tg

I have to say, I really admire you determination to mess this up. I pointed you to that CodeBank thread of mine some time ago and you said that you had followed it and yet here we are again. You need to make up your mind how you're going to do your data access. Are you going to use a dat adapter or not? If you are then use. Don't be calling ExecuteNonQuery anywhere. What you need to do is very, very simple. You create your data adapter and you populate its four commands: SelectCommand, InsertCommand, UpdateCommand and DeleteCommand. When you want to retrieve data, you call Fill and that populates a DatatTable. When you want to save data, you call Update and that saves changes from that DataTable back to the database. That's it, that's all. No calls to ExecuteNonQuery. If you're inserting data then you add a row to your DataTable and then that change will be saved with all the others when you call Update. That's what the InsertCommand is for. All the changes come form the DataTable. If you want to insert new data then you add new data to the DataTable. If you want to update existing data then you modify existing data in the DataTable. If you want to delete existing data then you delete existing data from the DataTable. When you call Update on the data adapter, ALL the changes of ALL kinds will be saved from the DataTable to the database.

Quote:

---

Originally Posted by techgnome

there's no real good reason to keep the connection object around any longer than necessary.

---

The reason is that it's supposed to be one data adapter to retrieve the data and save the changes. The OP is supposed to be following an example but only did half a job of it.

Quote:

---

Originally Posted by jmcilhinney

The reason is that it's supposed to be one data adapter to retrieve the data and save the changes. The OP is supposed to be following an example but only did half a job of it.

---

RIght... that wouldbe the adaptor... not the connection... you should be able to drop the connection w/o affecting the adaptor, no? Then, re-connect the connection in the adaptor when you need it again. I get keeping the adaptor around, I only question the connection.

-tg

Quote:

---

Originally Posted by techgnome

RIght... that wouldbe the adaptor... not the connection... you should be able to drop the connection w/o affecting the adaptor, no? Then, re-connect the connection in the adaptor when you need it again. I get keeping the adaptor around, I only question the connection.

-tg

---

The connection gets closed after each use of the data adapter, but the data adapter contains the commands and each of the commands has a reference to the connection object. When you call Fill, the connection associated with the SelectCommand is opened implicitly and closed implicitly after the data is retrieved. When you call Update, the connection associated with the InsertCommand and/or UpdateCommand and/or DeleteCommand is opened implicitly and then closed implicitly after the changes are saved. It can be a different connection but there's really no good reason for it to be, unless the data is coming from one database and going to another.

jmcilhinney I couldn't exactly understand what you mean. I removed "using" statament and used my global connection and the problem seems gone. Am I on the wrong way? If I'm using wrong part of code in your codebank, what's the right one? Can you be more specific, give some example code.

In this case I should not use "executenonquery" and use this one?

VB.NET Code:

private SqlConnection connection = new SqlConnection("connection string here");
private SqlDataAdapter adapter;
private DataTable table = new DataTable();
 
private void InitialiseDataAccessObjects()
{
    this.adapter = new SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem", this.connection);
 
    SqlCommand delete = new SqlCommand("DELETE FROM StockItem WHERE ID = @ID", this.connection);
    SqlCommand insert = new SqlCommand("INSERT INTO StockItem (Name, Quantity, Unit) VALUES (@Name, @Quantity, @Unit)", this.connection);
    SqlCommand update = new SqlCommand("UPDATE StockItem SET Name = @Name, Quantity = @Quantity, Unit = @Unit WHERE ID = @ID", this.connection);
     
    delete.Parameters.Add("@ID", SqlDbType.Int, 4, "ID");
     
    insert.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name");
    insert.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity");
    insert.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit");
     
    update.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name");
    update.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity");
    update.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit");
    update.Parameters.Add("@ID", SqlDbType.Int, 4, "ID");
     
    this.adapter.DeleteCommand = delete;
    this.adapter.InsertCommand = insert;
    this.adapter.UpdateCommand = update;
     
    this.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
}
 
private void GetData()
{
    // Retrieve the data.
    this.adapter.Fill(this.table);
     
    // The table can be used here to display and edit the data.
    // That will most likely involve data-binding but that is not a data access issue.
}
 
private void SaveData()
{
    // Save the data.
    this.adapter.Update(this.table);
}

It's too complicated. What if I have several tables, foreign keys and need to use dataset? I'll have to start from the beginning?

Quote:

---

Originally Posted by nikel

It's too complicated.

---

Then I suggest that you give up programming now.

Quote:

---

Originally Posted by nikel

What if I have several tables, foreign keys and need to use dataset?

---

Then you add the appropriate code for that.

Quote:

---

Originally Posted by nikel

I'll have to start from the beginning?

---

Why would having to retrieve data for additional tables mean that the code you already had for existing tables was suddenly useless?

If you don't want to write all the SQL code and add all the parameters yourself then don't. You can create a typed DataSet and pretty much everything you'll need for most situations will be generated for you. All the connections, commands, SQL and parameters will be auto-generated. You then just create instances of the DataSet and the appropriate table adapter(s) where they're needed. There's also the option of using Entity Framework, if your data source supports it.

By the way, I notice that you are using SQLite in post #1 and SQL Server in post #8. Was that a deliberate change or did you not read my CodeBank thread properly?

Quote:

---

Originally Posted by nikel

It's too complicated. What if I have several tables, foreign keys and need to use dataset? I'll have to start from the beginning?

---

It may seem difficult and daunting at first, but it gets easier... it gets (hopefully) easier with practice. It just takes time.

-tg