Recent

Author Topic: TSQLQuery.ExecSQL not making changes  (Read 11595 times)

HopefulGuy

  • New Member
  • *
  • Posts: 28
Re: TSQLQuery.ExecSQL not making changes
« Reply #45 on: May 13, 2021, 04:08:56 am »
1.  With your program, try inserting

      ReadQuery.First;

    just after Requery.Open; . Not sure for now whether this will solve the problem.
It did, thanks!

2.  You can define calculated field for sCBXEntry.

3.  Then, you don't have to populate combobox manually as you did. There is a TDBComboBox component.
I saw the TDBComboBox but I couldn't see how to make it a compound of multiple fields.

Before you try combobox, just drop a DBGrid and a DataSource on the form, and set DBGrid's datasource property to the DataSource, and DataSource's DataSet to ReadQuery. And see whether the query result is displayed correctly whenever you open ReadQuery.

With the ReadQuery.First(); added in, the ComboBox works perfectly as is so I am disinclined to change it.

However, that doesn't solve the other issue with the DB components. Form1 has a shared OnClick event for two buttons, differing only slightly from each other based on which button was pushed:
Code: Pascal  [Select][+][-]
  1. procedure TfrmList.btnNewEditClick(Sender: TObject);
  2.   var strIDNo : string;
  3. begin
  4.   frmEditInfo:=TfrmEditInfo.Create(self);
  5.   if (Sender=btnEdit) then
  6.   begin
  7.     strIDNo:=trim(copy(cbxListLadies.Text, 1,pos(' - ', cbxListLadies.Text)-1));
  8.     frmEditInfo.SetupForm('EDIT', strIdNo);
  9.   end
  10.   else
  11.   begin
  12.     frmEditInfo.SetupForm('NEW', '0');
  13.   end;
  14.   frmEditInfo.ShowModal();
  15.   FormUpdate();
  16. end;
  17.  

