none
TableAdapterManager failing with MySQL DataSource RRS feed

  • Question

  • Hi all

    I have two tables: tblCustomers, tblOrders as part of a test scenario.

    tblCustomers:

    • CustomerID
    • Name

    tblOrders:

    • OrderID
    • CustomerID

    If I were using SQL server, as I have in the past, I could use this code if I wanted to add a new customer, and a new order at the very same time:

                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);

    This particular row -- ordersRow.CustomerID = custRow.CustomerID -- allowing me to grab the ID of the inserted row to pass to a child table.

    When testing this with a MySQL database, I keep getting this error:

    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)'

    I have checked the relations in my dataset and set them to cascade for updates/deletes with 'Both Relation and Foreign Key' selected.

    I have even tried adding SELECT LAST_INSERT_ID() at the end of the insert statement generated, but to no avail.

    Before I give up on table adapters and write my own, I was hoping to find out whether this is a known issue/limitation, or whether I'm just missing something.

    Any help appreciated!


    Thursday, June 21, 2018 1:19 PM

Answers

  • Hi Zhanglong

    I have it working now.

    In my insert statement, after the actual insert code, I added:

    SELECT CustomerID FROM tblCustomers WHERE CustomerID = LAST_INSERT_ID();

    Before this, and without success, I had tried 'SELECT ID = LAST_INSERT_ID();', then 'SELECT CustomerID = LAST_INSERT_ID();', but these both failed with 'Unknown column 'ID' in 'field list'.

    Thankfully, either adding this line to the generated SQL or using my own stored procedures, it works perfectly with the table adapter manager.

    I'll mark this as the answer.

    Thank you for your help.

    • Marked as answer by reyreyreyes Monday, June 25, 2018 8:52 AM
    Monday, June 25, 2018 8:52 AM

All replies

  • Hi reyreyreyes,

    According to your description and related error message, it seems that you does not set cascade delete and cascade update on your database table. like this:

    [CONSTRAINT [symbol]] FOREIGN KEY
        [index_name] (index_col_name, ...)
        REFERENCES tbl_name (index_col_name,...)
        [ON DELETE CASCADE]
        [ON UPDATE CASCADE]
    

    For more information, please refer to:

    https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 22, 2018 7:56 AM
    Moderator
  • Hi Zhanglong

    Thanks for replying.

    Already, both the database and dataset are configured in that way as shown in the query within the table inspector for tblOrders:

    CREATE TABLE `tblOrders` (
      `OrderID` int(11) NOT NULL AUTO_INCREMENT,
      `CustomerID` int(11) NOT NULL,
      PRIMARY KEY (`OrderID`),
      KEY `FK_Orders_Customers_idx` (`CustomerID`),
      CONSTRAINT `FK_Orders_Customers` FOREIGN KEY (`CustomerID`) REFERENCES `tblCustomers` (`CustomerID`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4

    Within the relation in the dataset, it is set to 'Both Relation and Foreign Key Constraint' with Update and Delete Rules set to 'Cascade'.

    ... Stuck!

    Friday, June 22, 2018 10:46 AM
  • Hi Rey,

    Could you please share a complete sample via OneDrive, which could reproduce the issue.

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 25, 2018 8:24 AM
    Moderator
  • Hi Zhanglong

    I have it working now.

    In my insert statement, after the actual insert code, I added:

    SELECT CustomerID FROM tblCustomers WHERE CustomerID = LAST_INSERT_ID();

    Before this, and without success, I had tried 'SELECT ID = LAST_INSERT_ID();', then 'SELECT CustomerID = LAST_INSERT_ID();', but these both failed with 'Unknown column 'ID' in 'field list'.

    Thankfully, either adding this line to the generated SQL or using my own stored procedures, it works perfectly with the table adapter manager.

    I'll mark this as the answer.

    Thank you for your help.

    • Marked as answer by reyreyreyes Monday, June 25, 2018 8:52 AM
    Monday, June 25, 2018 8:52 AM