Bug #37865 TableAdapter.Update(DataTable) does not refresh the Primary Key for new Rows
Submitted: 4 Jul 2008 10:18 Modified: 25 Aug 2009 16:21
Reporter: Cyrille Giquello Email Updates:
Status: Won't fix Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:5.2. latest OS:Windows
Assigned to: Reggie Burnett CPU Architecture:Any
Tags: primary key, UPDATE

[4 Jul 2008 10:18] Cyrille Giquello
Description:
Hello,

TableAdapter.Update(DataTable) does not refresh the Primary Key of just added rows.
If I call TableAdapter.Fill(DataTable) before Updates, it's work, but I do not want to retreive hundred of rows before add one or two rows.

Environment :
Mysql 5.0.41
Connector/Net 5.2.2
VisualStudio 2005
Windows Vista

How to repeat:
// DROP TABLE IF EXISTS `scrutalysconfig`.`tabletest`;
// CREATE TABLE  `scrutalysconfig`.`tabletest` (
//  `id` int(11) NOT NULL auto_increment,
//  `key` varchar(45) default NULL,
//  PRIMARY KEY  (`id`)
//) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tabletestTableAdapter ta = new tabletestTableAdapterNormal();
tabletestDataTable dt = new tabletestDataTable();

//Debug.WriteLine("Before fill : " + dt.Rows.Count);
//ta.Fill(dt);
//Debug.WriteLine("After fill : " + dt.Rows.Count);

tabletestRow row = dt.AddtabletestRow("1");
Debug.WriteLine("Pass#1 before update : row.id=" + row.id + ", RowState=" + row.RowState);
ta.Update(dt);
Debug.WriteLine("Pass#1 after update : row.id=" + row.id + ", RowState=" + row.RowState);

TableTestDataSet.tabletestRow row2 = dt.AddtabletestRow("2");
Debug.WriteLine("Pass#2 before update : row2.id=" + row2.id + ", RowState=" + row2.RowState);
ta.Update(dt);
Debug.WriteLine("Pass#2 after update : row2.id=" + row2.id + ", RowState=" + row2.RowState);
[4 Jul 2008 10:58] Cyrille Giquello
It seems the problem come from the VisualStudio Plugin.

In the Advanced Tab of TableAdapter configuration, the option "Actualiser la table de données" (Translate: Update Data) seems to not work.
[4 Jul 2008 12:24] Coudray Jean-Sébastien
Same thing for me. You can use MySQLCommandBuilder, but the first autoincrement id rows is unchanged, and not updated by the autoincrement value of server. 

If you checked ReturnGeneratedIdentifiers a bug appear, if ReturnGeneratedIdentifiers is true, UpdatedRowSource must be set to none...

MySql.Data.MySqlClient.MySqlCommandBuilder cb = new MySql.Data.MySqlClient.MySqlCommandBuilder(Adapter);
cb.ReturnGeneratedIdentifiers = true;

Source :
http://library.pantek.com/Applications/MySQL/doc/refman/5.0/en/connector-net-using-faq.htm...
[17 Jul 2008 12:40] Tonci Grgin
Hi Cyrille and thanks for your report.

I will quote our c/NET team leader, Mr. Reggie Burnett, here:
--<quote>--
Retrieving autoincrement field values
Often people ask me how to retrieve the value of an autoincrement column when they are using the MySqlCommandBuilder object.  Generally this is done by appending something like ";SELECT last_insert_id()" onto the generated insert command text.  Command builder objects usually provide some type of syntax where a user can indicate that they want this to be done.  Connector/Net 1.0 had some syntax for this but starting with Connector/Net 5.0 that all changed. 

With the new ADO.Net 2.0 API, Microsoft did not directly provide for any type of API that users could use for selecting this.  With ADO.Net 2.0, there are two things that need to happen.  First, a SELECT statement similar to above must be attached to the INSERT command.  Second, the command object must have its UpdatedRowSource property set to Both or FirstReturnedRecord.  What this does is tell the data adapter update engine to take the first returned row and merge it's values back into the changed row.  So, if the first returned row is the result of a 'SELECT last_insert_id()', then the generated key will be returned and merged back in.  Good, huh?

Not so fast.  The UpdatedRowSource property exists on the command object.  The whole point of using MySqlCommandBuilder is to not mess with command objects.  Everything should be handled automatically with as little code as possible.  To this end, we've added a custom property to MySqlCommandBuilder called ReturnGeneratedIdentifiers.  By setting this property to true, the proper select statement will be included and the generated command object will have its UpdatedRowSource property set.
--<quote>--

