DazlerD

Hi

I have a form which shows the order lines added to an order. These are displayed in a flexgrid.

There is a button called 'Add Product' which shows a Product form with all the products in the database listed in a list box. The user can choose as many products as he likes and on pressing OK they should all be added to the flexgrid in the order form as they are now order lines.

Ive got a problem with the 'Row Already belongs to another table' error. It occurs when I try to add a new OrderDetail datarow to my orderdetails table. The thing is ive read various sites and they all say this error occurs when you try to copy stuff from one table to another, but Im trying to copy rows from the Product datatable to the OrderDetails datatable.

The order form passes in a datatable and I would like the product form to add to this all the chosen products. Then I can move the contents of the new orderdetails table into the actual datatable and refresh the grid.

I am using typed datasets in VB.NET 2005 and I have two OrderDetails tables in my dataset.xsd.

Please can somebody tell me what im doing wrong and what I should be doing

Thanks

Darren

**********************

ORDER FORM

Private Sub cmdAddProduct_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs)

Dim dtOrderDetails1 As New CreamDS.OrderDetails1DataTable

Dim frmSelect As New frmSelectOrderProducts(mlngPCNumber)

dtOrderDetails1 = frmSelect.FormLoad()

If dtOrderDetails1.Count > 0 Then

' Add the order lines in CreamDS.OrderDetails1 to creamDS.OrderDetails

End If

End Sub

PRODUCT FORM

Private mdtNewProducts As New CreamDS.OrderDetails1DataTable

************************************

Public Function FormLoad() As CreamDS.OrderDetails1DataTable

Me.ShowDialog()

Return mdtNewProducts

End Function

************************************

Private Sub cmdOK_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs)

Dim drOrderDetails1 As CreamDS.OrderDetails1Row

Dim drProduct As CreamDS.ProductRow = Me.CreamDS.Product.NewProductRow

Do While iCount < lsvProducts.SelectedItems.Count

drProduct = lsvProducts.SelectedItems(iCount).Tag

drOrderDetails1 = CreamDS.OrderDetails1.NewOrderDetails1Row

If Not drProduct.IsNominalCodeNull Then drOrderDetails1.NominalCode = drProduct.NominalCode

' fill the rest of the columns

mdtNewProducts.AddOrderDetails1Row(drOrderDetails1) ' FAILS ON THIS LINE

Loop

************************************



Re: .NET Framework Data Access and Storage Copying rows from one datatable to another

cverdon

Hi,

Two things to verify:

drOrderDetails1 must be a new row at every iteration of the loop.
drOrderDetails1 must have a unique primary key.

Can you please post the whole code of the ok click event.

Charles




Re: .NET Framework Data Access and Storage Copying rows from one datatable to another

DazlerD

Hi thanks for your help.

The orderdetails table has a primary key 'Counter' which is an autoincrement. In my dataset.xsd i have removed the primary key and made in non increment. I had to do this becuase of the way the grid works. The grid needs to always show one records so even if its an empty row the datatable needs to contain one datarow which cant have a PCNumber or a Counter as they havent been commited yet.

Private Sub cmdOK_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdOK.Click, lsvProducts.DoubleClick

Dim drOrderDetails1 As CreamDS.OrderDetails1Row

Dim drProduct As CreamDS.ProductRow = Me.CreamDS.Product.NewProductRow

Try

Cursor = System.Windows.Forms.Cursors.WaitCursor

Do While iCount < lsvProducts.SelectedItems.Count

' Get the details for the whole datarow

drProduct = lsvProducts.SelectedItems(iCount).Tag

' Copy the product into the Order Detail

drOrderDetails1 = CreamDS.OrderDetails1.NewOrderDetails1Row

With drOrderDetails1

.PCNumber = mlngPCNumber

.ProductId = drProduct.ProductId

If Not drProduct.IsNominalCodeNull Then .NominalCode = drProduct.NominalCode

If Not drProduct.IsDescriptionNull Then .Description = drProduct.Description

If Not drProduct.IsUnitOfSaleNull Then .UnitOfSale = drProduct.UnitOfSale

If Not drProduct.IsSupplierCodeNull Then .SupplierCode = drProduct.SupplierCode

If Not drProduct.IsSalespriceNull Then .SellPrice = drProduct.Salesprice

If Not drProduct.IsLastPurchasePriceNull Then .CostPrice = drProduct.LastPurchasePrice.ToString

If Not drProduct.IsTaxCodeNull Then .TaxCode = Mid(drProduct.TaxCode, 2, 1)