on frmEditInfo there are many data-aware components. TDBEdits and TDBMemos mostly. Each component is assigned to the Data set on that form. But if I click on the btnEdit, the screen comes up with ALL controls empty. Her's how I have the form setup (test version of .lfm file):
Code: Pascal  [Select][+][-]
  1. object frmEditInfo: TfrmEditInfo
  2.   Left = 327
  3.   Height = 823
  4.   Top = 144
  5.   Width = 1047
  6.   Caption = 'Woman Information'
  7.   ClientHeight = 823
  8.   ClientWidth = 1047
  9.   Position = poMainFormCenter
  10.   LCLVersion = '2.0.12.0'
  11.   object dbedtGiven: TDBEdit
  12.     Left = 95
  13.     Height = 23
  14.     Top = 91
  15.     Width = 176
  16.     DataField = 'GivenName'
  17.     DataSource = DBData
  18.     CharCase = ecNormal
  19.     MaxLength = 0
  20.     TabOrder = 0
  21.   end
  22.   object lblGiven: TLabel
  23.     Left = 23
  24.     Height = 15
  25.     Top = 95
  26.     Width = 65
  27.     Caption = 'Given Name'
  28.     ParentColor = False
  29.   end
  30.   object lbFamily: TLabel
  31.     Left = 340
  32.     Height = 15
  33.     Top = 95
  34.     Width = 70
  35.     Caption = 'Family Name'
  36.     ParentColor = False
  37.   end
  38.   object dbedtFamily: TDBEdit
  39.     Left = 412
  40.     Height = 23
  41.     Top = 91
  42.     Width = 176
  43.     DataField = 'FamilyName'
  44.     DataSource = DBData
  45.     CharCase = ecNormal
  46.     MaxLength = 0
  47.     TabOrder = 1
  48.   end
  49.   object lblAge: TLabel
  50.     Left = 600
  51.     Height = 15
  52.     Top = 95
  53.     Width = 21
  54.     Caption = 'Age'
  55.     ParentColor = False
  56.   end
  57.   object dbedtAge: TDBEdit
  58.     Left = 628
  59.     Height = 23
  60.     Top = 91
  61.     Width = 20
  62.     DataField = 'Age'
  63.     DataSource = DBData
  64.     CharCase = ecNormal
  65.     EditMask = '##;0;_'
  66.     MaxLength = 2
  67.     TabOrder = 2
  68.   end
  69.   object dbmemPostal: TDBMemo
  70.     Left = 95
  71.     Height = 90
  72.     Top = 112
  73.     Width = 345
  74.     DataField = 'postal'
  75.     DataSource = DBData
  76.     TabOrder = 3
  77.   end
  78.   object lblPostal: TLabel
  79.     Left = 11
  80.     Height = 15
  81.     Top = 123
  82.     Width = 77
  83.     Caption = 'Postal Address'
  84.     ParentColor = False
  85.   end
  86.   object dbedtemail: TDBEdit
  87.     Left = 550
  88.     Height = 23
  89.     Top = 112
  90.     Width = 176
  91.     DataField = 'FamilyName'
  92.     DataSource = DBData
  93.     CharCase = ecNormal
  94.     MaxLength = 0
  95.     TabOrder = 4
  96.   end
  97.   object lblEmail: TLabel
  98.     Left = 468
  99.     Height = 15
  100.     Top = 116
  101.     Width = 74
  102.     Caption = 'Email Address'
  103.     ParentColor = False
  104.   end
  105.   object lblPhone: TLabel
  106.     Left = 468
  107.     Height = 15
  108.     Top = 144
  109.     Width = 64
  110.     Caption = 'Telephone #'
  111.     ParentColor = False
  112.   end
  113.   object dbedtPhone: TDBEdit
  114.     Left = 550
  115.     Height = 23
  116.     Top = 140
  117.     Width = 176
  118.     DataField = 'FamilyName'
  119.     DataSource = DBData
  120.     CharCase = ecNormal
  121.     MaxLength = 0
  122.     TabOrder = 5
  123.   end
  124.   object lblGender: TLabel
  125.     Left = 468
  126.     Height = 15
  127.     Top = 172
  128.     Width = 38
  129.     Caption = 'Gender'
  130.     ParentColor = False
  131.   end
  132.   object dbcbxGender: TDBComboBox
  133.     Left = 552
  134.     Height = 23
  135.     Top = 164
  136.     Width = 100
  137.     DataField = 'gender'
  138.     DataSource = DBData
  139.     ItemHeight = 15
  140.     Items.Strings = (
  141.       'Female'
  142.       'Male'
  143.       'Other'
  144.     )
  145.     MaxLength = 0
  146.     TabOrder = 6
  147.   end
  148.   object dbmemNotes: TDBMemo
  149.     Left = 96
  150.     Height = 459
  151.     Top = 204
  152.     Width = 843
  153.     DataSource = DBData
  154.     TabOrder = 7
  155.   end
  156.   object lblNotes: TLabel
  157.     Left = 11
  158.     Height = 15
  159.     Top = 220
  160.     Width = 31
  161.     Caption = 'Notes'
  162.     ParentColor = False
  163.   end
  164.   object btnSave: TButton
  165.     Left = 155
  166.     Height = 25
  167.     Top = 716
  168.     Width = 75
  169.     Caption = 'Save Data'
  170.     TabOrder = 8
  171.   end
  172.   object btnAbort: TButton
  173.     Left = 764
  174.     Height = 25
  175.     Top = 716
  176.     Width = 75
  177.     Caption = 'Close'
  178.     OnClick = btnAbortClick
  179.     TabOrder = 9
  180.   end
  181.   object DBData: TDataSource
  182.     Left = 28
  183.     Top = 16
  184.   end
  185.   object ReadQuery: TSQLQuery
  186.     IndexName = 'DEFAULT_ORDER'
  187.     MaxIndexesCount = 4
  188.     FieldDefs = <    
  189.       item
  190.         Name = 'IDNo'
  191.         DataType = ftAutoInc
  192.         Precision = -1
  193.       end    
  194.       item
  195.         Name = 'GivenName'
  196.         DataType = ftString
  197.         Precision = -1
  198.         Size = 20
  199.       end    
  200.       item
  201.         Name = 'FamilyName'
  202.         DataType = ftString
  203.         Precision = -1
  204.         Size = 20
  205.       end    
  206.       item
  207.         Name = 'email'
  208.         DataType = ftString
  209.         Precision = -1
  210.         Size = 90
  211.       end    
  212.       item
  213.         Name = 'age'
  214.         DataType = ftWord
  215.         Precision = -1
  216.       end    
  217.       item
  218.         Name = 'phone'
  219.         DataType = ftString
  220.         Precision = -1
  221.         Size = 15
  222.       end    
  223.       item
  224.         Name = 'postal'
  225.         DataType = ftMemo
  226.         Precision = -1
  227.       end    
  228.       item
  229.         Name = 'notes'
  230.         DataType = ftMemo
  231.         Precision = -1
  232.       end>
  233.     Database = frmList.Connection
  234.     Transaction = frmList.Transaction
  235.     SQL.Strings = (
  236.       'Select * from Contact where IDNo = 1'
  237.     )
  238.     Params = <>
  239.     DataSource = DBData
  240.     Left = 104
  241.     Top = 8
  242.   end
  243.   object WriteQuery: TSQLQuery
  244.     IndexName = 'DEFAULT_ORDER'
  245.     MaxIndexesCount = 4
  246.     FieldDefs = <    
  247.       item
  248.         Name = 'IDNo'
  249.         DataType = ftAutoInc
  250.         Precision = -1
  251.       end    
  252.       item
  253.         Name = 'GivenName'
  254.         DataType = ftString
  255.         Precision = -1
  256.         Size = 20
  257.       end    
  258.       item
  259.         Name = 'FamilyName'
  260.         DataType = ftString
  261.         Precision = -1
  262.         Size = 20
  263.       end    
  264.       item
  265.         Name = 'email'
  266.         DataType = ftString
  267.         Precision = -1
  268.         Size = 90
  269.       end    
  270.       item
  271.         Name = 'age'
  272.         DataType = ftWord
  273.         Precision = -1
  274.       end    
  275.       item
  276.         Name = 'phone'
  277.         DataType = ftString
  278.         Precision = -1
  279.         Size = 15
  280.       end    
  281.       item
  282.         Name = 'postal'
  283.         DataType = ftMemo
  284.         Precision = -1
  285.       end    
  286.       item
  287.         Name = 'notes'
  288.         DataType = ftMemo
  289.         Precision = -1
  290.       end>
  291.     Database = frmList.Connection
  292.     Transaction = frmList.Transaction
  293.     Params = <>
  294.     DataSource = DBData
  295.     Left = 196
  296.     Top = 12
  297.   end
  298. end

