Results 1 to 11 of 11

Thread: [RESOLVED] ObjectDisposedException Error

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Oct 2013
    Posts
    200

    Resolved [RESOLVED] ObjectDisposedException Error

    Hello, I'm getting this error:
    An unhandled exception of type 'System.' occurred in System.Data.SQLite.dll

    cannot access a disposed object
    ... indicating the line:
    VB.NET Code:
    1. sqliteDataAdapter = new SQLiteDataAdapter("SELECT * FROM tbl_programs;", m_dbConnection);

    Here's my code:
    VB.NET Code:
    1. private void addNew_tsmi_Click(object sender, EventArgs e)
    2. {
    3.     info_lbl.Text = "yeni program ekleniyor";
    4.  
    5.     List<string> list = new List<string>();
    6.     int result;
    7.  
    8.     using (InsertData insertData = new InsertData())
    9.     {
    10.         insertData.ShowDialog();
    11.         list = insertData.GetValues();
    12.     }
    13.  
    14.     using (SQLiteConnection connection = m_dbConnection)
    15.     {
    16.         using (SQLiteCommand command = new SQLiteCommand(
    17.             "INSERT INTO tbl_programs " +
    18.             "(int_isSelected, txt_programName, int_is86, int_is64, txt_preCommand, " +
    19.             "txt_preSwitch, txt_executable, txt_switches, int_timeout, txt_md5, txt_version, " +
    20.             "txt_description, txt_category, dt_insertDate) " +
    21.             "VALUES(" +
    22.             ":isSelected, :programName, :is86, :is64, :preCommand, :preSwitch, :executable, " +
    23.             ":switches, :timeout, :md5, :version, :description, :category, :insertDate);",
    24.              connection))
    25.         {
    26.             command.Parameters.AddWithValue("isSelected", 0);
    27.             command.Parameters.AddWithValue("programName", list[0]);
    28.             ...
    29.  
    30.             connection.Open();
    31.  
    32.             command.ExecuteNonQuery();
    33.         }
    34.     }
    35.  
    36.     InitialiseDataAccessObjects();
    37.     GetData();
    38.  
    39.     info_lbl.Text = "hazır";
    40. }
    41.  
    42. private void InitialiseDataAccessObjects()
    43. {
    44.     sqliteDataAdapter = new SQLiteDataAdapter("SELECT * FROM tbl_programs;", m_dbConnection);
    45.     sqliteCommandBuilder = new SQLiteCommandBuilder(sqliteDataAdapter);
    46.  
    47.     sqliteDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
    48. }
    49.  
    50. private void GetData()
    51. {
    52.     // Retrieve the data.
    53.     sqliteDataAdapter.Fill(dataTable);
    54.  
    55.     // The table can be used here to display and edit the data.
    56.     // That will most likely involve data-binding but that is not a data access issue.
    57. }

    Can anyone help me?

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: ObjectDisposedException Error

    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 don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: ObjectDisposedException Error

    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.

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: ObjectDisposedException Error

    Quote Originally Posted by techgnome View Post
    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.

  5. #5
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: ObjectDisposedException Error

    Quote Originally Posted by jmcilhinney View Post
    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: ObjectDisposedException Error

    Quote Originally Posted by techgnome View Post
    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.

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Oct 2013
    Posts
    200

    Re: [RESOLVED] ObjectDisposedException Error

    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.

  8. #8

    Thread Starter
    Addicted Member
    Join Date
    Oct 2013
    Posts
    200

    Re: ObjectDisposedException Error

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

    VB.NET Code:
    1. private SqlConnection connection = new SqlConnection("connection string here");
    2. private SqlDataAdapter adapter;
    3. private DataTable table = new DataTable();
    4.  
    5. private void InitialiseDataAccessObjects()
    6. {
    7.     this.adapter = new SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem", this.connection);
    8.  
    9.     SqlCommand delete = new SqlCommand("DELETE FROM StockItem WHERE ID = @ID", this.connection);
    10.     SqlCommand insert = new SqlCommand("INSERT INTO StockItem (Name, Quantity, Unit) VALUES (@Name, @Quantity, @Unit)", this.connection);
    11.     SqlCommand update = new SqlCommand("UPDATE StockItem SET Name = @Name, Quantity = @Quantity, Unit = @Unit WHERE ID = @ID", this.connection);
    12.      
    13.     delete.Parameters.Add("@ID", SqlDbType.Int, 4, "ID");
    14.      
    15.     insert.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name");
    16.     insert.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity");
    17.     insert.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit");
    18.      
    19.     update.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name");
    20.     update.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity");
    21.     update.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit");
    22.     update.Parameters.Add("@ID", SqlDbType.Int, 4, "ID");
    23.      
    24.     this.adapter.DeleteCommand = delete;
    25.     this.adapter.InsertCommand = insert;
    26.     this.adapter.UpdateCommand = update;
    27.      
    28.     this.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
    29. }
    30.  
    31. private void GetData()
    32. {
    33.     // Retrieve the data.
    34.     this.adapter.Fill(this.table);
    35.      
    36.     // The table can be used here to display and edit the data.
    37.     // That will most likely involve data-binding but that is not a data access issue.
    38. }
    39.  
    40. private void SaveData()
    41. {
    42.     // Save the data.
    43.     this.adapter.Update(this.table);
    44. }
    Last edited by nikel; Aug 24th, 2017 at 12:05 PM. Reason: I added word executenonquery

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    Oct 2013
    Posts
    200

    Re: [RESOLVED] ObjectDisposedException Error

    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?

  10. #10
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: [RESOLVED] ObjectDisposedException Error

    Quote Originally Posted by nikel View Post
    It's too complicated.
    Then I suggest that you give up programming now.
    Quote Originally Posted by nikel View Post
    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 View Post
    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?

  11. #11
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: [RESOLVED] ObjectDisposedException Error

    Quote Originally Posted by nikel View Post
    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width