Bug #17121 System.Data.NoNullAllowedException when adding row with auto_increment field

• Submitted: 4 Feb 2006 16:00
• Modified: 1 Sep 2009 14:14
• Reporter: Jan Bouwhuis
• Status: Not a Bug
• Category: Connector / NET
• Severity: S4 (Feature request)
• Version: 1.07
• OS: Windows (Windows or Linux/Mono 1.1.13)
• CPU Architecture: Any

[4 Feb 2006 16:00] Jan Bouwhuis

Description:
When adding a row to a table the auto_increment in a field should be filled by the server. When running the code on MS .NET there is no problem. When using Mono 1.1.13 a System.Data.NoNullAllowedException is thrown.

How to repeat:
Create a database:

create database if not exists `testdb`;

USE `testdb`;

CREATE TABLE `testtable` (
  `id` int(4) NOT NULL auto_increment,
  `testfield` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Compile the following code (I used VS 2002):

using System;
using System.Data;
using MySql.Data.MySqlClient;

namespace DBAddRecord
{
	class testapp
	{
		[STAThread]
		static void Main(string[] args)
		{
			//
			// test code
			//
      try 
      {
        MySqlConnection MyCon = new MySqlConnection();
        MyCon.ConnectionString = "server=localhost;database=testdb;UID=userid;pwd=password;PORT=3306;";
        MyCon.Open();
        MySqlDataAdapter MyAdapter = new MySqlDataAdapter("select id, testfield from testtable", MyCon); //id, 
        MySqlCommandBuilder MyDataRowsCommandBuilder = new MySqlCommandBuilder(MyAdapter);
        DataSet MyDataSet = new DataSet();
        DataRow MyNewRow;
        MyAdapter.Fill(MyDataSet);

        MyNewRow = MyDataSet.Tables[0].NewRow();

        MyNewRow["testfield"] = DateTime.Now.ToString();
        MyDataSet.Tables[0].Rows.Add(MyNewRow);
        MyAdapter.Update(MyDataSet);

        MyDataSet.Dispose();
        MyAdapter.Dispose();
        MyDataRowsCommandBuilder.Dispose();

        MyCon.Close();
        Console.WriteLine("Record added");
      } 
      catch(System.Exception e) 
      {
        Console.WriteLine("Error: " + e.Message);
      }
  }
 }
}

When using Mono the error is generated. When the ID-field is assigned to a higher ID, there is no error.

Suggested fix:
Pass the correct query to the SQL-server when using Mono, so that adding records with an auto_increment field works.

[7 Feb 2006 9:59] Jan Bouwhuis

Found a work-a-round by passing a value of 0 to the auto_incremented instead of System.DBNull.Value. Tested both on Windows and Mono. This seems te be working correctly.

[1 Sep 2009 14:14] Tonci Grgin

This is actually not a bug. Please check MS docs on autoincrement and adjust proper properties such as:
          (dt.Columns["Col1"]).AutoIncrement = true;
                              .AutoIncrementSeed = -1;
                              .AutoIncrementStep = -1;