1st record added to table crashes my program

[Tony K]

VB.NET 2005
Okay, 1 more attempt at fixing this problem. I have a parent table in
details view, and a datagridview which is the child table. If and only if
there are not records in the parent table 'Purchase Orders' will I receive
this error message when saving for the first time. I did the drag/drop
method for creating this form.

When clicking on the save icon from the Purchase_OrdersBindingNavigator the
following code executes and then I receive this error message.

**************code*************
Try
Cursor = Cursors.WaitCursor
Me.Validate()
Me.Purchase_OrdersBindingSource.EndEdit()
Me.Purchase_OrdersTableAdapter.Update(Me.Inventory_management_databaseDataSet.Purchase_Orders)
Me.Inventory_TransactionsBindingSource.EndEdit()
Me.Inventory_TransactionsTableAdapter.Update(Me.Inventory_management_databaseDataSet.Inventory_Transactions)
Me.StatusToolStripStatusLabel.Text = Nothing
UpdateTotalLabel()
Catch ex As Exception
MessageBox.Show(ex.ToString, "Error Saving Purchase Order",
MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
Finally
Cursor = Cursors.Default
End Try

The Update queries are as follows (keep in mind they were also
autogenerated)
UPDATE `Purchase Orders` SET `PurchaseOrderNumber` = ?,
`PurchaseOrderDescription` = ?, `SupplierID` = ?, `EmployeeID` = ?,
`OrderDate` = ?, `DateRequired` = ?, `ShipDate` = ?, `ShippingMethodID` = ?,
`FreightCharge` = ?, `PurchaseOrderClosed` = ?, `DateReceived` = ? WHERE
((`PurchaseOrderID` = ?) AND ((? = 1 AND `PurchaseOrderNumber` IS NULL) OR
(`PurchaseOrderNumber` = ?)) AND ((? = 1 AND `PurchaseOrderDescription` IS
NULL) OR (`PurchaseOrderDescription` = ?)) AND ((? = 1 AND `SupplierID` IS
NULL) OR (`SupplierID` = ?)) AND ((? = 1 AND `EmployeeID` IS NULL) OR
(`EmployeeID` = ?)) AND ((? = 1 AND `OrderDate` IS NULL) OR (`OrderDate` =
?)) AND ((? = 1 AND `DateRequired` IS NULL) OR (`DateRequired` = ?)) AND ((?
= 1 AND `ShipDate` IS NULL) OR (`ShipDate` = ?)) AND ((? = 1 AND
`ShippingMethodID` IS NULL) OR (`ShippingMethodID` = ?)) AND ((? = 1 AND
`FreightCharge` IS NULL) OR (`FreightCharge` = ?)) AND ((? = 1 AND
`PurchaseOrderClosed` IS NULL) OR (`PurchaseOrderClosed` = ?)) AND ((? = 1
AND `DateReceived` IS NULL) OR (`DateReceived` = ?)))

UPDATE `Inventory Transactions` SET `TransactionDate` = ?, `ProductID` = ?,
`PurchaseOrderID` = ?, `TransactionDescription` = ?, `UnitPrice` = ?,
`UnitsOrdered` = ?, `UnitsReceived` = ?, `UnitsSold` = ?, `UnitsShrinkage` =
?, `Equipment` = ? WHERE ((`TransactionID` = ?) AND ((? = 1 AND
`TransactionDate` IS NULL) OR (`TransactionDate` = ?)) AND ((? = 1 AND
`ProductID` IS NULL) OR (`ProductID` = ?)) AND ((? = 1 AND `PurchaseOrderID`
IS NULL) OR (`PurchaseOrderID` = ?)) AND ((? = 1 AND
`TransactionDescription` IS NULL) OR (`TransactionDescription` = ?)) AND ((?
= 1 AND `UnitPrice` IS NULL) OR (`UnitPrice` = ?)) AND ((? = 1 AND
`UnitsOrdered` IS NULL) OR (`UnitsOrdered` = ?)) AND ((? = 1 AND
`UnitsReceived` IS NULL) OR (`UnitsReceived` = ?)) AND ((? = 1 AND
`UnitsSold` IS NULL) OR (`UnitsSold` = ?)) AND ((? = 1 AND `UnitsShrinkage`
IS NULL) OR (`UnitsShrinkage` = ?)) AND ((? = 1 AND `Equipment` IS NULL) OR
(`Equipment` = ?)))


When the Update command for the 2nd table 'Inventory Transactions' is
executed, I receive the following error...only when it is the first record
in Purchase Orders.

You cannot add or change a record because a related record is required in
table 'Purchase Orders'.

at
System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs
rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs
rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable
dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
at
Daily_Foods_Inventory.Inventory_management_databaseDataSetTableAdapters.Inventory_TransactionsTableAdapter.Update(Inventory_TransactionsDataTable
dataTable) in C:\Users\Tony\Documents\Visual Studio 2005\Projects\Daily
Foods Inventory\Daily Foods
Inventory\Inventory_management_databaseDataSet.Designer.vb:line 9381
at
Daily_Foods_Inventory.FormPurchaseOrder.Purchase_OrdersBindingNavigatorSaveItem_Click(Object
sender, EventArgs e) in C:\Users\Tony\Documents\Visual Studio
2005\Projects\Daily Foods Inventory\Daily Foods
Inventory\FormPurchaseOrder.vb:line 15

I do not know how to debug this. I step through and each time I don't even
see where an INSERT is performed. Each of the tables do have an Insert
command, but when stepping through this, I don't see WHERE it is executed.

Is there something I am missing?? Is there a property I'm missing in the
BindingSource for each table?

Thank you in advance,
Tony K.

 

[Steve Gerrard]

VB.NET 2005
Okay, 1 more attempt at fixing this problem. I have a parent table in
details view, and a datagridview which is the child table. If and
only if there are not records in the parent table 'Purchase Orders' will I
receive this error message when saving for the first time.

I can't tell you exactly what the problem is, but it almost certainly has to do
with PurchaseOrderID, in both tables.

Where does the new value for PurchaseOrderID come from when you create a new
purchase order? This value must also get set in all the transactions that are
related to this purchase order. Something in that chain is getting broken.

I believe that your generated dataset will try to save the new purchase order,
then get back from the database the assigned PurchaseOrderID, then pass this
along to all the new transactions, before actually saving the transactions.

However, it may have difficulty retaining a link between the new purchase order
and the new transactions, and may need an existing purchase order to come up
with a temporary value to link the two tables. Without one, it can't form the
link, so the transactions don't get the correct PurchaseOrderID, and the update
fails.

Or something like that.

One solution would be to always save the new purchase order without any
transactions, then add the transactions and save those. That should help
maintain the relationship. Or start monitoring changes in the table, like the
NewRow event, and see if you can pickup what value is being used for a temporary
PurchaseOrderID, and trace it thorugh the whole process. Maybe you can assign
it -1 if there are no existing purchase orders.

 

[Tony K]

Steve,
I have temporarily added the PurchaseOrderID column to my datagridview
to see what number is being generated and you are right. When the 1st
record is created, the PurchaseOrderID assigned to my child table is "0"
instead of say... "58" If I exit, then re-enter the program, and re-enter
the info for the datagridview, the correct number "58" is now in the field
for PurchaseOrderID.
I have created a separate routine to save the PurchaseOrder table
first.. then refresh the datagridview. Still, when I select to add the
first record to the child table in the dgv, the number shows up as "0".

Tony K.

 

[Tony K]

Steve,
A little update here. I've been trying some things and found the
following code to allow the PurchaseOrderID in my child table to match my
parent table. Please note, I have temporarily removed the child table
update functions and have placed them in a separate routine that requires a
different button click to update that table.


My save routine looks like this:

Try
Cursor = Cursors.WaitCursor
Me.Purchase_OrdersBindingSource.EndEdit()
Me.Purchase_OrdersTableAdapter.Update(Me.Inventory_management_databaseDataSet.Purchase_Orders)
Me.Purchase_OrdersTableAdapter.Fill(Me.Inventory_management_databaseDataSet.Purchase_Orders)

Catch ex As Exception
MessageBox.Show(ex.ToString, "Error Saving Purchase Order",
MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
Finally
Cursor = Cursors.Default
End Try

See... apparently, I have to fill the parent table again before the
primary key of that table is available to my child table. I must be on the
right track, but not sure how to properly correct this problem. Does it
sound like a Binding issue? (just guessing here).

Tony K.

 

[Steve Gerrard]

Steve,
A little update here. I've been trying some things and found the
following code to allow the PurchaseOrderID in my child table to
match my parent table.

See... apparently, I have to fill the parent table again before the
primary key of that table is available to my child table. I must be
on the right track, but not sure how to properly correct this problem. Does
it sound like a Binding issue? (just guessing here).

Does the dataset include both tables, and a defined relationship between
purchase order and transaction, based on PurchaseOrderID? That would seem to me
to be necessary for the link to happen automatically.