What am I doing wrong this time?

rvk

  • Hero Member
  • *****
  • Posts: 6109
Re: TSQLQuery.ExecSQL not making changes
« Reply #46 on: May 13, 2021, 05:12:24 am »
What am I doing wrong this time?
Your ReadQuery has datasource filled in, but you don't work with master/client relations here yet. And your datasource does not connect to any query. A datasource needs to connect with a query. Not the other way around (unless you are using master/client relation which you are not).

So you need to switch that. Fill ReadQuery in in DBData datasource.
And remove DBData as datasource from ReadQuery.

HopefulGuy

  • New Member
  • *
  • Posts: 28
Re: TSQLQuery.ExecSQL not making changes
« Reply #47 on: May 13, 2021, 05:45:30 am »
So you need to switch that. Fill ReadQuery in in DBData datasource.
And remove DBData as datasource from ReadQuery.

Did that, and presto. The fields are filled in.  Sweet. Thank You!! Now I just have to figure out the best method to write changes back to the database.

By the way, if I want to branch and read from a second table and have DB Aware controls read from the second, then I take it I need another TSQLQuery and Another TDatasource? Right now, notes is just a text field. What I'd like to do is convert it into a separate table with both text and image components. So I want to double check that the way I'm working on things now is correct. :)

HopefulGuy

  • New Member
  • *
  • Posts: 28
Re: TSQLQuery.ExecSQL not making changes
« Reply #48 on: May 13, 2021, 07:11:29 am »
No joy on the updates. I have a second query and a second dataset, but when I try to write something, if I don't have EVERY FIELD filled out, it complains that the missing field is required. I've checked EVERYWHERE and I can't see anywhere that is defining them as required. The FieldDefs were populated from the database automatically by Lazarus, and not one of them, in either the reading set or the writing set, shows them as required. I'll include the latest .lfm file, hopefully someone can see what I'm missing.

Also, when I DO fill in all the fields, it LOOKS like it is saving okay. I can make changes, and when I hit the save button to close the window, changes to the name show up in the ComboBox. If I re-edit the person, all my changes are there. BUT, if I save, then close the program, when I re-open the program the changes are gone!

