0
Answer

retrieving @@IDENTITY and rows added to the datatable

giovanni

giovanni

14y
8k
1
Hi, there

I've a problem that is bothering me since yesterday. I hope somebody can help.

I'm developing an application using C#, visualstudio 2008, Database MS Access.
I'm using bound DataGridViews in a "Accesslike" way. It means adding a new row every time the user starts to edit the "current" new row. This carries the well known ID problem. Following the Microsoft instructions (http://support.microsoft.com/kb/815629)I've added the RowUpdated event retrieving the ID via select @@identity.
Everything seems to work fine, however there is an exception. If I click in the current newrow to register the last added record the rows inserted are two. If I click in any other previously stored row, everything works fine.

Here is the code I' using

saveData()
                        DataTable dataChanges = this.dSetDBPresetHeader.DB_Preset.GetChanges();
                        OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT ID FROM DB_Preset",
                                                                        clGlobal.m_cnADONetConnection);
                        adapter.InsertCommand = new OleDbCommand("INSERT INTO DB_Preset (sPresetCode) Values(?)",
                                                                 clGlobal.m_cnADONetConnection);
                        adapter.InsertCommand.CommandType = CommandType.Text;
                        adapter.InsertCommand.Parameters.Add("@sPresetCode", OleDbType.VarWChar, 50, "sPresetCode");
                        adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.Both;

                        adapter.RowUpdated += new OleDbRowUpdatedEventHandler(OnRowUpdated);
                        adapter.Update(dataChanges);
                       
                        this.dSetDBPresetHeader.DB_Preset.Merge(dataChanges);
                        this.dSetDBPresetHeader.DB_Preset.AcceptChanges();

OnRowUpdated()
            if (e.StatementType == StatementType.Insert)
            {
                OleDbCommand cmdNewID = new OleDbCommand("SELECT @@IDENTITY",
                                                         clGlobal.m_cnADONetConnection);
                // Retrieve the Autonumber and store it in the ID column.
                e.Row["ID"] = (int)cmdNewID.ExecuteScalar();
                e.Status = UpdateStatus.SkipCurrentRow;
            }

BPresetBindingSource_PositionChanged()

            BindingSource thisBindingSource = (BindingSource)sender;
            if ((DataRowView)thisBindingSource.Current != null)
            {
                DataRow thisDataRow = ((DataRowView)thisBindingSource.Current).Row;

                // Now Update the EWDB
                SavePresetDataSet();

                // Store the last data row for the next event
                m_LastDataRow = thisDataRow;
            }

Thanks in advance for the help
Ciao
Giovanni

Next Recommended Forum