Can't submit changes in C# to a database record with a null field

[polocar]

Hi all,
I'm preparing a C# program in which there is a form with some textboxes bound to the fields of a database.

The database is named "Persons", and consists of only 1 table named "Persons".
This table has 3 fields: "IDPerson", "NamePerson" and "AgePerson".

In my form I have positioned 3 textboxes bound to these fields, two buttons ("btnPrev" and "btnNext", to move to the previous and next record, setting the Position Property of the "cmPersons" CurrencyManager object) and other two buttons ("btnSave" and "btnCancel", to save and cancel the modifies of the current record).

I would like to give the possibility to leave the "AgePerson" field of a record empty (if for example the user doesn't know the age of the person).

The problem is this:

1) When the current record has the "AgePerson" field empty (so the txtAgePerson textbox is empty too), if I try to modify in any way the name or to write the age of the person, submitting the changes the compiler throws the exception "Concurrency violation" (if I close and re-open the program, the modifies don't appear)

2) When the current record has the "AgePerson" field not empty, if I cancel its value in the txtAgePerson textbox and I click on another textbox (txtNamePerson, for example), the old value of txtAgePerson textbox re-appears.

3) Indeed, if I try to modify the fields of a record with a non-null "AgePerson" value (and I don't write a null-value in the txtAgePerson textbox), when I submit the changes there is no problem (in practise, neither the old value nor the new one must be null).

In the database I have set the "AgePerson" field as "Number", with no value predefined and with not required value.
In the C# code I have written the statement

dtPersons.Columns["AgePerson"].AllowDBNull = true;

but the result didn't change.

Is there any way to resolve this problem?

There is also another thing (on the same argument) I'm not able to do:
I would like that, when I click on the "btnCancel" button, all the modifies I have made on the textboxes disappeared, and the old values would be shown again.
So, in the "btnCancel_Click" procedure, I have written the statement

cmPersons.CancelCurrentEdit();

(cmPersons" is the CurrencyManager object bound to the "Persons" table), but after I have clicked on the button, the new values still remain on the textboxes.
Is that statement correct or I'd better to change it (how?)

Thank you very much

P.S.: I have reported the C# code below: it's rather long, but the interesting parts are only "Initialize ADO.NET objects" and "btnSave_Click" and "btnCancel_Click".
I have posted the entire code in the case you wanted to try...

using System;
using System.Collections;
using System.Data;
using System.Data.OleDb;
using System.Data.Common;
using System.Drawing;
using System.Windows.Forms;

class SeparateMain
{
	public static void Main()
	{
		Application.Run(new Persons());
	}
}

class Persons : Form
{
	// ADO.NET objects
	OleDbConnection cn;
	OleDbDataAdapter daPersons;
	DataSet ds;
	DataTable dtPersons;
	CurrencyManager cmPersons;

	// form object (textboxes and buttons)
	Button btnSave, btnCancel, btnPrev, btnNext;
	TextBox txtIDPerson, txtNamePerson, txtAgePerson;


	public Persons()
	{
		/*******************************************/
		/****** Istantiate ADO.NET objects *********/
		/*******************************************/

		cn = new OleDbConnection();
		daPersons = new OleDbDataAdapter();
		ds = new DataSet();
		dtPersons = new DataTable();

		/*******************************************/
		/****** Istantiate form objects ************/
		/*******************************************/

		btnSave = new Button();
		btnCancel = new Button();
		btnPrev = new Button();
		btnNext = new Button();

		txtIDPerson = new TextBox();
		txtNamePerson = new TextBox();
		txtAgePerson = new TextBox();

		/*******************************************/
		/****** Initialize ADO.NET objects *********/
		/*******************************************/

		ds.DataSetName = "TablesSet";

		//  Initialize dtPersons
		dtPersons.TableName = "Persons";

		dtPersons.Columns.Add("IDPerson", typeof(long));
		dtPersons.Columns["IDPerson"].AutoIncrement = true;
		dtPersons.Columns["IDPerson"].AutoIncrementSeed = 1;
		dtPersons.Columns["IDPerson"].AutoIncrementStep = 1;
		
		dtPersons.Columns.Add("NamePerson", typeof(string));
		dtPersons.Columns["NamePerson"].AllowDBNull = true;

		dtPersons.Columns.Add("AgePerson", typeof(short));
		dtPersons.Columns["AgePerson"].AllowDBNull = true;

		dtPersons.PrimaryKey = new DataColumn[] {dtPersons.Columns["IDPerson"]};

		ds.Tables.Add(dtPersons);

		cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Persons.mdb";

		// Initialize daPersons
		string strSQL;
		DataTableMapping tblMap;
		DataColumnMapping colMap;
		OleDbParameterCollection pc;
		OleDbParameter param;

		strSQL = "SELECT IDPerson, NamePerson, AgePerson FROM Persons";

		tblMap = daPersons.TableMappings.Add("Table", "Persons");
		colMap = tblMap.ColumnMappings.Add("IDPerson", "IDPerson");
		colMap = tblMap.ColumnMappings.Add("NamePerson", "NamePerson");
		colMap = tblMap.ColumnMappings.Add("AgePerson", "AgePerson");

		daPersons.SelectCommand = new OleDbCommand(strSQL, cn);

		strSQL = "UPDATE Persons SET NamePerson = ?, AgePerson = ? WHERE IDPerson = ? AND Nameperson = ? AND AgePerson = ?";
		daPersons.UpdateCommand = new OleDbCommand(strSQL, cn);
		pc = daPersons.UpdateCommand.Parameters;
		pc.Add("NamePerson_New", OleDbType.Char, 50, "NamePerson");
		pc.Add("AgePerson_New", OleDbType.SmallInt, 0, "AgePerson");

		param = pc.Add("IDPerson_Orig", OleDbType.BigInt, 0, "IDPerson");
		param.SourceVersion = DataRowVersion.Original;
		param = pc.Add("NamePerson_Orig", OleDbType.Char, 50, "NamePerson");
		param.SourceVersion = DataRowVersion.Original;
		param = pc.Add("AgePerson_Orig", OleDbType.SmallInt, 0, "AgePerson");
		param.SourceVersion = DataRowVersion.Original;

		daPersons.Fill(ds, "Persons");


		// Initialize CurrencyManager object to bind textboxes to dtPersons' current record
		cmPersons = (CurrencyManager) BindingContext[ds, "Persons"];


		/*******************************************/
		/****** Initialize form objects ************/
		/*******************************************/

		this.Size = new Size(500, 500);
		this.CenterToScreen();

		btnSave.Parent = this;
		btnSave.Size = new Size(100, 30);
		btnSave.Location = new Point(10, 10);
		btnSave.Text = "Conferma";
		btnSave.Click += new EventHandler(btnSave_Click);

		btnCancel.Parent = this;
		btnCancel.Size = btnSave.Size;
		btnCancel.Location = new Point(btnSave.Left + btnSave.Width + 10, btnSave.Top);
		btnCancel.Text = "Annulla";

		btnPrev.Parent = this;
		btnPrev.Size = btnSave.Size;
		btnPrev.Location = new Point(btnCancel.Left + btnCancel.Width + 10, btnCancel.Top);
		btnPrev.Text = "Prec";
		btnPrev.Click += new EventHandler(btnPrev_Click);

		btnNext.Parent = this;
		btnNext.Size = btnSave.Size;
		btnNext.Location = new Point(btnPrev.Left + btnPrev.Width + 10, btnPrev.Top);
		btnNext.Text = "Succ";
		btnNext.Click += new EventHandler(btnNext_Click);


		txtIDPerson.Parent = this;
		txtIDPerson.Size = new Size(100, 20);
		txtIDPerson.Location = new Point(10, 50);
		txtIDPerson.DataBindings.Add("Text", ds, "Persons.IDPerson");

		txtNamePerson.Parent = this;
		txtNamePerson.Size = new Size(100, 20);
		txtNamePerson.Location = new Point(10, 80);
		txtNamePerson.DataBindings.Add("Text", ds, "Persons.NamePerson");

		txtAgePerson.Parent = this;
		txtAgePerson.Size = new Size(100, 20);
		txtAgePerson.Location = new Point(10, 110);
		txtAgePerson.DataBindings.Add("Text", ds, "Persons.AgePerson");
	}

	/*************************************************************/
	/***************** Event handlers methods ********************/
	/*************************************************************/

	private void btnSave_Click(object sender, EventArgs e)
	{
		try
		{
			cmPersons.EndCurrentEdit();
			daPersons.Update(dtPersons);
			dtPersons.Rows[cmPersons.Position].AcceptChanges();
		}
		catch (Exception ex)
		{
			Console.WriteLine(ex);
		}
	}

	private void btnCancel_Click(object sender, EventArgs e)
	{
		try
		{
			cmPersons.CancelCurrentEdit();
		}
		catch (Exception ex)
		{
			Console.WriteLine(ex);
		}
	}

	private void btnPrev_Click(object sender, EventArgs e)
	{
		cmPersons.Position--;
	}

	private void btnNext_Click(object sender, EventArgs e)
	{
		cmPersons.Position++;
	}
}