none
Can't update Sqlite with dataAdapter when deleting rows in datagridView in c# RRS feed

  • Question

  • Hello;

    I have two tabPages in my winform, the first I have a combobox which i fill it with my dataTable dt, it contains list of camera and 3 textBox (Latitude, longitude adressIP)

    here the code to fill my dt and combobox, and i call this function in the main()

    private void SQL()
            {
                string SQL = "SELECT * FROM CoordonneeCamera";
                SQLiteCommand cmd = new SQLiteCommand(SQL);
                cmd.Connection = sqliteconnection1;
                sqliteconnection1.Open();
                adapter = new SQLiteDataAdapter(cmd);
                ds = new DataSet();
                adapter.Fill(ds, "CoordonneeCamera");
                builder = new SQLiteCommandBuilder(adapter);
    
                try
                {
                    DataTable dt = ds.Tables[0];
                    comboBox1.DataSource = dt;
                    comboBox1.DisplayMember = "CameraName";
                    textBoxLat.DataBindings.Add("text", dt, "CameraLat");
                    textBoxLong.DataBindings.Add("text", dt, "CameraLgt");
                    textBoxAdressIP.DataBindings.Add("text", dt, "CameraAdressIP");
                }
                catch
                {
    
                }
                finally
                {
                    cmd.Dispose();
                    sqliteconnection1.Close();
                }
            }



    In the second tab i have a datagridView to manage my dataBase, so until now i have 4 textbox (column name) to add information to database with button save

    this is the code, in this function i recall SQL(), to update combobox.

    private void btnSave_Click(object sender, EventArgs e)
            {            
                SQLiteTransaction trans;
                string SQLInsert = "INSERT INTO CoordonneeCamera (ID,CameraName,CameraLat,CameraLgt,CreationDate,CameraAdressIP) VALUES";
                SQLInsert += "(@ID, @CameraName, @CameraLat, @CameraLgt, @CreationDate, @CameraAdressIP)";
                SQLiteCommand cmd = new SQLiteCommand(SQLInsert);
                cmd.Parameters.AddWithValue("@ID", null);
                cmd.Parameters.AddWithValue("@CameraName", txtboxNameAdd.Text);
                cmd.Parameters.AddWithValue("@CameraLat", txtBoxLatAdd.Text);
                cmd.Parameters.AddWithValue("@CameraLgt", txtBoxLgtAdd.Text);
                cmd.Parameters.AddWithValue("@CameraAdressIP", txtBoxCamIP.Text);
                cmd.Parameters.AddWithValue("@CreationDate", DateTime.Now);
                //cmd.Parameters.AddWithValue("@ModifiedDate", DateTime.Now);
    
                cmd.Connection = sqliteconnection1;
                sqliteconnection1.Open();
                //adapter = new SQLiteDataAdapter(cmd);
    
                trans = sqliteconnection1.BeginTransaction();
                cmd.Transaction = trans;
                int retval = 0;
    
                try
                {
                    retval = cmd.ExecuteNonQuery();
                    trans.Commit();
                    if (retval == 1)
                    {
                        MessageBox.Show("Données enregistrées");                    
                    }
                    else
                    {
                        MessageBox.Show("Données non enregistrées");
                    }                
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                }
                finally
                {               
                    cmd.Dispose();
                    sqliteconnection1.Close();
                }
    
                SQL();           
            }

    Now i want to delete some rows from datagrid, when i click on button delete it delete the row from datagrid but not in the dataBase, when i restart the app it show me all rows although those deleted!

    private void btnDelete_Click(object sender, EventArgs e)
            {           
                if (this.dataGridView1.SelectedRows.Count > 0)
                {
                    DataTable dt = ds.Tables[0];
                    //int index = this.dataGridView1.SelectedRows[0].Index;
                    //dt.DefaultView[index].Delete();
                    dt.Rows.RemoveAt(this.dataGridView1.SelectedRows[0].Index);
                    adapter.Update(dt);
                    
                }
                else
                {
                    MessageBox.Show("Veuillez selectionner une ligne");
                }
    
                
            }

    Any idea why 

    adapter.Update(dt);

    is not working for me and delete the row from dataBase?

    Thank you.

    Sunday, May 13, 2012 10:40 AM

Answers

  • Hi Bonnie;

    Well, that doesn't make sense ... what happened to the error you were getting previously? (You said the error was "The DataAdapter.SelectCommand.Connection property needs to be initialized".)

    Sorry, I forgot that in my constructor when i create dataSet, i open connection and i close it, like this:

    SQLiteConnection sqliteconnection1 = new SQLiteConnection(@"data source=coordCamera1.db3"); 
            SQLiteDataAdapter adapter;
            SQLiteCommandBuilder builder;
    {                                                           
                        string SQL = "SELECT * FROM CoordonneeCamera";
                        SQLiteCommand cmd = new SQLiteCommand(SQL);
                        cmd.Connection = sqliteconnection1;                   
                        sqliteconnection1.Open();
                        this.adapter = new SQLiteDataAdapter(cmd);
                        this.ds = new DataSet();
                        this.adapter.Fill(ds, "CoordonneeCamera");
                        this.builder = new SQLiteCommandBuilder(adapter);
                        try
                        {
                            this.dt = ds.Tables["CoordonneeCamera"];
                        }
                        catch
                        {
    
                        }
                        finally
                        {
                            cmd.Dispose();
                            sqliteconnection1.Close();
                        }

    I searched in forum and i found that the problem comes when i close the connection:

    http://forums.codeguru.com/showthread.php?t=367070

    so I commented this two lines

     cmd.Dispose();
     sqliteconnection1.Close();

    Soory again to be not explicit, in button btn_SaveClick() I save the inserted rows, and update my dataTable in the same time when i show dataGrid.

    I think i have to manage all this :)

    Also the second problem of "the error is "Operation is not valid due to the current state of the object."" comes from the two famous lines which there are also in button save.

    cmd.Dispose();
     sqliteconnection1.Close();

    Now it wotks fine, thanks  a lot for your support and your help.

    Tuesday, May 15, 2012 8:52 AM