.TaxRate = drProduct.TaxRate

If Not drProduct.IsDeptNameNull Then .DepartmentName = drProduct.DeptName

End With

' Add this to the Order details 1 datatable

mdtNewProducts.AddOrderDetails1Row(drOrderDetails1)

Loop

End If

Me.Hide()

Catch ex As Exception

MessageBox.Show("cmdOK_Click " & ex.ToString, CONST_CREAM_MSG_CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Error)

Finally

Cursor = System.Windows.Forms.Cursors.Default

End Try

End Sub





Re: .NET Framework Data Access and Storage Copying rows from one datatable to another

cverdon

Dim drProduct As CreamDS.ProductRow = Me.CreamDS.Product.NewProductRow

The yellow above is not needed, you overwrite drProduct a few lines later anyway.

The problem is that you need to assign a temporary primary key to every row. Setting the AutoIncrement in the designer generates such a temporary key. The InsertCommand should automatically update the key to the real one after it's executed. Look at the sql, it should look like:

INSERT INTO xyz (...) VALUES (...);
SELECT ... FROM xyz WHERE every column except id = inserted data;

Charles





Re: .NET Framework Data Access and Storage Copying rows from one datatable to another

DazlerD

Hi Charles

Thanks for the reply. Point taking about the yellow hightlighted text.

Ive been into my CreamDS.xsd and made the Counter column of OrderDetails1 to the primary key and also set it to autoincrement. I still get the same error when trying to add a row into this table.

Just to confirm, In my dataset I have two datatables which look at the same actual database table namely 'OrderDetails'.

Both datatables have fill commands of 'select * from OrderDetails' but 'dtOrderDetails' has a FillByCompanyId query aswell 'select * from OrderDetails where companyId = @CompanyId'. This FillByCompanyId query gets called when loading the order form with an exisiting order.

My InsertCommand on OrderDetails1 is:

INSERT INTO [orderdetails] ([PCNumber], [ProductId], [Description], [Comment1], [Comment2], [SupplierCode], [UnitOfSale], [Qty], [CostPrice], [SellPrice], [SupInvCost], [NominalCode], [FullNetAmount], [NetAmount], [TaxAmount], [GrossAmount], [TaxCode], [TaxRate], [DepartmentNumber], [DepartmentName]) VALUES (@PCNumber, @ProductId, @Description, @Comment1, @Comment2, @SupplierCode, @UnitOfSale, @Qty, @CostPrice, @SellPrice, @SupInvCost, @NominalCode, @FullNetAmount, @NetAmount, @TaxAmount, @GrossAmount, @TaxCode, @TaxRate, @DepartmentNumber, @DepartmentName);
SELECT Counter, PCNumber, ProductId, Description, Comment1, Comment2, SupplierCode, UnitOfSale, Qty, CostPrice, SellPrice, SupInvCost, NominalCode, FullNetAmount, NetAmount, TaxAmount, GrossAmount, TaxCode, TaxRate, DepartmentNumber, DepartmentName FROM OrderDetails WHERE (Counter = SCOPE_IDENTITY())

Is there anything else I can try. I cant believe I cant create a new datarow of type OrderDetails1 and add it to datatable OrderDetails1.

Thanks for your help

Darren





Re: .NET Framework Data Access and Storage Copying rows from one datatable to another

Stuart Baker

I have had the same error happen too and it took some figuring too! As you spotted the Key idea whilst true is a little bit of misdirection in resolving this explicit problem.

In you cmd_OK you create a drOrderDetails1 that belongs to the CreamDS. However this row is added to the mdtNewProducts which (as far as I can tell is a standalone instance rather than refering to a table instance within the dataset.

You need to ensure that your NEW row is created from the SAME table. At the moment you are take a row created by the CreamDS.OrderDetails1 but trying to add it to a different table - the mdtNewProducts. You are not allowed to do this.

Ensure your new row is created by the mdtNewProducts table, I think this might be the line

                      drOrderDetails1 = mdtNewProducts.NewOrderDetailsRow()





Re: .NET Framework Data Access and Storage Copying rows from one datatable to another

DazlerD

Stuart

Thanks very much, you are right and that works great. I can now add rows to the mdtNewProducts datatable!!

Likewise when Im reading the rows from orderdetails1 into my orderdetails datatable i need to make sure that the current row is from dtOrderDetails1 rather CreamDS.OrderDetails1DataTable.

Thanks again

Darren