Adding parent/child rows to datatable/dataset + MySQL DataSource

[reyes]

Hi all

Due to an issue with a larger project, I'm trying a basic test of using a MySQL database (using InnoDB engine), which is this:

1. Add a new row to a 'Customers' table.
2. Add a new row, at the same time, to an 'Orders' table.

I'm doing this using a typed DataSet within Visual Studio 2017 Community. My code is:

            DataSet1 ds = new DataSet1();            DataSet1TableAdapters.TableAdapterManager manager = new DataSet1TableAdapters.TableAdapterManager();
            manager.tblCustomersTableAdapter = new DataSet1TableAdapters.tblCustomersTableAdapter();
            manager.tblCustomersTableAdapter.Fill(ds.tblCustomers);
            manager.tblOrdersTableAdapter = new DataSet1TableAdapters.tblOrdersTableAdapter();
            manager.tblOrdersTableAdapter.Fill(ds.tblOrders);


            DataSet1.tblCustomersRow custRow = ds.tblCustomers.NewtblCustomersRow();
            custRow.Name = "Berty";


            ds.tblCustomers.Rows.Add(custRow);


            DataSet1.tblOrdersRow ordersRow = ds.tblOrders.NewtblOrdersRow();
            ordersRow.CustomerID = custRow.CustomerID;


            ds.tblOrders.Rows.Add(ordersRow);


            manager.UpdateAll(ds);

My error is:

MySql.Data.MySqlClient.MySqlException: 'Cannot add or update a child row: a foreign key constraint fails (`dbTest`.`tblOrders`, CONSTRAINT `FK_Orders_Customers` FOREIGN KEY (`CustomerID`) REFERENCES `tblCustomers` (`CustomerID`) ON DELETE CASCADE ON UPDATE CASCADE)'

The relation in MySQL is set to 'On Update CASCADE' and 'On Delete CASCADE' as shown:




Using SQL Server, this approach (assigning the ID of the PK to that of the FK works), so is it a MySQL thing?

 

[jmcilhinney]

Is the database generating the PK values automatically? If so then the missing piece is probably getting the PK values for the parent table back into the DataSet and/or propagating those values to the child DataTable. If you're using SQL Server, the SQL in the InsertCommand of your table adapters would look something like this:

INSERT INTO SomeTable(Column1, Column2) VALUES (@Column1, @Column2);
SELECT ID = SCOPE_IDENTITY()

When you add a row to the parent DataTable, it will generate a temporary ID and then that is what you'll use in the child DataTable. When you save the parent data, the database will generate a final ID. That value is retrieved using the SCOPE_IDENTITY function and pulled back into the parent DataTable. You need the DataRelation in your DataSet also set to cascade on update so that that parent ID value is propagated to the child DataTable, ready to save to the database.

Things will be basically the same when using MySQL if the database is generating the final PK values, but you need to find the MySQL equivalent of SCOPE_IDENTITY.

 

[reyes]

Is the database generating the PK values automatically? If so then the missing piece is probably getting the PK values for the parent table back into the DataSet and/or propagating those values to the child DataTable. If you're using SQL Server, the SQL in the InsertCommand of your table adapters would look something like this:

INSERT INTO SomeTable(Column1, Column2) VALUES (@Column1, @Column2);
SELECT ID = SCOPE_IDENTITY()

When you add a row to the parent DataTable, it will generate a temporary ID and then that is what you'll use in the child DataTable. When you save the parent data, the database will generate a final ID. That value is retrieved using the SCOPE_IDENTITY function and pulled back into the parent DataTable. You need the DataRelation in your DataSet also set to cascade on update so that that parent ID value is propagated to the child DataTable, ready to save to the database.

Things will be basically the same when using MySQL if the database is generating the final PK values, but you need to find the MySQL equivalent of SCOPE_IDENTITY.

Good point on the final part of the Insert query jmcilhinney, and thanks for the quick reply. Incidentally, you right on. Having just looked at my Insert query for the dataset, the statement is: INSERT INTO `tblCustomers` (`Name`) VALUES (@p1)

