Changing Query dataset without changing table values

teunis · Post by **teunis** » Wed 22 Apr 2009 14:44

Example
MyQuery1:
[code]SELECT name, Count(name) AS quant FROM Table1
GROUP BY name[/code]
Now I like to delete some records from MyQuery without deleting an entry in Table1
[code]IF MyQuery.Locate('name',"name to delete,[]) THEN MyQuery.Delete;[/code]
I get a message: Update Failed
I can do[code] CREATE TEMPORARY TABLE Table2 etc.[/code] and read the records from MyQuery1
into Table2 and after that delete the records but it is not elegant.
Anybody knows a solution?
Thanks for your time Teunis

Dimon · Post by **Dimon** » Thu 23 Apr 2009 08:37

This probelm is arised because the number of deleted records in the table is more than 1.
To avoid this exception you sould set the TMyQuery.Options.StrictUpdate property to False.

teunis · Post by **teunis** » Thu 23 Apr 2009 13:17

NO NO
The question is that NO change should be made to the data in the underlying table
The only change should be made in the resultset of the Query.
With StrictUpdate:= FALSE; a whole lot of records can be lost in the underlying table.
The underlying table should stay intact only the Query result should be changed!
Is that possible???

Frega · Post by **Frega** » Thu 23 Apr 2009 23:49

you can use DataSetProvider

MyQuery -> DataSetProvider -> ClientDataSet

when you load the form it will copy all MyQuery columns and rows to ClientDataSet, then, you can edit everything in clientdataset, the real database will not be affected by any modification, only if you do DataSetProvider.ApplyUpdates;

teunis · Post by **teunis** » Fri 24 Apr 2009 01:35

Sory I don't understand the expression "when you load the form"
I can place a TDataSetProvider on my form and specify the dataset to be MyQuery
That doesn't help. Can you give me an example.
By the way I am using Delphi 6
Thanks

Frega · Post by **Frega** » Fri 24 Apr 2009 02:10

err, sorry dude, my english is not the better, but i will try to explain...

1. Put a DataSetProvider on your form...
2. set DataSetProvider.DataSet to MyQuery1

3. Put a ClientDataSet on your form...
4. set ClientDataSet.ProviderName to DataSetProvider1

5. Put a DataSource on your form...
6. set DataSource.dataset to ClientDataSet1

7. put a dbgrid on your form and link it to DataSource1 (Clientdataset)

8. Open your MyQuery and Active the clientdataset

9. you will se the DBGrid will load the Query table, but he is connected to Datasource1 (clientdataset)

this is a basic example, i think if you make it you will understand....

Dimon · Post by **Dimon** » Fri 24 Apr 2009 06:46

For storing data in memory you can use the TVirtualTable component. You can find more detailed information about this component in the MyDAC help.

Also you can set the TMyQuery.CachedUpdates property to True and never use ApplyUpdates.