Here's the lfm:
Code: Pascal  [Select][+][-]
  1. object frmEditInfo: TfrmEditInfo
  2.   Left = 327
  3.   Height = 823
  4.   Top = 144
  5.   Width = 1047
  6.   Caption = 'Person Information'
  7.   ClientHeight = 823
  8.   ClientWidth = 1047
  9.   Position = poMainFormCenter
  10.   LCLVersion = '2.0.12.0'
  11.   object dbedtGiven: TDBEdit
  12.     Left = 95
  13.     Height = 23
  14.     Top = 87
  15.     Width = 176
  16.     DataField = 'GivenName'
  17.     DataSource = DBData
  18.     CharCase = ecNormal
  19.     MaxLength = 0
  20.     TabOrder = 0
  21.     OnChange = DataChanged
  22.   end
  23.   object lblGiven: TLabel
  24.     Left = 23
  25.     Height = 15
  26.     Top = 91
  27.     Width = 65
  28.     Caption = 'Given Name'
  29.     ParentColor = False
  30.   end
  31.   object lbFamily: TLabel
  32.     Left = 340
  33.     Height = 15
  34.     Top = 91
  35.     Width = 70
  36.     Caption = 'Family Name'
  37.     ParentColor = False
  38.   end
  39.   object dbedtFamily: TDBEdit
  40.     Left = 412
  41.     Height = 23
  42.     Top = 87
  43.     Width = 176
  44.     DataField = 'FamilyName'
  45.     DataSource = DBData
  46.     CharCase = ecNormal
  47.     MaxLength = 0
  48.     TabOrder = 1
  49.   end
  50.   object lblAge: TLabel
  51.     Left = 600
  52.     Height = 15
  53.     Top = 91
  54.     Width = 21
  55.     Caption = 'Age'
  56.     ParentColor = False
  57.   end
  58.   object dbedtAge: TDBEdit
  59.     Left = 628
  60.     Height = 23
  61.     Top = 87
  62.     Width = 20
  63.     DataField = 'Age'
  64.     DataSource = DBData
  65.     CharCase = ecNormal
  66.     EditMask = '##;0;_'
  67.     MaxLength = 2
  68.     TabOrder = 2
  69.   end
  70.   object dbmemPostal: TDBMemo
  71.     Left = 95
  72.     Height = 90
  73.     Top = 112
  74.     Width = 345
  75.     DataField = 'postal'
  76.     DataSource = DBData
  77.     TabOrder = 3
  78.   end
  79.   object lblPostal: TLabel
  80.     Left = 11
  81.     Height = 15
  82.     Top = 123
  83.     Width = 77
  84.     Caption = 'Postal Address'
  85.     ParentColor = False
  86.   end
  87.   object dbedtemail: TDBEdit
  88.     Left = 550
  89.     Height = 23
  90.     Top = 112
  91.     Width = 176
  92.     DataField = 'email'
  93.     DataSource = DBData
  94.     CharCase = ecNormal
  95.     MaxLength = 0
  96.     TabOrder = 4
  97.   end
  98.   object lblEmail: TLabel
  99.     Left = 468
  100.     Height = 15
  101.     Top = 116
  102.     Width = 74
  103.     Caption = 'Email Address'
  104.     ParentColor = False
  105.   end
  106.   object lblPhone: TLabel
  107.     Left = 468
  108.     Height = 15
  109.     Top = 140
  110.     Width = 64
  111.     Caption = 'Telephone #'
  112.     ParentColor = False
  113.   end
  114.   object dbedtPhone: TDBEdit
  115.     Left = 550
  116.     Height = 23
  117.     Top = 136
  118.     Width = 176
  119.     DataField = 'phone'
  120.     DataSource = DBData
  121.     CharCase = ecNormal
  122.     MaxLength = 0
  123.     TabOrder = 5
  124.   end
  125.   object lblGender: TLabel
  126.     Left = 468
  127.     Height = 15
  128.     Top = 164
  129.     Width = 38
  130.     Caption = 'Gender'
  131.     ParentColor = False
  132.   end
  133.   object dbcbxGender: TDBComboBox
  134.     Left = 550
  135.     Height = 23
  136.     Top = 160
  137.     Width = 100
  138.     DataField = 'gender'
  139.     DataSource = DBData
  140.     ItemHeight = 15
  141.     Items.Strings = (
  142.       'Female'
  143.       'Male'
  144.       'Other'
  145.     )
  146.     MaxLength = 0
  147.     TabOrder = 6
  148.   end
  149.   object dbmemNotes: TDBMemo
  150.     Left = 96
  151.     Height = 459
  152.     Top = 204
  153.     Width = 843
  154.     DataField = 'notes'
  155.     DataSource = DBData
  156.     TabOrder = 7
  157.   end
  158.   object lblNotes: TLabel
  159.     Left = 11
  160.     Height = 15
  161.     Top = 220
  162.     Width = 31
  163.     Caption = 'Notes'
  164.     ParentColor = False
  165.   end
  166.   object btnSave: TButton
  167.     Left = 155
  168.     Height = 25
  169.     Top = 716
  170.     Width = 75
  171.     Caption = 'Save Data'
  172.     OnClick = btnSaveClick
  173.     TabOrder = 8
  174.   end
  175.   object btnAbort: TButton
  176.     Left = 764
  177.     Height = 25
  178.     Top = 716
  179.     Width = 75
  180.     Caption = 'Close'
  181.     OnClick = btnAbortClick
  182.     TabOrder = 9
  183.   end
  184.   object DBData: TDataSource
  185.     DataSet = ReadQuery
  186.     Left = 28
  187.     Top = 16
  188.   end
  189.   object ReadQuery: TSQLQuery
  190.     IndexName = 'DEFAULT_ORDER'
  191.     MaxIndexesCount = 4
  192.     FieldDefs = <>
  193.     Database = frmList.Connection
  194.     Transaction = frmList.Transaction
  195.     SQL.Strings = (
  196.       'Select * from Contact where IDNo = 1'
  197.     )
  198.     Params = <>
  199.     Left = 104
  200.     Top = 16
  201.   end
  202.   object WriteQuery: TSQLQuery
  203.     IndexName = 'DEFAULT_ORDER'
  204.     MaxIndexesCount = 4
  205.     FieldDefs = <    
  206.       item
  207.         Name = 'IDNo'
  208.         DataType = ftAutoInc
  209.         Precision = -1
  210.       end    
  211.       item
  212.         Name = 'GivenName'
  213.         DataType = ftString
  214.         Precision = -1
  215.         Size = 20
  216.       end    
  217.       item
  218.         Name = 'FamilyName'
  219.         DataType = ftString
  220.         Precision = -1
  221.         Size = 20
  222.       end    
  223.       item
  224.         Name = 'email'
  225.         DataType = ftString
  226.         Precision = -1
  227.         Size = 90
  228.       end    
  229.       item
  230.         Name = 'age'
  231.         DataType = ftWord
  232.         Precision = -1
  233.       end    
  234.       item
  235.         Name = 'phone'
  236.         DataType = ftString
  237.         Precision = -1
  238.         Size = 15
  239.       end    
  240.       item
  241.         Name = 'postal'
  242.         DataType = ftMemo
  243.         Precision = -1
  244.       end    
  245.       item
  246.         Name = 'notes'
  247.         DataType = ftMemo
  248.         Precision = -1
  249.       end>
  250.     Database = frmList.Connection
  251.     Transaction = frmList.Transaction
  252.     SQL.Strings = (
  253.       'select * from Contact where IDNo = :IDNo'
  254.     )
  255.     Params = <    
  256.       item
  257.         DataType = ftInteger
  258.         Name = 'IDNo'
  259.         ParamType = ptInput
  260.       end>
  261.     Left = 196
  262.     Top = 16
  263.   end
  264.   object DBWrite: TDataSource
  265.     DataSet = WriteQuery
  266.     Left = 252
  267.     Top = 16
  268.   end
  269. end