Does this explanation solves your problem?
[22 Jul 2008 9:33] Coudray Jean-Sébastien
Hello Tonci, 

I have a similar problem, but it seems to be a bug from your code. 

mySQLCommandBuilder handle event (rowUpdating) and take ReturnGeneratedIdentifiers to update last increment value.
The problem is an exception is done if UpdateRowSource is not set to none. 

BUT you change after this exception UpdateRowSource to FirstReturnedRecord so why putting this exception if you change the value after? With this bug we must put UpdateRowSource to none at each time before updating rows.

// CommandBuilder.cs, MySQL.Data, l.269
// Connector /net 5.2.2
private void RowUpdating(object sender, MySqlRowUpdatingEventArgs args)
        {
            base.RowUpdatingHandler(args);

            if (args.StatementType != StatementType.Insert) return;

            if (ReturnGeneratedIdentifiers)
            {
                if (args.Command.UpdatedRowSource != UpdateRowSource.None)
                    throw new InvalidOperationException(
                        Resources.MixingUpdatedRowSource);
                args.Command.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
                if (finalSelect == null)
                    CreateFinalSelect();
            }

 ....
        }
[5 Aug 2008 7:45] Cyrille Giquello
Hello,
I'm coming back to get some news about this problem.

Here is the resume of the problem :
The first call to TableAdapter.Update(DataTable) does not refresh the Primary Key of just added rows : if I read the DataTable after the Update, Primary Keys values are false.
If I call TableAdapter.Fill(DataTable) before Updates, it's work, but I do not want to retreive hundred of rows before add one or two rows.

Environment :
Mysql 5.0.41 with InnoDB table.
Connector/Net 5.2.2
VisualStudio 2005
Windows Vista
[8 Aug 2008 11:37] Tonci Grgin
Cyrille, agree. Same code present in latest sources too... Would like to know explanation.

Making a test case to prove your point.
[8 Aug 2008 12:05] Cyrille Giquello
Hi Tonci,

I'm writing a test case. But I think the simple explanation is in the "Advanced option" of the TableAdapter configuration dialog.
The third checkbox "Actualiser les données" (Update data after Insert or Update) is not remembered.

To get auto incremented value updated in the datatable after used tableadapter.Update(datatable), this option should be checked.
But when I check it, then close all dialogs (OK, Terminate) and reopen the dialog, the checkbox is not checked anymore.

So, I think the bug is not in the TableAdapter runtime code, but in the TableAdapter design time.

Regards,
Cyrille.
[8 Aug 2008 12:58] Cyrille Giquello
Here is a testCase :
   http://oueb.org/mysql.bug.37865/

Tell me what to add or precise.

Regards
cyrille
[13 Nov 2008 11:19] Cyrille Giquello
Is the same (duplicate) as bug#23082
http://bugs.mysql.com/bug.php?id=23082
[15 Jan 2009 13:12] dolzenko dolzenko
Pretty unfortunate issue as it breaks all the basic MSDN sample applications like http://msdn.microsoft.com/en-us/library/ms171884.aspx
[29 Apr 2009 16:19] Jason White
I've struggled many times with this bug, and I can confirm that it is present in 6.0.3 as well.  Has there been any progress on this front, or is there some workaround?  This is a basic functionality issue here.

TableAdapters in the designer are completely useless for anything more than SELECT statements, until this is fixed.
[29 Apr 2009 16:42] dolzenko dolzenko
Well, it's a horrible kludge, but I've done the whole project this way, and it seem to be working:

public static void FixTableAdapterInsertCommand(MySqlDataAdapter adapter)
{
const string finalSelect = "; SELECT last_insert_id() AS `id`";
if (adapter.InsertCommand.CommandText.EndsWith(finalSelect))
  throw new ArgumentException("adapter.InsertCommand is already fixed");

adapter.InsertCommand.CommandText += finalSelect;
adapter.InsertCommand.UpdatedRowSource = global::System.Data.UpdateRowSource.FirstReturnedRecord;
}

Then use like this:

FixTableAdapterInsertCommand(moneyFlowTableAdapter.Adapter);