All replies

  • I think the problems is still  what I told you in the other thread (for lurkers: http://social.msdn.microsoft.com/Forums/en-US/winformsdatacontrols/thread/b172874a-e608-412e-a130-e234b2f69b71): that you needed to use .Delete(),  not .Remove(). I thought you already did this, but I guess not.


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Sunday, May 13, 2012 4:49 PM
  • Yes, when i use delete like this and after update it shows me error :

    "The DataAdapter.SelectCommand.Connection property needs to be initialized;"

    private void btnDelete_Click(object sender, EventArgs e)
            {           
                if (this.dataGridView1.SelectedRows.Count > 0)
                {
                    DataTable dt = ds.Tables[0];
                    int index = this.dataGridView1.SelectedRows[0].Index;
                    dt.DefaultView[index].Delete();
                    //dt.Rows.RemoveAt(this.dataGridView1.SelectedRows[0].Index);
                    adapter.Update(dt);
                    
                }

    I initialized the dataAdapter in constructor:

    string sql = "SELECT * FROM CoordonneeCamera";
                        SQLiteCommand cmd = new SQLiteCommand(sql);
                        cmd.Connection = sqliteconnection1;
                        sqliteconnection1.Open();
                        adapter = new SQLiteDataAdapter(cmd);
                        ds = new DataSet();
                        adapter.Fill(ds, "CoordonneeCamera");
                        builder = new SQLiteCommandBuilder(adapter);

    Monday, May 14, 2012 5:41 AM
  • Well, I think you mean that in the constructor, you're calling you SQL() method that does the above code. I had been thinking about mentioning this in my reply to you yesterday, but I guess I forgot: try getting rid of the cmd.Dispose(). It's not necessary at all and it's probably what's causing the problem.

    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Monday, May 14, 2012 3:19 PM
  • You're right:) i commented cmd.dispose() and now it works, but when i decommented it it works also!!

    Now how could I edit dataGrid, I know that it's automatic but the change are not saved, should i make a method to edit?

    onother issue, when i want to add many items to dataGrid, in 

    private void btnSave_Click(object sender, EventArgs e)

    it shows me error in

    trans = sqliteconnection1.BeginTransaction(); (see above)

    the error is "Operation is not valid due to the current state of the object."

    How could i fix it?

    thanks again!

    Monday, May 14, 2012 4:43 PM
  • You're right:) i commented cmd.dispose() and now it works, but when i decommented it it works also!!

    Well, that doesn't make sense ... what happened to the error you were getting previously? (You said the error was "The DataAdapter.SelectCommand.Connection property needs to be initialized".)

    the error is "Operation is not valid due to the current state of the object."

    How could i fix it?

    I don't know ... the code that you've shown for btn_SaveClick() isn't updating the database with data from a Grid, it's updating from TextBoxes. You've either changed the code and haven't shown us, or there's some extra code in there somewhere that you haven't shown us.

    You should also try setting breakpoints and debugging. That usually tells you all you need to know to track down the reasons for your problem.


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Monday, May 14, 2012 7:45 PM
  • Hi Bonnie;

    Well, that doesn't make sense ... what happened to the error you were getting previously? (You said the error was "The DataAdapter.SelectCommand.Connection property needs to be initialized".)

    Sorry, I forgot that in my constructor when i create dataSet, i open connection and i close it, like this:

    SQLiteConnection sqliteconnection1 = new SQLiteConnection(@"data source=coordCamera1.db3"); 
            SQLiteDataAdapter adapter;
            SQLiteCommandBuilder builder;
    {                                                           
                        string SQL = "SELECT * FROM CoordonneeCamera";
                        SQLiteCommand cmd = new SQLiteCommand(SQL);
                        cmd.Connection = sqliteconnection1;                   
                        sqliteconnection1.Open();
                        this.adapter = new SQLiteDataAdapter(cmd);
                        this.ds = new DataSet();
                        this.adapter.Fill(ds, "CoordonneeCamera");
                        this.builder = new SQLiteCommandBuilder(adapter);
                        try
                        {
                            this.dt = ds.Tables["CoordonneeCamera"];
                        }
                        catch
                        {
    
                        }
                        finally
                        {
                            cmd.Dispose();
                            sqliteconnection1.Close();
                        }

    I searched in forum and i found that the problem comes when i close the connection:

    http://forums.codeguru.com/showthread.php?t=367070

    so I commented this two lines

     cmd.Dispose();
     sqliteconnection1.Close();

    Soory again to be not explicit, in button btn_SaveClick() I save the inserted rows, and update my dataTable in the same time when i show dataGrid.

    I think i have to manage all this :)

    Also the second problem of "the error is "Operation is not valid due to the current state of the object."" comes from the two famous lines which there are also in button save.

    cmd.Dispose();
     sqliteconnection1.Close();

    Now it wotks fine, thanks  a lot for your support and your help.

    Tuesday, May 15, 2012 8:52 AM
  • I'm glad that you've finally got it working! Good luck with the rest of your project!  =0)


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Tuesday, May 15, 2012 3:13 PM