I just tried right clicking the table adapter > Configure > Advanced Options > check 'Refresh the data table' > OK. After doing that, the Insert statement doesn't change at all (nor does the Update), and if I go back in, it's unchecked again. This would appear to be the reason for the error (the id is never being corrected - it's probably remaining '-1', '-2' etc. etc.

Would you agree that my options are either try and append the SCOPE_IDENTITY equivalent, and if that fails, ditch the table adapters in favour of data adapters with own-built stored procedures? If I can't utilise the table adapter manager functionality, I may be better off benefiting from the typed dataset without splitting when I do/don't use table adapters - that just sounds messy.

Thanks again.

 

[jmcilhinney]

There's nothing wrong with making a few manual changes to a typed DataSet. The automatic addition of the SELECT to the INSERT is part of the tools Microsoft build for SQL Server and the MySQL tools could be created that way too. If they're not then it's just a manual tweak and you're good to go.

 

[reyes]

Well, I have tried a couple of tweaks as you suggested:

1. Added the MySQL equivalent of SCOPE_IDENTITY(), SELECT LAST_INSERT_ID();
2. Created a stored procedure for each CRUD operation.

Still no luck, this same error as stated above comes no matter what. It's clearly holding on to the '-1' that's generated with it being an auto increment column. I wonder whether when it comes to the table adapter manager, something just is not supported...

The queries themselves work. I've tested them in separate executions (rather than as part of the table adapter manager) and they work.

Stumped!

 

[jmcilhinney]

Try just calling Update on the parent table adapter and then examine the PK values in the parent DataTable. Have they changed? What does you INSERT sproc look like?

 

[reyes]

Okay, here's the code that was called:

DataSet1 ds = new DataSet1();            DataSet1TableAdapters.tblCustomersTableAdapter taCust = new DataSet1TableAdapters.tblCustomersTableAdapter();
            DataSet1.tblCustomersRow custRow = ds.tblCustomers.NewtblCustomersRow();
            custRow.Name = "James";


            ds.tblCustomers.Rows.Add(custRow);


            int id = taCust.Update(ds.tblCustomers);

Here's what the dataset visualizer shows for ds.tblCustomers:



Here's the sproc as it is now:

CREATE DEFINER=`admin`@`192.168.0.10` PROCEDURE `insertCustomer`(IN p1 VARCHAR(45))
BEGIN


    INSERT INTO tblCustomers (Name) VALUES (p1);
    SELECT LAST_INSERT_ID();


END

 

[jmcilhinney]

Have a close look at the SQL code I posted earlier and compare it to yours. Do you see a significant difference?

 

[reyes]

Ah yes, I see it... SELECT ID = SCOPE_IDENTITY()

Well, I was very hopeful there, but I can't seem to get the equivalent (LAST_INSERT_ID()) working in MySQL Workbench. I just tried it in a stored procedure, and in an auto-generated Insert statement in my dataset.

That's what I was ABOUT to send. But! I tried adding this to my insert statement:

	SELECT CustomerID FROM tblCustomers WHERE CustomerID = LAST_INSERT_ID();

And it works! Even within the table adapter manager, which is exactly as I wanted. I did try more short-handed versions but I couldn't get it working, whereas this does.

I love overcoming a challenge - thank you very much for pointing me in the right direction (twice)! :smug:

 

[jmcilhinney]

You missed my point. Here's my code:

SELECT ID = SCOPE_IDENTITY()

and here's your code:

SELECT LAST_INSERT_ID()

As a further hint, here's my code again:

SELECT [B][U][COLOR="#FF0000"]ID =[/COLOR][/U][/B] SCOPE_IDENTITY()

 

[reyes]

No I got it, tried it, and it didn't work! I get 'Unknown column 'ID' in 'field list'. That's why I went down the route of trying 'CustomerID', which has the same thing, before trying what ended up working in my last post.

 

[jmcilhinney]

I think that I must have misread your previous post the first time as it seems to make perfect sense now. I guess the syntax I used is supported by SQL Server but not MySQL. I shouldn't be surprised that they're different. Glad you got it sorted.

 

[reyes]

I think that I must have misread your previous post the first time as it seems to make perfect sense now. I guess the syntax I used is supported by SQL Server but not MySQL. I shouldn't be surprised that they're different. Glad you got it sorted.

No problem at all. Your earlier pointer put me on to the correct path so thank you very much again for your help!


Sent from my SM-G935F using Tapatalk