add row to a BindingSource gives different autoincrement value from whats saved into DB

Question

I have a DataGridView that shows list of records and when I hit a insert button, a form should add a new record, edit its values and save it.

I have a BindingSource bound to a DataGridView. I pass is as a parameter to a NEW RECORD form so

// When the form opens it add a new row and de DataGridView display this new record at this time
DataRowView currentRow;
currentRow = (DataRowView) myBindindSource.AddNew();

when user confirm to save it I do a

myBindindSource.EndEdit(); // inside the form

and after the form is disposed the new row is saved and the bindingsorce position is updated to the new row

DataRowView drv = myForm.CurrentRow;
avaliadoTableAdapter.Update(drv.Row);
avaliadoBindingSource.Position = avaliadoBindingSource.Find("ID", drv.Row.ItemArray[0]);

The problem is that this table has a AUTOINCREMENT field and the value saved may not correspond the the value the bindingSource gives in EDIT TIME.

So, when I close and open the DataGridView again the new rowd give its ID based on the available slot in the undelying DB at the momment is was saved and it just ignores the value the BindingSource generated ad EDIT TIME,

Since the value given by the binding source should be used by another table as a foreingKey it make the reference insconsistent.

There's a way to get the real ID was saved to the database?

Answer 1

The same thing happens with Access databases. There is a great article (with solution) here. Basically, the TableAdapter normally sends 2 queries in a batch when you save the data. The first one saves the data and the second one asks for the new ID. Unfortunately, neither Access nor SQL CE support batch statements.

The solution is to add an event handler for RowUpdated that queries the DB for the new ID.

Answer 2

based on my answer on concurrency violation, use da.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord.

note: just change SQLiteConnection and SQLiteDataAdapter to MSSQL ones, and change the LAST_INSERT_ROWID() to SCOPE_IDENTITY()

    const string devMachine = @"Data Source=C:\_DEVELOPMENT\__.NET\dotNetSnippets\Mine\TestSqlite\test.s3db";

    SQLiteConnection c = new SQLiteConnection(devMachine);
    SQLiteDataAdapter da = new SQLiteDataAdapter();
    DataTable dt = new DataTable();

    public Form1()
    {
        InitializeComponent();


        da = new SQLiteDataAdapter("select product_id, product_name, abbrev from product", c);

        var b = new SQLiteCommandBuilder(da);

        da.InsertCommand = new SQLiteCommand(
            @"insert into product(product_id, product_name, abbrev) values(:_product_id, :_product_name, :_abbrev);
            select product_id /* include rowversion field here if you need */ 
            from product where product_id = LAST_INSERT_ROWID();", c);
        da.InsertCommand.Parameters.Add("_product_id", DbType.Int32,0,"product_id");
        da.InsertCommand.Parameters.Add("_product_name", DbType.String, 0, "product_name");
        da.InsertCommand.Parameters.Add("_abbrev", DbType.String, 0, "abbrev");
        da.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;

        da.UpdateCommand = b.GetUpdateCommand();
        da.DeleteCommand = b.GetDeleteCommand();

        da.Fill(dt);


        bds.DataSource = dt;
        grd.DataSource = bds;

    }

    private void uxUpdate_Click(object sender, EventArgs e)
    {
        da.Update(dt);
    }

here's the sample table on SQLite:

CREATE TABLE [product] (
[product_id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
[product_name] TEXT  NOT NULL,
[abbrev] TEXT  NOT NULL
)

[EDIT Nov 19, 2009 12:58 PM CN] Hmm... I guess my answer cannot be used, SQLCE does not allow multiple statements.

anyway, just use my answer when you use server-based MSSQL or if you use SQLite. or perhaps, encapsulate the two statements to a function that returns scope_identity(integer):

da.InsertCommand = new SQLiteCommand(
@"select insert_to_product(:_product_id, :_product_name, :_abbrev) as product_id", c);
da.InsertCommand.Parameters.Add("_product_id", DbType.Int32,0,"product_id");
da.InsertCommand.Parameters.Add("_product_name", DbType.String, 0, "product_name");
da.InsertCommand.Parameters.Add("_abbrev", DbType.String, 0, "abbrev");
da.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;

Answer 3

note: just change SQLiteConnection and SQLiteDataAdapter to MSSQL ones, and change the LAST_INSERT_ROWID() to SCOPE_IDENTITY()

use RowUpdated (shall work on SQLCE and RDBMS that doesn't support multi-statements):

const string devMachine = @"Data Source=C:\_DEVELOPMENT\__.NET\dotNetSnippets\Mine\TestSqlite\test.s3db";

SQLiteConnection c = new SQLiteConnection(devMachine);
SQLiteDataAdapter da = new SQLiteDataAdapter();
DataTable dt = new DataTable();

public Form1()
{
    InitializeComponent();


    da = new SQLiteDataAdapter("select product_id, product_name, abbrev from product", c);

    var b = new SQLiteCommandBuilder(da);

    da.InsertCommand = b.GetInsertCommand();
    da.UpdateCommand = b.GetUpdateCommand();
    da.DeleteCommand = b.GetDeleteCommand();

    da.Fill(dt);


    da.RowUpdated += da_RowUpdated;


    bds.DataSource = dt;
    grd.DataSource = bds;



}

void da_RowUpdated(object sender, System.Data.Common.RowUpdatedEventArgs e)
{
    if (e.StatementType == StatementType.Insert)
    {
        int ident = (int)(long) new SQLiteCommand("select last_insert_rowid()", c).ExecuteScalar();
        e.Row["product_id"] = ident;
    }
}

private void uxUpdate_Click(object sender, EventArgs e)
{
    da.Update(dt);
}

Answer 4

I haven't had a chance to use SQLiteConnection class but I do used SQLConnection and SQLCommand class. SqlCommand has a method ExecuteScalar that return the value of the first row and first column of your t-sql statement. You can use it to return the Auto-Identity column. Also, in SQL Server 2005 there is a keyword named OUTPUT you may also check it too.

Answer 5

I've come across this: all you need to do is set your autoincrement seed to -1 and have it "increment" by -1 too. This way all your datarows will have unique ids that DON'T map to anything in the real database. If you're saving your data with a DataAdapter, then after the save your datarow and any other rows with a datarelation pointing to that id will be updated

Answer 6

I come up with this solution

First added a GetNextID() method directly to the table model:

SELECT     autoinc_next
FROM         information_schema.columns
WHERE     (table_name = 'Estagio') AND (column_name = 'ID')

and whener I need a new row to be added I do

 EstagioTableAdapter ta = new EstagioTableAdapter ();
 nextID = ta.GetNextID();

 row = (DataRowView)source.AddNew();
 row.Row["ID"] = nextID;
 (...)
 source.EndEdit();