Problem with TSQLQuery, can not save data lazarus / 16-Databases ---------------------------------------------------------------------- mirws · 2011-05-02 01:54 All, I use lazarus 0.9.28 on Ubuntu 9.04. I have a problem using TSQLQuery component. This is the scenario; I have a mysql table with 6 field. 3 of it I put it on the form using TDBEdit (Dataset set to TSQLQuery the others not. When I edit these 3 field which is on the form, its working well, all data updated into database. But when I set the others 3 field which is not on the form, using a code : Code: [Select]    MySQLQuery.FieldByName('EncodedBy').value := 'admin';     MySQLQuery.FieldByName('ModifiedBy').value := 'admin';     MySQLQuery.FieldByName('EncodedDate').value := formatDateTime('yyyy-mm-dd',Now); on BeforePost event of TSQLQuery component, is not working. In other words, data not saved in database. There is no error message displayed... Pls help me to figure this out... ---------------------------------------------------------------------- Lacak2 · 2011-05-02 05:35 And 3 fields are saved only in case, when there is no BeforePost ? in other word if there is any BeforePost then no field is saved ? If yes, then there must be some error in BeforePost code ... may be: Code: [Select]MySQLQuery.FieldByName('EncodedDate').value := formatDateTime('yyyy-mm-dd',Now); Is your locale shortdateformat really 'yyyy-mm-dd' not for example 'dd.mm.yyyy' or 'mm/dd/yyyy' Try this: Code: [Select] MySQLQuery.FieldByName('EncodedBy').AsString := 'admin';  MySQLQuery.FieldByName('ModifiedBy').AsString := 'admin';  MySQLQuery.FieldByName('EncodedDate').AsDateTime := date(); ---------------------------------------------------------------------- mirws · 2011-05-02 06:24 Hi there.. thanks for your quick reply... I also thought like that before...it's should be an error in this line Code: [Select]MySQLQuery.FieldByName('EncodedDate').value := formatDateTime('yyyy-mm-dd',Now); I have been try to comment that line and leave the other 2 lines as it is because its just a text (String), But its still can not saved including any changes in my TDBEdit on the Tform. And again, with no error message ---------------------------------------------------------------------- Lacak2 · 2011-05-02 07:33 And If you comment also remaining 2 lines in BeforePost, then changes are applied  ? Common error is, that users do not use after Post also ApplyUpdates and Commit. ---------------------------------------------------------------------- mirws · 2011-05-02 07:46 I have add ApplyUpdates & Commit in AfterPost event without success.. ---------------------------------------------------------------------- Lacak2 · 2011-05-02 08:16 Quote from: mirws on May 02, 2011, 09:46:05 amI have add ApplyUpdates & Commit in AfterPost event without success.. OK and what happens if you comment remaining 2 lines in BeforePost, then changes are applied to database ? (as you wrote they should be) ---------------------------------------------------------------------- mirws · 2011-05-02 08:36 Yes.. the changes applied if I commented to all lines in the beforePost event ---------------------------------------------------------------------- Lacak2 · 2011-05-02 08:57 So it me seems logical, that there must be some error (probably which raises exception, which is consumed) in these lines ... do you can isolate problem by commenting line by line ? or use some of other fields, which are used in data-aware controls ? or put aroud the lines try ... except (something like): Code: [Select]try  DataSet.FieldByName('EncodedBy').AsString := 'admin';  DataSet.FieldByName('ModifiedBy').AsString := 'admin';  DataSet.FieldByName('EncodedDate').AsDateTime := date(); except  on E:Exception do showmessage(E.Message); end; Are you inserting or updating row ? ---------------------------------------------------------------------- mirws · 2011-05-04 00:54 Ok I think, right now I need a working example for this... Would you like to share it to me??? I need a simple example that use mysql database,the table structure should have these fields: field nametypecategoryIdvarchar(5)categoryNamevarchar(20)encodedByvarc har(5)encodedDateDatetimemodifiedByvarchar(5)modifiedDateDatetime application using TSQLQuery, and TDBGrid, TDBEdit   for column categoryId and categoryName, the others will edited or set by a code when a record on edit state or insert state I need an example that able to insert, update and delete... I think this is  simple app and I did this in Delphi with success. Any help, I'm so appreciate it... thanks in advance... ---------------------------------------------------------------------- Lacak2 · 2011-05-04 05:59 Attach here please your project (zipped) and I will look at it. ---------------------------------------------------------------------- mirws · 2011-05-05 00:15 I think you'r right.. the problem is in datetime field .. but I still can not find the solution yet... pls find my attach. for your review... ---------------------------------------------------------------------- Lacak2 · 2011-05-06 06:27 Hi mirws, I have tested your app and I see there 2 problems: 1) Put ApplyUpdates and Commit together in AfterPost: Code: [Select]procedure TForm1.SQLQuery1AfterPost(DataSet: TDataSet); begin   SQLQuery1.ApplyUpdates;   SQLTransaction1.Commit; end; procedure TForm1.btnSaveClick(Sender: TObject); begin   try      SQLQuery1.Post;   except      on E:Exception do         showmessage(E.message);   end; end; 2) You have Primary key defined on table, but you does not use it. Instead you use: Code: [Select]SQLQuery1.Upda]"]>Blockedde:= upWhereChanged; This seting affect how SQL UPDATE is constructed (only fields which have ProviderFlag = pfInKey or are updated and have ProviderFlag=pfInWhere are used in UPDATE WHERE part) Why you do not use?: Code: [Select]SQLQuery1.Upda]"]>Blockedde:= upWhereKeyOnly; Also related bug is in FPC exists which does not allow update rows, which have NULL values and these values are used in WHERE part of update statement. http://bugs.freepascal.org/view.php?id=14730 See attached unit with my modifications. ---------------------------------------------------------------------- mirws · 2011-05-06 07:42 Thanks Lacak2, I've note your comment... I know I made stupid thing in afterPost and SQLQuery.Upda]"]>Blockedde, this is after several possibility way that I tried not work at all... I think the main problem is in my datefield as you see in my previous post, I've found that I can not update/insert when I used my datefield in beforePost() especially when it was a null value. (Big thanks to your bugs link about it) its time to code now.. ---------------------------------------------------------------------- Lacak2 · 2011-05-06 10:13 Quote from: mirws on May 06, 2011, 09:42:32 amI've found that I can not update/insert when I used my datefield in beforePost() especially when it was a null value. (Big thanks to your bugs link about it) You can INSERT always,  but you can not UPDATE (in case of OldValue is null and Upda]"]>Blockedde is upWhereChanged or UpWhereAll)