0

I am using TAdoQuery with BatchOptimistic lock type. If the select command has some fields that are calculated on database server the returned fields has property ReadOnly = true, so i must change them to false so i can modify them in my query.

I am actually never attempting to post to database, but i must use TAdoQuery.

Its all good to the point I append or insert some record, set its fields, and then call for example TAdoQuery.Last, Next or First.. The appended records fields change to null. Please, is there a way that these records could stay as they were?

I am attaching a simple code here, where the problem is presented:

      // .. lockType = Batchoptimistic so TAdoQuery.first or TAdoQuery.last do NOT post do database
      ADOQuery1.LockType := ltBatchOptimistic;
      ADOQuery1.SQL.Text := 'SELECT 10 AS id, 20 AS sid ';
      ADOQuery1.Open;
    
      // .. readOnly = false so i can modify these two fields in appended record
      ADOQuery1.FieldByName('id').ReadOnly := false;
      ADOQuery1.FieldByName('sid').ReadOnly := false;
      ADOQuery1.Append;
    
      ADOQuery1.FieldByName('id').AsInteger := 5;
      ADOQuery1.FieldByName('sId').AsInteger := 5;
    
      // if use last, first  etc. the appended record fields will change to 0 (null)
      ADOQuery1.Last;
Pavel Vlasák
  • 1
  • 1
  • 1
    You should be calling`ADOQuery1.Post()` before `ADOQuery1.Last()` – Remy Lebeau Apr 28 '22 at 14:25
  • 1
    Your approach is wrong. To work correctly with ADO, your Sql database needs to support the metadata .needed to generate Insert, Update and Delete statements AND to be able to indentify which row needs to be updated or deleted. Your Sql statement only defines two constant values so clearly cannot do any of these things. Start again, this time defining **the structure of the table** you want to work with **in the datadabase** before you attempt anything else. – MartynA Apr 28 '22 at 18:21
  • Remy Lebau - As i mentioned i am never posting from this query.. its for select data from DB and then make some changes locally.. to post to DB i have other queries. I just want to make that changes when the fields are read only (and then set to not read only) – Pavel Vlasák Apr 29 '22 at 06:16
  • MartynA - This is just a simple example when fields return as read only. It could be a stored procedure where some fields returns converted. This is large project and I cannot do changes on database. I do not want to update or delete from this TAdoQuery, for this purposes there are other TAdoQueries. I just want to select from DB on this TAdoQuery and eventualy make there some changes withou posting, and without clearing records when calling First, etc.. I know solution could be creating second offline DataSet, but more legant way would be using just this qry, because of scale of the project – Pavel Vlasák Apr 29 '22 at 06:32
  • @PavelVlasák ADO does 2-way communication with the server. You cannot do what you are doing here, with ADO. Instead use a TClientDataSet (or an FDMemTable) with a TDatasetProvider, so you get a local copy of the data you can work on, without it posting changes to the database. – Freddie Bell Apr 29 '22 at 07:08
  • @FreddieBell Never used TDataSetProvider before, but sounds like a way. Could it be used like a connection between that TAdoQuery and for example TClientDataSet? I must preserve that TAdoQuery wich i have, and use for example sencond TClientDataSet for working with data. – Pavel Vlasák May 02 '22 at 06:07
  • Here is an example of how to use a TDatasetProvider between an (ADO)Query and a TClientdataset (https://www.drbob42.com/examines/examin64.htm) – Freddie Bell May 02 '22 at 14:46
  • In the end, we decided to migrate to UniDAC, which supports UpdateObject - and that is exactly what i needed. Modify, Insert and Delete driven by separated procedures. Its almost like FireDAC, but one must upgrade to enterprise version to use it with MS SQL Server. So UniDAC a lot cheaper option. – Pavel Vlasák May 09 '22 at 07:43

0 Answers0