And here's the pas:
Code: Pascal  [Select][+][-]
  1. unit untEditInfo;
  2.  
  3. {$mode objfpc}{$H+}
  4.  
  5. interface
  6.  
  7. uses
  8.   Classes, SysUtils, DB, SQLDB, Forms, Controls, Graphics, Dialogs, DBCtrls,
  9.   StdCtrls;
  10.  
  11. type
  12.  
  13.   { TfrmEditInfo }
  14.  
  15.   TfrmEditInfo = class(TForm)
  16.     btnSave: TButton;
  17.     btnAbort: TButton;
  18.     DBWrite: TDataSource;
  19.     dbcbxGender: TDBComboBox;
  20.     DBData: TDataSource;
  21.     dbedtAge: TDBEdit;
  22.     dbedtemail: TDBEdit;
  23.     dbedtPhone: TDBEdit;
  24.     dbedtGiven: TDBEdit;
  25.     dbedtFamily: TDBEdit;
  26.     dbmemNotes: TDBMemo;
  27.     dbmemPostal: TDBMemo;
  28.     lblEmail: TLabel;
  29.     lblAge: TLabel;
  30.     lblPhone: TLabel;
  31.     lblGiven: TLabel;
  32.     lbFamily: TLabel;
  33.     lblGender: TLabel;
  34.     lblPostal: TLabel;
  35.     lblNotes: TLabel;
  36.     ReadQuery: TSQLQuery;
  37.     WriteQuery: TSQLQuery;
  38.     procedure btnAbortClick(Sender: TObject);
  39.     procedure btnSaveClick(Sender: TObject);
  40.     procedure DataChanged(Sender: TObject);
  41.     procedure SetupForm(strMode, strID : string);
  42.   private
  43.  
  44.   public
  45.  
  46.   end;
  47.  
  48. var
  49.   frmEditInfo: TfrmEditInfo;
  50.  
  51. implementation
  52.  
  53. var
  54.   strFormMode : string;
  55.   intPersonNo : integer;
  56.   boolChanged : Boolean;
  57. { TfrmEditInfo }
  58.  
  59. procedure TfrmEditInfo.btnAbortClick(Sender: TObject);
  60. begin
  61.   if (boolChanged) then
  62.      if (MessageDlg('Confirm Close', 'Really Close? Any unsaved data will be lost!', mtWarning, [mbYes,mbNo], 0) = mrYes) then
  63.     close();
  64. end;
  65.  
  66. procedure TfrmEditInfo.btnSaveClick(Sender: TObject);
  67.   procedure SetInfoField(strFld, strVal : string);
  68.   begin
  69.     WriteQuery.FieldByName(strFld).AsString:=strVal;
  70.   end;
  71.  
  72.   procedure SetInfoField(strFld : String; intVal : integer);
  73.   begin
  74.     WriteQuery.FieldByName(strFld).AsInteger:=intVal;
  75.   end;
  76. begin
  77.   boolChanged := False;
  78.   WriteQuery.Close();
  79.   WriteQuery.SQL.Text := ReadQuery.SQL.Text;
  80.   WriteQuery.Open();
  81.   if (strFormMode = 'NEW') then
  82.   begin
  83.     WriteQuery.Insert();
  84.     SetInfoField('IDNo', IntPersonNo);
  85.   end
  86.   else
  87.     WriteQuery.Edit;
  88.   SetInfoField('GivenName', dbedtGiven.text);
  89.   SetInfoField('FamilyName', dbedtFamily.Text);
  90.   SetInfoField('phone', dbedtPhone.Text);
  91.   SetInfoField('postal', dbmemPostal.Text);
  92.   SetInfoField('gender', dbcbxGender.Text);
  93.   SetInfoField('email', dbedtEmail.Text);
  94.   SetInfoField('age', StrToInt(dbedtAge.Text));
  95.   SetInfoField('notes', dbmemNotes.Text);
  96.   WriteQuery.Post();
  97.   WriteQuery.ApplyUpdates();
  98.   Close();
  99. end;
  100.  
  101. procedure TfrmEditInfo.DataChanged(Sender: TObject);
  102. begin
  103.      BoolChanged:=True;
  104. end;
  105.  
  106. procedure TfrmEditInfo.SetupForm(strMode, strID : string);
  107. begin
  108.   strFormMode:=strMode;
  109.   intPersonNo:=StrToInt(strID);
  110.   if (strFormMode = 'EDIT') then
  111.   begin
  112.     ReadQuery.SQL.Text:='select * from Contact where IDNo = ' + strID;
  113.     ReadQuery.Open();
  114.   end;
  115. end;
  116.  
  117. {$R *.lfm}
  118.  
  119. end.
  120.  

