I'm kinda new to C#, I usually code with VB.net and SQL command I have a small problem in my datatable: when I add a new row and or edit a row and save the changes, it all works fine, but when I delete a row, it does not get recorded on the server and the row say there my code for saving the changes as follow:
using (SqlConnection con = new SqlConnection(ConString))
{
CmdString = "SELECT * FROM IncomingPapers";
SqlCommand cmd = new SqlCommand(CmdString, con);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
SqlCommandBuilder builder = new SqlCommandBuilder(sda);
sda.UpdateCommand = builder.GetUpdateCommand();
sda.DeleteCommand = builder.GetDeleteCommand();
sda.InsertCommand = builder.GetInsertCommand();
sda.Update(dt);
}and the code for deleting a row from the datatable is:
dt.Rows.RemoveAt(PapersLV.SelectedIndex);
dt.AcceptChanges();The row gets deleted from the datatable but when saving the changes are not saved to the server and all deleted rows return to normal
2条答案
按热度按时间ql3eal8s1#
If you want to use the SqlDataAdapter to Update the rows you should not use RemoveAt and/or AcceptChanges but you just call the Delete method on the Row and then the SqlDataAdapter Update method
This, of course, means that you need to have a reference to the SqlDataAdapter available at the point of the Delete action
Your current code use RemoveAt, but this removes the row from the DataTable (an in memory object) and doesn't leave any info for the SqlDataAdapter.Update method to find the record on the database. Instead the Delete method doesn't remove the row, but changes the property RowState to DataRowState.Deleted. Now the SqlDataAdapter uses the primary key value from the row marked as deleted to find the record to delete in the database.
In a similar way DataTable.AcceptChanges doesn't update the database table. It works on the DataTable object changing the RowState property for every row to DataRowState.Unchanged and discards from memory every row marked as deleted. It makes also other internal changes to the DataTable object in such a way that the rows looks like they are just loaded from the database table. So, also in this case, the SqlDataAdapter.Update method has no way to know which rows are really changed and does nothing to the underlying database table.
cngwdvgl2#
You have to call
da.Update(dt);beforedt.AcceptChanges();