where moneyFlowTableAdapter is an instance of typed TableAdapter.
[29 Apr 2009 17:14] Jason White
That worked for me, but only when the column that was autoincremented was named 'id'.  Here's my version, in VB.NET:

    Public Shared Sub FixDataAdapterInsertCommand(ByVal adapter As MySqlDataAdapter, ByVal autoIncrementColumn As DataColumn)

Dim finalSelect As String = ";SELECT last_insert_id() AS '" & autoIncrementColumn.ColumnName & "'"
   If adapter.InsertCommand.CommandText.IndexOf("last_insert_id()") >= 0 Then
       Throw New ArgumentException("adapter.InsertCommand is already fixed")
   End If

   adapter.InsertCommand.CommandText &= finalSelect
   adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord
End Sub

Bit of a hack, but as long as I fix the tableadapters this way, they do function correctly.  The option to configure the tableadapters to refresh after inserting doesn't seem to do anything. I'm using VS2008, Windows XP, MySQL Connector/NET 6.0.3.
[20 Jun 2009 11:12] Chuck Haeberle
How did you access the TableAdapater.Adapter property?  When created in the designer, the Adapter Property is being declared as private.
[20 Jun 2009 13:39] Jason White
Two words: partial classes.
[20 Jul 2009 6:20] Tonci Grgin
Bug#23082 was marked as duplicate of this report.
[20 Jul 2009 7:52] Tonci Grgin
I'd go with Cyrille here...
>> To get auto incremented value updated in the datatable after used tableadapter.Update(datatable), this option should be checked. But when I check it, then close all dialogs (OK, Terminate) and reopen the dialog, the checkbox is not checked anymore. So, I think the bug is not in the TableAdapter runtime code, but in the TableAdapter design time.

Verified as described using VS2005Pro, latest 5.2 sources and 6.0.4/5.2.7 plug-ins.
[24 Aug 2009 22:51] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/81461

709 Reggie Burnett	2009-08-24
      - adding a pretty nasty hack to enable output of a refresh select on the end of our insert
        statements when they are generated by the TableAdapter config wizard (bug #37865)
[24 Aug 2009 23:01] Reggie Burnett
fixed in 5.2.8, 6.0.5, and 6.1.2+

This is not really a good "fix" but a hack that should help.  The problem is that the base class DbCommandBuilder doesn't expose the command building mechanism as virtual so you can't hook into it.  It does not attach the final select statement.  Also, the table adapter config wizard is hard coded to only add a final select when dealing with Sql Server.

So, in order to help with this, I added a simple hack.  Basically, DbCommandBuilder will call InitializeCommand with a null parameter when the commands are being generated.  If that parameter is null, then I assume we are generating new commands. At that point I create the final select and stash it into an internal field on the command object.  When DbCommandBuilder assigns the commandtext, I check if it starts with 'INSERT INTO' and, if it does, attach the final select.  It should work ok in most simple scenarios.

I hope everyone understands that quite a bit in Visual Studio is hard coded to Sql Server and we do our best to make it work with MySQL.
[25 Aug 2009 16:13] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/81532

710 Reggie Burnett	2009-08-25
      removing fix for bug #37865.  That was going to cause other problems.  I've decided to go a different way in 6.1
[25 Aug 2009 16:16] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/81533

750 Reggie Burnett	2009-08-25 [merge]
      removing fix for bug #37865.  That was going to cause other
      problems.  I've decided to go a different way in 6.1
[25 Aug 2009 16:21] Reggie Burnett
Rolled this fix out as it was causing some problems.  We will be removing the ReturnIdentifiers property in 6.1 and moving to a more SqlClient way of doing things.  If people like what we do with 6.1 then we will consider backporting this to 5.2 and 6.0.  We are not making this change in 5.2 and 6.0 now as we don't like making feature changes in GA releases.

Please email or post in forums if you disagree with this approach.
[4 Feb 2010 9:57] Jean-Luc Barraud
This bug still existing with VS 2008, MySQL.data 6.2.2.0 .
[9 Apr 2010 7:05] Tonci Grgin
Test cases

Attachment: Bug37865_2.txt (text/plain), 3.17 KiB.

[9 Apr 2010 7:05] Tonci Grgin
Jean-Luc, what actually does not work? I have attached test case and you can find more in c/NET sources.
[7 Sep 2010 9:45] D Schausb
Will this bug be solved, or has it been solved yet in any newer stable or developer version?
[15 May 2013 5:53] Mandar Damodare
Is it fixed yet? I still facing prob VS2008 and connector-net-6.6.5.
or any patch ?