[RESOLVED] Can't save from datagridview to database

Hello, I have a database and after I delete a row I cannot update database, I'm getting error: (edit: please ignore this)

Quote:

---

update requires a valid updatecommand when passed datarow collection with deleted rows

---

... indicating line: sqliteDataAdapter.Update(dataTable);
Here's my code:

VB.NET Code:

private void SaveChanges(bool keepControlsDisabled)
{
    disableControls();
 
    Validate();
    programs_dgv.CommitEdit(DataGridViewDataErrorContexts.Commit);
    programs_dgv.EndEdit();
 
    programs_dgv.DataSource = null;
    programs_dgv.DataSource = bindingSource;
    programs_dgv.Update();
 
    sqliteDataAdapter = new SQLiteDataAdapter();
    sqliteDataAdapter.Update(dataTable);
    dataTable.AcceptChanges();
 
    if (keepControlsDisabled == false)
    {
        enableControls();
    }
}

Here's the delete code:

VB.NET Code:

private void contextMenuStrip_tsmi_Click(object sender, EventArgs e)
{
    Int32 rowToDelete = programs_dgv.Rows.GetFirstRow(DataGridViewElementStates.Selected);
    programs_dgv.Rows.RemoveAt(programs_dgv.CurrentCell.RowIndex);
    programs_dgv.ClearSelection();
}

Is there a universal save function to pass records from datagridview to database?

EDIT: Am on the right way? Do I have to deal with database and display results on datagridview? Or firstly datagridview must be handled? I'm so confused with that. Which one is right for performance?

It appears that you are using a data adapter to populate a DataTable and then binding that to a DataGridView via a BindingSource. There's nothing wrong with that. That is a very common scenario.

Your SaveChanges method is a mess though. Loads of silly code there. All you need to do is call EndEdit on the BindingSource and then Update on the data adapter. That's it, that's all. There's no point disabling and enabling any controls because the user can't do anything with the form while that code is executing anyway. Clearing and setting DataSource properties is pointless and you don't need to call AceptChanges because Update does that automatically.

As for that error message, what you posted here is not what you saw. You would not have been told that you needed a valid UpdateCommand for Deleted rows. You would have been told that you needed a valid DeleteCommand for Deleted rows. Please use copy and paste for things like that so that you don't mislead us and possibly waste everyone's time trying to fix the wrong issue.

As far as solving the issue, you need to provide a DeleteCommand. A data adapter is basically a group of up to four commands. When you call Fill, the SQL SELECT statement in the SelectCommand is executed to retrieve data. When you call Update, the SQL INSERT, UPDATE and DELETE statements in the InsertCommand, UpdateCommand and DeleteCommand respectively are executed as needed to save changes. If you don't provide any SQL for those last three commands then you cannot save any changes of the corresponding type. If you want to be able to save deletions to the database then you need to either provide a DeleteCommand yourself or, if the SelectCommand is appropriate, use a command builder to do it for you. I suggest that you follow the CodeBank link in my signature below and check out my thread on Retrieving & Saving Data. It includes code examples of various ADO.NET scenarios, including using a data adapter with or without a command builder.

Thanks for the help. I'm working on it.

Hello, I tried this but didn't work:

VB.NET Code:

private void contextMenuStrip_tsmi_Click(object sender, EventArgs e)
{
    Int32 rowToDelete = programs_dgv.Rows.GetFirstRow(DataGridViewElementStates.Selected);
    programs_dgv.Rows.RemoveAt(programs_dgv.CurrentCell.RowIndex);
    programs_dgv.ClearSelection();
 
    bindingSource.EndEdit();
    sqliteDataAdapter = DeleteByAdapter(m_dbConnection);
    DataTable dataTable = (DataTable)bindingSource.DataSource;
}
 
private SQLiteDataAdapter DeleteByAdapter(SQLiteConnection connection)
{
    string idStr = programs_dgv.Rows[programs_dgv.CurrentCell.RowIndex].Cells[0].Value.ToString();
    // Create the DeleteCommand.
    SQLiteDataAdapter da = new SQLiteDataAdapter();
    SQLiteCommand command = new SQLiteCommand(
        "DELETE FROM tbl_programs WHERE id = :idStr", connection);
 
    // Add the parameters for the DeleteCommand.
    SQLiteParameter parameter = command.Parameters.Add(
        "idStr", DbType.Int32, 5, "idStr");
    parameter.SourceVersion = DataRowVersion.Original;
 
    da.DeleteCommand = command;
 
    return da;
}

I mean it doesn't delete anything.

and this gives me concurrency error:

VB.NET Code:

sqliteDataAdapter.Update(dataTable);

Where exactly in that code do you think it should be deleting something? Read what I said:

Quote:

---

All you need to do is call EndEdit on the BindingSource and then Update on the data adapter.

---

Now look at your code again.

By the way, the way to delete the current row in a DataGridView that is bound to a BindingSource that is bound to a DataTable is to call RemoveCurrent on the BindingSource. The point of the BindingSource is to be your single point of contact for the bound data. For most things, you use it only and don't touch the DataGridView or the DataTable.

Quote:

---

Originally Posted by nikel

and this gives me concurrency error:

VB.NET Code:

sqliteDataAdapter.Update(dataTable);

---

Now we're getting to something relevant at least. Try calling RemoveCurrent on the BindingSource and see whether that makes a difference. Also, there's no point or need to set the SourceVersion of the parameter in that case.

I called removeCurrent on the BindingSource but the same thing happened.

You're clearly doing something wrong but it's not obvious what. I suggest that you go back to my CodeBank thread and model your code on that. If you follow that example then it will work.

That worked fine. Thanks a lot!

Here's my final code:

VB.NET Code:

private void contextMenuStrip_tsmi_Click(object sender, EventArgs e)
{
    Int32 rowToDelete = programs_dgv.Rows.GetFirstRow(DataGridViewElementStates.Selected);
    programs_dgv.Rows.RemoveAt(programs_dgv.CurrentCell.RowIndex);
    programs_dgv.ClearSelection();
 
    InitialiseDataAccessObjects();
    SaveData();
 
    bindingSource.EndEdit();
    DataTable dataTable = (DataTable)bindingSource.DataSource;
    bindingSource.RemoveCurrent();
}
 
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.
}
 
private void SaveData()
{
    // Save the data.
    sqliteDataAdapter.Update(dataTable);
}