Datagridview and access autonumber

Question

Hi

I have two related master/detail datagridview controls. OnUpdated event I update ID field. For master DGV everything works ok, but for detail DGV ID field doesn't update (i mean not relation field, but detailID, masterID in detail control is updated by foreign key constraint). (BTW: I am using MS Access DB)

UzsakymoKunasID=DetailID

        protected void OnTblUzsakymoKunasRowUpdated(object sender, System.Data.OleDb.OleDbRowUpdatedEventArgs args)
        {
            // Include a variable and a command to retrieve the identity value from the Access database.
            int newID = 0;
            int oldID = 0;
            System.Data.OleDb.OleDbCommand idCMD = new System.Data.OleDb.OleDbCommand();
            //"SELECT @@IDENTITY", Connection);
            idCMD.CommandText = "SELECT @@IDENTITY";
            idCMD.Connection = tblUzsakymoKunasTableAdapter.Connection;

            //args.Status == UpdateStatus.Continue &&
            if (args.StatementType == System.Data.StatementType.Insert)
            {
                // Retrieve the identity value and store it in the DetailID column.
                newID = (int)idCMD.ExecuteScalar();
                oldID = (int)args.Row["UzsakymoKunasID"];
                args.Row["UzsakymoKunasID"] = newID;
                //uzklausosDataSet.tblUzsakymoKunas.
                //System.Windows.Forms.MessageBox.Show("Senas ID: " + oldID + "\r\nNaujasID: " + newID.ToString(), "Užsakymo kūnas.");
                args.Row.AcceptChanges();
            }
        }

Answer 1

BTW here is saving method.

I try debugging method. newUzklausaKunas has new ID value, but after: tblUzsakymoKunasTableAdapter.Update(newUzsakymoKunas); dataset not setting to new ID.

void UpdateDB()
        {
            tblUzsakymoGalvaTableAdapter.SqlAdapter.RowUpdated += new System.Data.OleDb.OleDbRowUpdatedEventHandler(OnTblUzsakymoGalvaRowUpdated);
            tblUzsakymoKunasTableAdapter.SqlAdapter.RowUpdated += new System.Data.OleDb.OleDbRowUpdatedEventHandler(OnTblUzsakymoKunasRowUpdated);
            this.BindingContext[uzklausosDataSet, "tblUzsakymoGalva"].EndCurrentEdit();
            this.BindingContext[uzklausosDataSet, "tblUzsakymoKunas"].EndCurrentEdit();
            tblUzsakymoGalvaBindingSource.EndEdit();
            tblUzsakymoKunasBindingSource.EndEdit();

            try
            {
                UzklausosDataSet.tblUzsakymoKunasDataTable deleteUzsakymoKunas = (UzklausosDataSet.tblUzsakymoKunasDataTable)
                uzklausosDataSet.tblUzsakymoKunas.GetChanges(DataRowState.Deleted);

                //  Remove  all  deleted  records  from  the  tblUzsakymoKunas table.
                if (deleteUzsakymoKunas != null)
                {
                    tblUzsakymoKunasTableAdapter.Update(deleteUzsakymoKunas);
                }
               

                //  Update  the  tblUzsakymoGalva  table.
                tblUzsakymoGalvaTableAdapter.Update(uzklausosDataSet.tblUzsakymoGalva);

                UzklausosDataSet.tblUzsakymoKunasDataTable newUzsakymoKunas = (UzklausosDataSet.tblUzsakymoKunasDataTable)
                    uzklausosDataSet.tblUzsakymoKunas.GetChanges(DataRowState.Added);

                //  Add  new  orders  to  the  tblUzsakymoKunas table.
                if (newUzsakymoKunas != null)
                {
                    tblUzsakymoKunasTableAdapter.Update(newUzsakymoKunas);
                }
               

                UzklausosDataSet.tblUzsakymoKunasDataTable modifiedUzsakymoKunas = (UzklausosDataSet.tblUzsakymoKunasDataTable)
                    uzklausosDataSet.tblUzsakymoKunas.GetChanges(DataRowState.Modified);

                //  Update  all  modified  tblUzsakymoKunas.
                if (modifiedUzsakymoKunas != null)
                {
                    tblUzsakymoKunasTableAdapter.Update(modifiedUzsakymoKunas);
                }
               
                uzklausosDataSet.AcceptChanges();               
            }

            catch (System.Data.DBConcurrencyException dbcx)
            {
                MessageBox.Show("Update failed:" + dbcx.Data + "\r\n" + dbcx.Message);
            }

            catch (System.Data.DataException dbcx)
            {
                MessageBox.Show("Update failed:" + dbcx.Data + "\r\n" + dbcx.Message);
            }

            catch (System.Exception ex)
            {
                MessageBox.Show("Update  failed" + ex.Data + "\r\n" + ex.Message);
            }

            finally
            {
                /*
                if (deleteUzsakymoKunas != null)
                {
                    deleteUzsakymoKunas.Dispose();
                }
                if (newUzsakymoKunas != null)
                {
                    newUzsakymoKunas.Dispose();
                }
                if (modifiedUzsakymoKunas != null)
                {
                    modifiedUzsakymoKunas.Dispose();
                }
                 */
            }
        }

Answer 2

I understood where is problem.

UzklausosDataSet.tblUzsakymoKunasDataTable newUzsakymoKunas = (UzklausosDataSet.tblUzsakymoKunasDataTable)
                    uzklausosDataSet.tblUzsakymoKunas.GetChanges(DataRowState.Added);

Creates a *COPY* of dataset. So, when I Update dataset in RowUpdated event, I update newUzsakymoKunas, but doesn't  update binded dataset uzklausosDataSet. So only question is how to correct this problem elegant way.

First I try:

uzklausosDataSet.tblUzsakymoKunas.Merge(newUzsakymoKunas);

But in dgv lines duplicates: two rows with old id and new id.

Then I try to add direct write to dataset (from the UpdatedRow Event):

Uzklausos.UzklausosDataSet.tblUzsakymoKunasRow row = (Uzklausos.UzklausosDataSet.tblUzsakymoKunasRow)uzklausosDataSet.tblUzsakymoKunas.Rows.Find(oldID);
                if (row != null)
                    row.UzsakymoKunasID = newID;

This time everything works fine, but it's not elegant solution, more slowly and etc. Is there other variants?