Adodataset Deleting from Joined Table

ADODataSet deleting from joined table

You need to use the Unique Table dynamic property

ADOQuery1.Properties['Unique Table'].Value := 'GAMES';

From the MSDN ADO Documentation

If the Unique Table dynamic property is set, and the Recordset is the
result of executing a JOIN operation on multiple tables, then the
Delete method will only delete rows from the table named in the Unique
Table property.

How do you delete, update, etc. tables produced by queries in Delphi ADO?

Both James L and Hendra provide the essence of how to do what you want. The following is a way to implement it.

procedure TForm1.ADOQuery1BeforeDelete(DataSet: TDataSet);
var
  SQL : string;
begin
  SQL := 'DELETE FROM [Note] WHERE NoteID='+
    DataSet.FieldByName('NoteID').AsString;
  ADOConnection1.Execute(SQL);
  TADOQuery(DataSet).ReQuery;
  Abort;
end;

This will allow TADOQuery.Delete to work properly. The Abort is necessary to prevent TADOQuery from also trying to delete the record after you have deleted it. The primary down side is that the TADOQuery.ReQuery does not preserve the cursor position, i.e. the current record will be the first record.

Update:

The following attempts to restore the cursor. I do not like the second Requery, but it appears to be necessary to restore the DataSet after attempting to restore a invalid bookmark (due to deleting the last record). This worked with my limited testing.

procedure TForm1.ADOQuery1BeforeDelete(DataSet: TDataSet);
var
  SQL : string;
  bm : TBookmarkStr;
begin
  SQL := 'DELETE FROM [Note] WHERE NoteID='+
    DataSet.FieldByName('NoteID').AsString;
  bm := Dataset.BookMark;
  ADOConnection1.Execute(SQL);
  TADOQuery(DataSet).ReQuery;
  try
    Dataset.BookMark := bm;
  except
    TADOQuery(DataSet).Requery;
    DataSet.Last;
  end;
  Abort;
end;

Delphi - Deleting an active/selected record of a ADO Table

Like this:

procedure TForm1.Button1Click(Sender : TObject);
begin
    try
        AdoTable1.Delete;
    except
        on E:Exception do begin
            ShowMessage('Unable to delete record. ' + 
                        E.ClassName + ':' + E.Message);
        end;
    end;
end;

Delete old records from DataSet and reload view c#

The Delete method doesn't remove the row from the DataSet table. It just marks the DataRow.RowState with the enum DataRowState.Deleted. And if you try to reference a DataRow with its RowState = DataRowState.Deleted the mentioned error is raised.

This approach is required if you want to persist your changes back to the database. If you think about it, how do you manage to delete the row from the database table if the row deleted is no more in the DataSet table?

If you want to remove the row deleted from the in-memory datatable then you need to call

bez.dset.Tables["typ"].AcceptChanges();

This calls applies your changes to the in memory DataTable changing every row RowState to DataRowState.Unchanged and removing the deleted rows. Of course, after this call the ADO.NET classes like the DbDataAdapter are no more able to persist your changes back to the database.

If you keep your DataRow with the RowState set to DataRowState.Deleted in your Datatable then you need to check for every RowState before trying to use it

foreach (DataRow drow in bez.dset.Tables["typ"].Rows) 
{
   if(drow.RowState != DataRowState.Deleted)
   {
       string value = drow.Field<string>("NameOfYourColumnOfStringType");
       ..... whatever you want to do with it....
   }
}

Refresh ADO dataset when underlying table is updated

ADO Dataset retrieve data to client based on Client / Server concept. Like a web browser.

That means: Client Request -> Server, Server Answer -> Client. So Client really can't know if any data has been changed.

Simplest way is to requery data (Close/Open) and fetch data by your needs, not all 1300 at once. This is most used solution.

Anyway, if amount of data is really BIG and you want to play with optimization You have two ways:

A. Build a log table and register table data changes using triggers. Periodically ask server for changes (you may do this in a background thread):

select L.RECORD_ID, L.OPERATION_ID
from FILE_LOG L
where L.FDATESTAMP between :LAST_WATCH and :CURRENT_STAMP and L.FOLDER_ID = :FOLDER_ID

You will get RECORD_ID and OPERATION_ID (ie insert/update/delete).

B. If you are not linked to DBMS, Firebird/Interbase have events concept. Using triggers you can inform client that data has been changed, and you can requery just modified data.

either bof or eof is true or the current record has been deleted.. error on applyupdates that contains a delete operation

I had to bypass the provider and apply delete operation manually. it keeps error in Debug mode, but i can live with that.

procedure Tfrm.dspTarifelerBeforeUpdateRecord(Sender: TObject;
  SourceDS: TDataSet; DeltaDS: TCustomClientDataSet; UpdateKind: TUpdateKind;
  var Applied: Boolean);
begin
  if updatekind = ukDelete then
  begin
   if dm.qryTarifeler.Locate('Prefix',DeltaDs['Prefix'],[]) then
      dm.qryTarifeler.Delete;
   applied := true;
  end;
end;

---

Related Topics