So, I have this datagridview associated with a Binding source that is bound to a underlying data table. The problem is that I need to manually add rows in a datagridview.
This cannot be done while it is connected, so I have to work with data binding.
If I add rows to the base datatable when the data is saved, the rows will be duplicated, probably because the binding source somehow grabbed onto the copy and inserted it as well.
Adding it to the binding source is what I tried to do, but it doesn’t quite work.
Let me explain exactly what my setup is:
I have a database with two tables: CashReceiptTable and CashReceiptItemsTable
CashReceiptItemsTable contains the FK for CashReceiptTable.
The form allows users to add and modify two tables.
When the user enters a new cashreceipt, the cash receipt identifier is -1, and the FK in cashReceiptitemstable is -1. When the database is saved, the cashReceipt identifier is updated, and I have to manually update the cashreceiptitem FK.
Here are the problems:
When I try to update CashReceiptID (FK) in more than one row in the cashreceiteitems binding source, the first row is updated and disappears (because it is filtered out), and the remaining rows are deleted, and I can no longer access them.
I have no idea why this is so, I have not updated the filter yet, so they should still be there, but when trying to access them, a RowNotInTableException is thrown.
I managed to find a job that copies the rows in the binding source to an array from memory, deletes the first row in the binding source (all other lines just disappear), updates the FK line and pastes them into the binding source and save the table.
This works fine, but why do the lines disappear?
I also have another small problem. When CashReceiptsTable is empty and I add a new row to it, if I add several rows to CashReceiptsItemTable, it will cause problems. When manually adding elements to the binding source, the addition of a new row occurs before the previous row and pushes it to the data. This hides it from my FK updater, and it is lost, it also removes it from the DataGridView.
This only happens when I add the first row to the CashReceiptsTable. Why is he doing this, and how can I fix it?
I am posting my code, which is autopopulated here:
private void autopopulate(decimal totalPayment) { //remove old rows for (int i = 0; i < tblCashReceiptsApplyToBindingSource.List.Count; i++) { DataRowView viewRow = tblCashReceiptsApplyToBindingSource.List[i] as DataRowView; RentalEaseDataSet.tblCashReceiptsApplyToRow row = viewRow.Row as RentalEaseDataSet.tblCashReceiptsApplyToRow; if (row.CashReceiptsID == this.ReceiptID) { tblCashReceiptsApplyToBindingSource.List.Remove(viewRow); i--; } } decimal payment = totalPayment; //look for an exact amount foreach (DataGridViewRow dueRow in dataViewDueRO.Rows) { decimal due = -1 * (Decimal)dueRow.Cells[Due.Index].Value; if (due == payment) { String charge = (String)dueRow.Cells[Description.Index].Value; int chargeID = ManageCheckbooks.findTransactionID(charge); tblCashReceiptsApplyToBindingSource.AddNew(); RentalEaseDataSet.tblCashReceiptsApplyToRow row = ((DataRowView)tblCashReceiptsApplyToBindingSource.Current).Row as RentalEaseDataSet.tblCashReceiptsApplyToRow; row.CashReceiptsID = this.ReceiptID; row.ApplyTo = chargeID; row.Paid = payment; //convert to positive payment = 0; break; } } //if the exact amount was found, payment will = 0, and this will do nothing, otherwise, //divy out everything left over (which will be everything) foreach (DataGridViewRow dueRow in dataViewDueRO.Rows) { String charge = (String)dueRow.Cells[Description.Index].Value; decimal due = (Decimal)dueRow.Cells[Due.Index].Value; if (due > 0 || payment <= 0) { continue; } int chargeID = ManageCheckbooks.findTransactionID(charge); payment += due; //due is negative, so this will subtract how much the user owes tblCashReceiptsApplyToBindingSource.AddNew(); RentalEaseDataSet.tblCashReceiptsApplyToRow row = ((DataRowView)tblCashReceiptsApplyToBindingSource.Current).Row as RentalEaseDataSet.tblCashReceiptsApplyToRow; row.CashReceiptsID = this.ReceiptID; row.ApplyTo = chargeID; if (payment >= 0) { //payment is enough to cover this row.Paid = due * -1; //convert to positive } else { //doesn't have enough money to conver this, can only cover partial, or none row.Paid = (due - payment) * -1; //math: //money remaining $50, current charge = $60 //payment = 50 + -60 = -10 //row["Paid"] = (-60 - -10) * -1 //row["Paid"] = (-60 + 10) * -1 //row["Paid"] = -50 * -1 //row["Paid"] = 50 } if (payment <= 0) { break; //don't conintue, no more money to distribute } } isVirginRow = true; }
And this is the function that stores it in the database:
protected override void saveToDatabase() { tblCashReceiptsBindingSource.EndEdit(); isVirginRow = false; RentalEaseDataSet.tblCashReceiptsRow[] rows = rentalEaseDataSet.tblCashReceipts.Select("ID < 0") as RentalEaseDataSet.tblCashReceiptsRow[]; int newID = -1; if (rows.Count() > 0) { tblCashReceiptsTableAdapter.Update(rows[0]); newID = rows[0].ID; } tblCashReceiptsTableAdapter.Update(rentalEaseDataSet.tblCashReceipts); //update table /*foreach (RentalEaseDataSet.tblCashReceiptsApplyToRow row in rentalEaseDataSet.tblCashReceiptsApplyTo.Select("CashReceiptsID = -1")) { row.CashReceiptsID = newID; }*/ //update binding source DataRowView[] applicationsOld = new DataRowView[tblCashReceiptsApplyToBindingSource.List.Count]; RentalEaseDataSet.tblCashReceiptsApplyToRow[] applicationsNew = new RentalEaseDataSet.tblCashReceiptsApplyToRow[tblCashReceiptsApplyToBindingSource.List.Count]; tblCashReceiptsApplyToBindingSource.List.CopyTo(applicationsOld, 0); for (int i = 0; i < applicationsOld.Count(); i++) { RentalEaseDataSet.tblCashReceiptsApplyToRow row = applicationsOld[i].Row as RentalEaseDataSet.tblCashReceiptsApplyToRow; if (row.CashReceiptsID < 0) { applicationsNew[i] = rentalEaseDataSet.tblCashReceiptsApplyTo.NewRow() as RentalEaseDataSet.tblCashReceiptsApplyToRow; applicationsNew[i]["ID"] = row.ID; applicationsNew[i]["CashReceiptsID"] = this.ReceiptID; applicationsNew[i][2] = row[2]; applicationsNew[i][3] = row[3]; applicationsNew[i][4] = row[4]; //row.Delete(); } } for (int i = 0; i < applicationsOld.Count(); i++) { try { if ((int)applicationsOld[i].Row["ID"] < 0) { applicationsOld[i].Row.Delete(); } } catch (RowNotInTableException) { break; } } this.tblCashReceiptsApplyToBindingSource.Filter = "CashReceiptsID = " + this.ReceiptID; foreach (DataRow newRow in applicationsNew) { if (newRow == null) { break; } tblCashReceiptsApplyToBindingSource.AddNew(); ((DataRowView)tblCashReceiptsApplyToBindingSource.Current).Row[0] = newRow[0]; ((DataRowView)tblCashReceiptsApplyToBindingSource.Current).Row[1] = newRow[1]; ((DataRowView)tblCashReceiptsApplyToBindingSource.Current).Row[2] = newRow[2]; ((DataRowView)tblCashReceiptsApplyToBindingSource.Current).Row[3] = newRow[3]; ((DataRowView)tblCashReceiptsApplyToBindingSource.Current).Row[4] = newRow[4]; } tblCashReceiptsApplyToBindingSource.EndEdit(); checkForBadRows(); tblCashReceiptsApplyToTableAdapter.Update(rentalEaseDataSet.tblCashReceiptsApplyTo); tblCashReceiptsApplyToTableAdapter.Fill(rentalEaseDataSet.tblCashReceiptsApplyTo); }