Bug #59001 Primary key field not updated when a new record is created in a datagridview
Submitted: 17 Dec 2010 14:36 Modified: 24 Jun 2011 11:17
Reporter: Anita Maxwell Email Updates:
Status: Won't fix Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.3.5, 6.3.6 OS:Microsoft Windows (7)
Assigned to: Fernando Gonzalez.Sanchez
Tags: regression

[17 Dec 2010 14:36] Anita Maxwell
Description:
Scenario
Using Visual Studio 2010 to create a screen with a datagridview. 
The datasource of the datagridview is set to a MySQL table using the .Net Connector 6.3.5. 
The table has a primary key 

Problem
When using previous versions of the connector all you had to do was set the primary key's default value to zero and the connector/Visual Studio took care of setting the actual key value as soon as the user added a new row to the datagridview.

Now the value in the datatgridview remains at zero so that when you try to add a second row you get a constraint error because 2 rows in the datagridview cannot both contain a zero value.

Note that this error occurs before you even try to update the actual table.

How to repeat:
Create a new screen using Visual Studio 2010 and add a datagridview to it.
Set the datasource of the datagridview to a table containing a primary key (auto-increment).

Add a subroutine to set the default value of this column to zero as follows:-

        Private Sub DataGridView1_DefaultValuesNeeded(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowEventArgs) Handles DataGridView1.DefaultValuesNeeded
        With e.Row
            .Cells("IDDataGridViewTextBoxColumn").Value = 0
        End With
    End Sub

As soon as you try to enter 2 new rows you get an error about duplicate values.

Suggested fix:
This worked in earlier versions of the connector e.g. 6.0.2. (using Visual Studio 2005)
[9 Feb 2011 14:22] Valerii Kravchuk
Please, check if you'll see the same problem with a newer version, 6.3.6.
[21 Feb 2011 18:52] Anita Maxwell
Tried to install 6.3.6 - had to uninstall 6.3.5 first.
6.3.6 appeared to install OK but ended up with 102 errors in my application similar to :-
'MySql.Data.MySqlClient.MySqlConnection' is not defined.
Assumed this was because 6.3.6 was not added as a reference - tried to do this but it was not in the list of available references.
Uninstalled 6.3.6 and installed 6.3.5 again. Still have the same errors in my application.

Windows 7 system restore to a time prior to uninstalling 6.3.5 - still have the same 102 errors.

PLEASE HELP - how do I get back to where I was before trying to do the upgrade to 6.3.6? How do I get rid of all these error messages?
I think I need to get MySQL.Data into the list of .NET available messages but I'm not sure how to do this?
[22 Feb 2011 10:46] Anita Maxwell
OK, after may panic yesterday, I reinstalled 6.3.6 and when I changed the version of .NET framework that my project was targetting from 2 to 4 I could add the reference to MySQL and the errors went away and I was able to run the project.

QUESTION - should 6.3.6 only be available with version 4 of .Net Framework?

I'm not sure how I had 6.3.5 working with version 2 of .Net framework on this project previously as I cannot get it to work that way now.

Anyway, back to the actual bug report.....

The problem still exists with version 6.3.6. If you try and add more than one row into a datagrid as soon as you go into the third row it throws an contraint error as two rows cannot have a zero value in their primary key.
[21 Jun 2011 17:16] Fernando Gonzalez.Sanchez
Hi I was testing this, and after some troubleshooting the issue is reproducible with both MSSQL & MySQL, so It looks like it is by design.

Below details on how to reproduce:

On MySQL, you can create a table as follow.
CREATE TABLE `animals` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `name` char(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1$$

On MSSQL you can use something like

CREATE TABLE [dbo].[animals](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [char](30) NOT NULL
) ON [PRIMARY]

GO

alter table animals add constraint PK_animals primary key ( id )
go

The last line "alter table... add contraint" is critical.
If you don't add it, you will not get the exception behavior (not adding the constraint basically implies that DataReader.GetSchemaTable returns IsKey=False for Id column, and so the typed dataset doesn't get the UniqueConstraint in the respective DataTable).

The UniqueConstraint is charge of throwing the exception in ADO.NET code in UniqueConstraint.CheckConstraint 
following this stack trace
System.Data.dll!System.Data.UniqueConstraint.CheckConstraint(System.Data.DataRow row, System.Data.DataRowAction action) Line 116 + 0x84 bytes	C#
 	System.Data.dll!System.Data.DataTable.RaiseRowChanging(System.Data.DataRowChangeEventArgs args, System.Data.DataRow eRow, System.Data.DataRowAction eAction, bool fireEvent = true) Line 3038 + 0x13a bytes	C#
>	System.Data.dll!System.Data.DataTable.SetNewRecordWorker(System.Data.DataRow row = {Bug_59001_MSSQL.testDataSet.animalsRow}, int proposedRecord = 9, System.Data.DataRowAction action = Add, bool isInMerge, int position = -1, bool fireEvent = true, out System.Exception deferredException = null) Line 4790 + 0x1b bytes	C#
 	System.Data.dll!System.Data.DataTable.InsertRow(System.Data.DataRow row = {Bug_59001_MSSQL.testDataSet.animalsRow}, long proposedID = 10, int pos, bool fireEvent) Line 2180 + 0x33 bytes	C#
 	System.Data.dll!System.Data.DataView.FinishAddNew(bool success) Line 448 + 0x1a bytes	C#
 	System.Data.dll!System.Data.DataRowView.EndEdit() Line 70 + 0xe bytes	C#

Given this evidence it is probably a bug to manually set the autoid column with:
private void dataGridView1_DefaultValuesNeeded(object sender, DataGridViewRowEventArgs e)
{
       e.Row.Cells["IDDataGridViewTextBoxColumn"].Value = 0;
}

If you don't do it, you can later call DataAdapter.Update, and will work fine.
That is probably the best solution

Another WA is to manually remove the primary key in the Form_Load after Fill call with:
this.animalsTableAdapter.Fill(this.testDataSet.animals);
this.testDataSet.Tables[0].PrimaryKey = null;
[21 Jun 2011 17:18] Fernando Gonzalez.Sanchez
added details on how to reproduce in both MySQL & MSSQL.
[21 Jun 2011 17:38] Fernando Gonzalez.Sanchez
Oh, and Connector/NET 6.3.6 is supported in .NET 3.5/VS2008 as well.

> QUESTION - should 6.3.6 only be available with version 4 of .Net Framework?
[24 Jun 2011 11:17] Anita Maxwell
Hi,

Thank you for your feedback.

I have tried not setting the value of the primary key column as suggested and just get an error saying that this column cannot be null.

Changing the column in the dataset designer AllowDBNull = True does not make any difference. 

I have tried creating a brand new form and just adding a datagridview to it that is bound to my table (no "hand written code") and still get the same error:-
System.Data.NoNullAllowedException: Column 'ID' does not allow nulls.

This error occurs as soon as I try to move onto the second line of the datagridview (having started with an empty table). It is before any attempt to update the database (in fact as stated above there is no code on the form to update the database).

I'm obviously missing something as you appear to be able to get this to work and would be grateful if you could point me in the right direction as my "workaround" involves quite a bit of code that I'd like to do without.

Thanks

Anita