Thanks for all your help!

rvk

  • Hero Member
  • *****
  • Posts: 6109
Re: TSQLQuery.ExecSQL not making changes
« Reply #49 on: May 13, 2021, 08:12:24 am »
If you are using TDBEdits you don't need to set any fieldbyname.
That's the whole point of the TDBEdit.

Next... You also don't need a separate WriteQuery. You can just switch the ReadQuery to ReadQuery.Edit or .Insert and the fields will automatically be changed (after the ReadQuer.Post and ReadQuery.ApplyUpdates and Transaction.Commit.

If you change  that you'll see you'll have hardly any code at all left  :D

One extra note... You need to decide before/on opening the form if you need to insert or edit. For example on retrieving the record. If recordcount = 0 for the select you need to do the ReadQuery.Insert and if it's 1 you do a ReadQuery.Edit. Then the dataset is in edit,mode and you can change the fields. Your save-button only does the post, applyupdates and commit and close-form.
« Last Edit: May 13, 2021, 08:16:56 am by rvk »

HopefulGuy

  • New Member
  • *
  • Posts: 28
Re: TSQLQuery.ExecSQL not making changes
« Reply #50 on: May 13, 2021, 09:22:58 am »
If you are using TDBEdits you don't need to set any fieldbyname.
That's the whole point of the TDBEdit.

