Concurrency Nightmare

[Malenfant]

I've recently made the switch to C# from vb6 and although loving it
I'm plagued by concurrency problems when updating new recordsets. I've
solved prior problems by using the FillSchema command and changed the
database to make things harder to fall apart but I'm still having the
same core problem.

Whenever I create a new datarow and add it to the table the very next
edit on that same datarow causes a concurrency violation. I've scoured
the net for answers but none have illuminated the problem.

To making helping me as easy as possible I've created a stripped down
example of the problem in a seperate project, this contains just 2
buttons, one to create the new record, one to change the name column.
The database behind it is Access 2000 in this case and the underlying
table could be as simple as just ID,name.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

namespace concurrancyError
{
public partial class Form1 : Form
{
private string customerConnectionString;
private OleDbConnection customerConnect;

private string billingrunSQL;
private OleDbDataAdapter billingRunAdapter;

public DataSet billingRuns;

public DataRow billingRunDataRow;

public Form1()
{
InitializeComponent();
customerConnectionString = "Provider=Microsoft.Jet.OLEDB.
4.0;Data Source = c:\\testing\\test.mdb";
customerConnect = new
OleDbConnection(customerConnectionString);

billingrunSQL = "SELECT * from billingrun";
billingRunAdapter = new OleDbDataAdapter(billingrunSQL,
customerConnect);
OleDbCommandBuilder billingRunBuilder = new
OleDbCommandBuilder(billingRunAdapter);

billingRuns = new DataSet();
billingRunAdapter.FillSchema(billingRuns,
SchemaType.Mapped, "billingrun");
billingRunAdapter.Fill(billingRuns, "billingrun");
}

private void button1_Click(object sender, EventArgs e)
{
billingRunDataRow =
billingRuns.Tables["billingrun"].NewRow();

billingRuns.Tables["billingrun"].Rows.Add(billingRunDataRow);
billingRunDataRow["name"] = "pre foo";

billingRunAdapter.Update(billingRuns.Tables["billingrun"]);
}

private void button2_Click(object sender, EventArgs e)
{
billingRunDataRow["name"] = "foo";
//This is where it crashes.

billingRunAdapter.Update(billingRuns.Tables["billingrun"]);
}
}
}

Any help would be greatly appreciated.

 

[Malenfant]

The database behind it is Access 2000 in this case and the underlying

table could be as simple as just ID,name.

Ah, spotted an assumption of mine there, the problem doesn't happen
when it's just ID,name. In fact the column that seems to be causing
the problem is a boolean datatype (called Yes/No in access). New
records default to false but either way you set it in the code it
still fails.

 

[Nicholas Paldino [.NET/C# MVP]]

Honestly, I looked at the command generated by the OleDbCommandBuilder
for the update statement, and it's crap. It does the best it can with
limited information.

You really should be crafting an update command on your own (or at least
build it through the designer) so that you are in control of concurrency
checks (if you want them) and using a primary key to identify rows (if you
want). You didn't specify what the primary key should be (I assume it is
the id field) either, and whether or not the name needs to be unique.

It's because of all these things that the command builders are generally
a bad idea.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I've recently made the switch to C# from vb6 and although loving it
I'm plagued by concurrency problems when updating new recordsets. I've
solved prior problems by using the FillSchema command and changed the
database to make things harder to fall apart but I'm still having the
same core problem.

Whenever I create a new datarow and add it to the table the very next
edit on that same datarow causes a concurrency violation. I've scoured
the net for answers but none have illuminated the problem.

To making helping me as easy as possible I've created a stripped down
example of the problem in a seperate project, this contains just 2
buttons, one to create the new record, one to change the name column.
The database behind it is Access 2000 in this case and the underlying
table could be as simple as just ID,name.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

namespace concurrancyError
{
public partial class Form1 : Form
{
private string customerConnectionString;
private OleDbConnection customerConnect;

private string billingrunSQL;
private OleDbDataAdapter billingRunAdapter;

public DataSet billingRuns;

public DataRow billingRunDataRow;

public Form1()
{
InitializeComponent();
customerConnectionString = "Provider=Microsoft.Jet.OLEDB.
4.0;Data Source = c:\\testing\\test.mdb";
customerConnect = new
OleDbConnection(customerConnectionString);

billingrunSQL = "SELECT * from billingrun";
billingRunAdapter = new OleDbDataAdapter(billingrunSQL,
customerConnect);
OleDbCommandBuilder billingRunBuilder = new
OleDbCommandBuilder(billingRunAdapter);

billingRuns = new DataSet();
billingRunAdapter.FillSchema(billingRuns,
SchemaType.Mapped, "billingrun");
billingRunAdapter.Fill(billingRuns, "billingrun");
}

private void button1_Click(object sender, EventArgs e)
{
billingRunDataRow =
billingRuns.Tables["billingrun"].NewRow();

billingRuns.Tables["billingrun"].Rows.Add(billingRunDataRow);
billingRunDataRow["name"] = "pre foo";

billingRunAdapter.Update(billingRuns.Tables["billingrun"]);
}

private void button2_Click(object sender, EventArgs e)
{
billingRunDataRow["name"] = "foo";
//This is where it crashes.

billingRunAdapter.Update(billingRuns.Tables["billingrun"]);
}
}
}

Any help would be greatly appreciated.

 

[Malenfant]

Honestly, I looked at the command generated by the OleDbCommandBuilder
for the update statement, and it's crap. It does the best it can with
limited information.

You really should be crafting an update command on your own (or at least
build it through the designer) so that you are in control of concurrency
checks (if you want them) and using a primary key to identify rows (if you
want). You didn't specify what the primary key should be (I assume it is
the id field) either, and whether or not the name needs to be unique.

It's because of all these things that the command builders are generally
a bad idea.

Ok I've tried this and I'm still falling afoul of the concurrency
problem. This time however I've noticed that the ID value of the newly
created record is 0 rather than whatever it should be (usually 1 if
I've reset the autonumber). If I create the datarow based off an
existing record the ID is correct and thus no problems occur, it only
seems to happen on new records.