I have a dbase with a table called GROUPS. Then I hooked up a . If I put a "SELECT * FROM GROUPS ORDER BY ORGANISATIONNAME" this works perfect with the reminder that I have to add the SQL statements at the Form->Activate event, adding it statically does not work somehow (???).
Anyway, I also hooked up a for some delete/update actions. To the property I add "update GROUPS set *" (this is done statically in the component through the IDE).
Both delete and update won't work somehow. At delete (push delete at At update I get error message
What am I doing wrong here? :shock: I use . What should I put in the TASQLiteQuery->SQL at time of delete or update? I don't want to work directly on the (as suggested in other posts) because I want to work on a selection (subset) of the data in the end (so I need a select * where blah blah). Hope someone has some hint(s) for me :roll:
Geregistreerd: wo jan 25 2006, 04:34 Woonplaats: groningen (NL) berichten: 1561
There is a sample in the D2 package you could look at. In fact the insert/delete/update sql should be valid statements with parameters to be attached. When executing insert/delete/update this statement is copied into the sql object and executed the normal way. So afterwards (the execsql) replace the sql with the select. This is what is not done automatically.
The other problem: sql is a property to be assigned a string value. It should not depend on events:
I'm not a C++ guru, but in Delphi is should be like this
with Q do close; sql.text := 'select something from something where something'; params[0].AsSomething := Something; etc. open; end;
or
with Q do close; sql.text := 'insert...'; params[0].AsSomething := Something; etc. execsql; end;
Thanks for the answer so far. Where can I find that D2 package? I searched the download section and the samples that came with the component but could'nt find it there. In the samples there are only delphi samples which I cannot open in C++ Builder so its a bit hard... Maybe I can assist in composing some C++ Builder examples? (as there are C++ Builder users here frequently I noticed) :shock:
Anyway, some light at the end of the tunnel but I still don't understand. The params I found in the TASQLiteQuery but where to put what is still unclear.
I have like this now at a button click:
AnsiString tempString;
tempString = "DELETE FROM GROUPS WHERE GROUPID="; tempString += groupIDDBEdit->Text; groupsUpdateSQL->DeleteSQL->Clear(); groupsUpdateSQL->DeleteSQL->Add( tempString );
Questions remaining: - What should I put in the TASQLQuery->SQL? (strings) - Do I need to use the 'params' as you describe? Can't I just compose my desired SQL statement and copy it in the SQL property? - If I need to use the 'params' (which is a propery of the TASQLQuery), how do I use it? How do I indicate what are the keywords that should be replaced and with what at what moment? U use 3x something in the example which might confuse the example...? :twisted: - Why does one need the UpdateSQL component anyway, why can't I just insert my SQL statement in the TASQLQuery component (SQL property) and execSQL() it or Open() the component? Maybe if I understand that I get more feeling bout the way of working...
Hope you can explain some more because I am dying to get this working :wink:
Geregistreerd: wo jan 25 2006, 04:34 Woonplaats: groningen (NL) berichten: 1561
I'll see if I can translate the Delphi sample. You don't need the updatesql if you do not use an updatable query with the data-aware components. If you want to use a dbgrid with tquery THEN you need a tupdatesql. You can insert, delete and update rows with the dbnavigator. To keep things easy I would suggest to use a ttable component, but there are situations where you might have a more complex sql. But in general, using tquery with tupdatesql is the hard way of using ttable :wink:
Geregistreerd: wo jan 25 2006, 04:34 Woonplaats: groningen (NL) berichten: 1561
Just rereading your question. In TASQLiteQuery.Query you should put your select statement. If in a DBGrid insert/delete or update is created, an event is sent to the queryobject causing the select statement to be replaced by the tupdatesql insert/delete or update statement. It is then executed using execsql. So after the update you need to restore the selectstatement and re-open the query. This is logical since the internal resultset does not get updated if you put something with execsql into the database, it needs to be refreshed.
The proper UpdateSQL syntax can be found in the comment in the component source.
But now the easy way without update etc, but just the select. Put a DB and Query object on the form. A datasource and DBGrid. Fill the right directories to the sqlitedll and the database. Select the database. Now you can put a valid sql into the sqlstring and open the sql component. Connect the datasource and dbgrid. You should see the content of your sql now. If not, you've done something wrong.
To enter sql in your program it should look something like this.
Q -> Close(); Q -> SQL = 'create table x (pk integer pimary key, name varchar(20))'; Q-> ExecSQL;
The easiest way for an updatable resultset:
Use the SQL component for complex queries. For data-aware result sets use a TTable. Follow the same steps, but select the wanted table in the table property. You don't need sql here just open the table and you' re off.
If you need to filter, just add this to the filter component, it is added to the generated sql statement.
Just rereading your question. In TASQLiteQuery.Query you should put your select statement. If in a DBGrid insert/delete or update is created, an event is sent to the queryobject causing the select statement to be replaced by the tupdatesql insert/delete or update statement. It is then executed using execsql. So after the update you need to restore the selectstatement and re-open the query. This is logical since the internal resultset does not get updated if you put something with execsql into the database, it needs to be refreshed.
The proper UpdateSQL syntax can be found in the comment in the component source.
But now the easy way without update etc, but just the select. Put a DB and Query object on the form. A datasource and DBGrid. Fill the right directories to the sqlitedll and the database. Select the database. Now you can put a valid sql into the sqlstring and open the sql component. Connect the datasource and dbgrid. You should see the content of your sql now. If not, you've done something wrong.
To enter sql in your program it should look something like this.
Q -> Close(); Q -> SQL = 'create table x (pk integer pimary key, name varchar(20))'; Q-> ExecSQL;
The easiest way for an updatable resultset:
Use the SQL component for complex queries. For data-aware result sets use a TTable. Follow the same steps, but select the wanted table in the table property. You don't need sql here just open the table and you' re off.
If you need to filter, just add this to the filter component, it is added to the generated sql statement.
Albert
Can you may show example with update/delete syntacs because alone select is simple but I can't make proper example with delete or update syntax. I connected dbgrid to datasource , datasource to query and when I used to delete syntax and after select syntax into code I got mistake.
Geregistreerd: wo jan 25 2006, 04:34 Woonplaats: groningen (NL) berichten: 1561
It should work with TQuery and TUpdateSQL. Problem is that the Delphi sample does work. Next week I will publish a new alpha with changes in the updatesql code. Please try again then. Problem is that the patch will be for version 3, but you should be able to apply the changes in 2. I'll do that when version 3 alpha is stable.
It should work with TQuery and TUpdateSQL. Problem is that the Delphi sample does work. Next week I will publish a new alpha with changes in the updatesql code. Please try again then. Problem is that the patch will be for version 3, but you should be able to apply the changes in 2. I'll do that when version 3 alpha is stable.
albert
When thjis patch will be accessible??? Can I try TQuery and TUpdateSQL with ver. 3 components without this patch???
I tried and: When I've used TQuery, TUpdateSQL and DBNavigator for delete record from DBGrid is working quite good but not for all record (I got error) only where was only one record in DGGrid, Do you know why this technology ( based on TQuery and TUpdateSQL ) working properly only when I have only one record in database ( DBGrid)??? Maybe I should use any method earlier or later e.g.: BeforeDelete, AfterDelete, UpdateApply or sth else??? Do you anybody use properly delete/update/insert using TQuery, TUpdateSQL??? Because I 'stay in one point' with my application without solving how to delete/update records using DBGrid 'on live'
Geregistreerd: wo jan 25 2006, 04:34 Woonplaats: groningen (NL) berichten: 1561
Actually, the most development is done in the version 3 components. I'll try to test the V3 alpha next week on this point. Useually I send an email to all users when a new patch is available so if you have set your preferences right on this forum you will be notified when it's available. Sorry I can't be more specific since I'm not able to work on the components before next week.
without solving how to delete/update records using DBGrid 'on live'