I tried without, and nothing saved at all. I can comment them out and try again, I suppose.

Next... You also don't need a separate WriteQuery. You can just switch the ReadQuery to ReadQuery.Edit or .Insert and the fields will automatically be changed (after the ReadQuer.Post and ReadQuery.ApplyUpdates and Transaction.Commit.

Speaking of Transaction.Commit, the previous example given to me was Query.Transaction.Commit. But if I look at Query.Transaction there IS no Commit routine. Now if I reference the Transaction component directly, it's there. But for some weird reason when I reference it as Query.Transaction the Commit routine is missing.

If you change  that you'll see you'll have hardly any code at all left  :D

We'll see.

One extra note... You need to decide before/on opening the form if you need to insert or edit. For example on retrieving the record. If recordcount = 0 for the select you need to do the ReadQuery.Insert and if it's 1 you do a ReadQuery.Edit. Then the dataset is in edit,mode and you can change the fields. Your save-button only does the post, applyupdates and commit and close-form.

That's already done in SetupForm(). It takes 2 paramaters. A string (NEW or EDIT) and an integer (record number to view or 0 if new record). The mode gets stored in a strFrmMode variable. That variable is queried in the btnSaveEdit() routine. IF mode = NEW then it calls (currently) WriteQuery.Insert. If the mode is EDIT then it calls WriteQuery.Edit


Oh, and you never mentioned how to fix the form so that blank fields are ACCEPTABLE. Not everyone is going to have notes, for example. But currently if the Notes field is empty then I get an Exception saying that notes is required. Same for postal, phone, email, etc...   Really the only fields that should be mandatory (on the form) are Given Name, Family Name, and Age.

rvk

  • Hero Member
  • *****
  • Posts: 6109
Re: TSQLQuery.ExecSQL not making changes
« Reply #51 on: May 13, 2021, 09:36:31 am »
If you are using TDBEdits you don't need to set any fieldbyname.
That's the whole point of the TDBEdit.

I tried without, and nothing saved at all. I can comment them out and try again, I suppose.
You also need to remove the whole WriteQuery. You don't need a second Query to write. You can just use the ReadQuery. It's designed to be read AND write.

Next... You also don't need a separate WriteQuery. You can just switch the ReadQuery to ReadQuery.Edit or .Insert and the fields will automatically be changed (after the ReadQuer.Post and ReadQuery.ApplyUpdates and Transaction.Commit.

Speaking of Transaction.Commit, the previous example given to me was Query.Transaction.Commit. But if I look at Query.Transaction there IS no Commit routine. Now if I reference the Transaction component directly, it's there. But for some weird reason when I reference it as Query.Transaction the Commit routine is missing.
The TSQLQuery.Transaction is a base clase. It always points to a TSQLTransaction but essentially it is just an empty class. You can either use casting like TSQLTransaction(Query.Transaction).Commit or just use SQLTransaction1.Commit directly.

But... if you have set the autocommit option in TSQLQuery that might not even be neccesary.

One extra note... You need to decide before/on opening the form if you need to insert or edit. For example on retrieving the record. If recordcount = 0 for the select you need to do the ReadQuery.Insert and if it's 1 you do a ReadQuery.Edit. Then the dataset is in edit,mode and you can change the fields. Your save-button only does the post, applyupdates and commit and close-form.

That's already done in SetupForm(). It takes 2 paramaters. A string (NEW or EDIT) and an integer (record number to view or 0 if new record). The mode gets stored in a strFrmMode variable. That variable is queried in the btnSaveEdit() routine. IF mode = NEW then it calls (currently) WriteQuery.Insert. If the mode is EDIT then it calls WriteQuery.Edit
Yes.. but you don't do an Insert in SetupForm. You do it far too late in SaveButton.

Oh, and you never mentioned how to fix the form so that blank fields are ACCEPTABLE.
If you do it correctly directly on ReadQuery then you don't have blank fields and it will work automatically. So remove the WriteQuery and it should be fine.

BTW there isn't even a need to pass NEW of EDIT. If you pass a 0 as ID it will mean there is no record found and you want to do insert. If you pass a valid ID there is a record found and you do an Edit.

Something like this:
Code: Pascal  [Select][+][-]
  1. procedure TfrmEditInfo.SetupForm(strID : string);
  2. begin
  3.   intPersonNo:=StrToInt(strID);
  4.   ReadQuery.SQL.Text:='select * from Contact where IDNo = ' + strID;
  5.   ReadQuery.Open();
  6.   if ReadQuery.RecordCount = 0 then
  7.     ReadQuery.Edit
  8.   else
  9.   begin
  10.     ReadQuery.Insert;
  11.     ReadQuery.FieldByName('IDNo').AsInteger := intPersonNo; // so even a nonexisting id > 0 will be ok
  12.   end;
  13. end;

Your saveclick will  be something like this:
Code: Pascal  [Select][+][-]
  1. procedure TfrmEditInfo.btnSaveClick(Sender: TObject);
  2. begin
  3.   ReadQuery.Post();
  4.   ReadQuery.ApplyUpdates();
  5.   TSQLTransaction(ReadQuery).Commit; // maybe not needed but it doesn't hurt
  6.   Close();
  7. end;

That's all you need.

Edit: o, one final note. You have set all the fields in you db to NOT NULL. That's also why you need to fill in everything. Just remove the NOT NULL and you can insert blank fields.
« Last Edit: May 13, 2021, 09:46:56 am by rvk »

HopefulGuy

  • New Member
  • *
  • Posts: 28
Re: TSQLQuery.ExecSQL not making changes
« Reply #52 on: May 13, 2021, 07:44:12 pm »
Oh, and you never mentioned how to fix the form so that blank fields are ACCEPTABLE.
If you do it correctly directly on ReadQuery then you don't have blank fields and it will work automatically. So remove the WriteQuery and it should be fine.

BTW there isn't even a need to pass NEW of EDIT. If you pass a 0 as ID it will mean there is no record found and you want to do insert. If you pass a valid ID there is a record found and you do an Edit.

That's all you need.

Thanks! It works better now.
Edit: o, one final note. You have set all the fields in you db to NOT NULL. That's also why you need to fill in everything. Just remove the NOT NULL and you can insert blank fields.

Actually, in MariaDB (and I think mysql) NOT NULL just means it cannot be a NULL value. An EMPTY value is still perfectly fine. It just stores an empty value rather than recording NULL in the field. At least, that's been my experience.

rvk

  • Hero Member
  • *****
  • Posts: 6109
Re: TSQLQuery.ExecSQL not making changes
« Reply #53 on: May 13, 2021, 07:57:44 pm »
Actually, in MariaDB (and I think mysql) NOT NULL just means it cannot be a NULL value. An EMPTY value is still perfectly fine. It just stores an empty value rather than recording NULL in the field. At least, that's been my experience.
I wasn't sure what the value would be if the TDBEdit would be empty. I thought it might have been NULL in that case. But if it works now I guess it's just an empty string.

I don't use NOT NULL in my Firebird database in the case of normal strings so NULL values are also valid. My own framework even makes empty TDBEdits automatically NULL before saving. But that's up to you.

egsuh

  • Hero Member
  • *****
  • Posts: 1273
Re: TSQLQuery.ExecSQL not making changes
« Reply #54 on: May 14, 2021, 12:21:19 am »
Quote
BTW there isn't even a need to pass NEW of EDIT. If you pass a 0 as ID it will mean there is no record found and you want to do insert. If you pass a valid ID there is a record found and you do an Edit.

Something like this:

    procedure TfrmEditInfo.SetupForm(strID : string);
    begin
      intPersonNo:=StrToInt(strID);
      ReadQuery.SQL.Text:='select * from Contact where IDNo = ' + strID;
      ReadQuery.Open();
      if ReadQuery.RecordCount = 0 then
        ReadQuery.Edit
      else
      begin
        ReadQuery.Insert;
        ReadQuery.FieldByName('IDNo').AsInteger := intPersonNo; // so even a nonexisting id > 0 will be ok
      end;
    end;


Isn't ReadQuery.Edit automatically done if cursor moves between records in this case?  I think what is definitely necessary is ReadQuery.Insert to insert new record. It is not necessary to open one record every time whenever an id is selected if the whole dataset is not very large.

Quote
By the way, if I want to branch and read from a second table and have DB Aware controls read from the second, then I take it I need another TSQLQuery and Another TDatasource? Right now, notes is just a text field. What I'd like to do is convert it into a separate table with both text and image components.

The second form --- which is said to have one memo field and one image field --- if these two fields are from the same table of the first form, then no new Query is necessary. DBMemo and DBImage components may share the same datasource of form 1.  But if it is one-to-many relationship between first table and second table, then second query is necessary.

 

TinyPortal © 2005-2018