TSQLQuery.ExecSQL not making changes
lazarus / 16-Databases

----------------------------------------------------------------------
HopefulGuy · 2021-05-10 04:51

Okay, I'm sure I'm just missing something here. But I've read the docs
and am at a loss.
I'm trying to make a form that displays a MariaDB record, and if I
change the form and press the SAVE button it writes everything back to
the record. So I tried something like this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Procedure
TfrmData.btnSaveClick(Sender: TObject);  procedure SetField(FieldName:
String; Data : String);  begin   
Query.FieldByName(FieldName).AsString:=Data;  end;  procedure
SetField(FieldName: String; Data: Integer);  begin   
Query.FieldByName(FieldName).AsInteger:=Data;  end;begin  Query.Edit; 
if (FrmMode = 'NEW') then    Query.SQL.Text:='inert into Table1 values
(0, :Gender, :GivenName, :FamilyName, :Age,  :Email,  :Phone,
:Postal)'  else  begin    Query.SQL.Text:='update Table1 set
Gender=:Gender GivenName=:GivenName, FamilyName:=FamilyName, Age=:Age,
, Email=:Email, Phone=:Phone, Postal=:Postal where IDNo=:IDNo';   
SetField('IDNo', IDNo);  end;  setField('Gender', cbxGender.Text); 
setField('GivenName', edtGiven.Text);  setField('FamilyName',
edtFamily.Text);  setField('Age', StrToInt(trim(medtAge.text))); 
setField('Email', edtEmail.Text);  SetField('Phone', edtPhone.text); 
SetField('Postal', memPostal.Lines.Text);  Query.ExecSQL(); 
Query.Close();end; But when I go back and look at the actual database
after I run this procedure, NOTHING HAS CHANGED.   Can anyone see what
I did wrong?

----------------------------------------------------------------------
Gustavo 'Gus' Carreno · 2021-05-10 05:21

Hey HopefulGuy,

Could I, very politely, ask for you to use the code tags when putting
code examples here?

Apart from the syntax highlighting, if and when you publish a bigger
example, it will create a scrollable box that will be easier for
anyone looking at your code AND your message at the same time.

Thank you very much in advance.

Cheers,
Gus

----------------------------------------------------------------------
HopefulGuy · 2021-05-10 05:26

Hopefully that fixed things for you.

----------------------------------------------------------------------
Gustavo 'Gus' Carreno · 2021-05-10 05:33

Hey HopefulGuy,

Quote from: HopefulGuy on May 10, 2021, 07:26:45 amHopefully that
fixed things for you.  

Yes it did and thank you very much !!!!

Cheers,
Gus

----------------------------------------------------------------------
Gustavo 'Gus' Carreno · 2021-05-10 05:38

Hey HopefulGuy,

After looking at your code I have a question:

Why do you start your code with the variable Query and put it on Edit
mode, but then use AsianLadiesQuery to execute the SQL?

Is this a copy/paste error?

Cheers,
Gus

----------------------------------------------------------------------
rvk · 2021-05-10 05:41

Just looking at your code I see multiple errors.

Your insert statement reads INERT.
And in your update statement you are missing a comma after :gender.
These errors should have given you an exception when executed them.

If these are typos.... Then please don't just edit your code because
it can create these typos.
Post actual code.

Then another observation... You fill in Query.
But you actually perform the ExecSQL on a AsianLadiesQuery.
AsianLadiesQuery has nothing to do with Query so you actually never
perform the update or insert.

And... You can't set Query.SQL.Text when you are in browse or edit
mode (and you are with Query.Edit in edit mode).

----------------------------------------------------------------------
HopefulGuy · 2021-05-10 05:44

I'm coming from a BDE perspective, so perhaps I'm doing it wrong.  But
before I put the Edit in there, when I tried to assign the fields it
gave me an error about the database not being in edit mode. So I
inserted the Edit statement. As for the Query, That part was a
copy/paste error. AsianGalsQuery was replaced with Query when I
expanded the database. I've made edits to the code shown here to fix
that.

----------------------------------------------------------------------
rvk · 2021-05-10 05:51

You still have invalid SQL statement (INERT and that missing comma
after :gender).

Second... You can't use statements like insert and update an a
dataset. If you use Query.SQL.Edit and Query.Findbyfield, you can't
use insert/update and ExelSQL. You just use Query.Edit and Query.Post
in conbination with Query.Findbyfield.

If you use INSERT/UPDATE in SQL.Text you'll need to use
Query.Parambyname to fill in the parameter fields.

Now you are mixing two methods if updating (Query.Edit and
Query.ExelSql) and you can't do that.

https://wiki.freepascal.org/Working_With_TSQLQuery

----------------------------------------------------------------------
HopefulGuy · 2021-05-10 05:54

Quote from: rvk on May 10, 2021, 07:41:25 amJust looking at your code
I see multiple errors.

Your insert statement reads INERT.

And in your update statement you are missing a comma after :gender.
These errors should have given you an exception when executed them.

Oops. Thanks for pointing those out. They are both fixed now.

QuoteIf these are typos.... Then please don't just edit your code
because it can create these typos.
Post actual code.

Then another observation... You fill in Query.
But you actually perform the ExecSQL on a AsianLadiesQuery.
AsianLadiesQuery has nothing to do with Query so you actually never
perform the update or insert.

Mis-Copy, see above. Didn't realize I had two versions of the file
open and accidentally copied part from new file some from old file. In
the new file, AsianLadiesQuery has been completely replaced by Query.
QuoteAnd... You can't set Query.SQL.Text when you are in browse or
edit mode (and you are with Query.Edit in edit mode).
Interesting. It doesn't throw any errors. I have just changed the code
ever so slightly. Observe this section now and see if you think it
will work better:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i begin  if
(FrmMode = 'NEW') then  begin    Query.SQL.Text:='insert into Table1
values (0, :Gender, :GivenName, :FamilyName, :Age,  :Email,  :Phone,
:Postal)';    AsianLadiesQuery.Edit;  end  else  begin   
Query.SQL.Text:=update Table1 set Gender=:Gender GivenName=:GivenName,
FamilyName:=FamilyName, Age=:Age, Email=:Email, Phone=:Phone,
Postal=:Postal where IDNo=:IDNo'    AsianLadiesQuery.Edit;  
 SetField('IDNo', LadyIDNo);  end;  setField('GivenName',
edtGiven.Text);

----------------------------------------------------------------------
HopefulGuy · 2021-05-10 05:58

Quote from: rvk on May 10, 2021, 07:51:54 amSecond... You can't use
statements like insert and update an a dataset. If you use
Query.SQL.Edit and Query.Findbyfield, you can't use insert/update and
ExelSQL. You just use Query.Edit and Query.Post in conbination with
Query.Findbyfield.

If you use INSERT/UPDATE in SQL.Text you'll need to use
Query.Parambyname to fill in the parameter fields.

Now you are mixing two methods if updating (Query.Edit and
Query.ExelSql) and you can't do that.

https://wiki.freepascal.org/Working_With_TSQLQuery
I will look again. I did read that and kind of got lost. Maybe because
of my BDE perspective.

Anyway, I cleaned up the code. This is now what shows, copied directly
from the window. There may be errors yet because I haven't tried
recompiling yet. So stupid syntax errors are a possibility.
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TfrmData.btnSaveClick(Sender: TObject);  procedure SetField(FieldName:
String; Data : String);  begin   
Query.Params.ParamByName(FieldName).AsString:=Data;  end;  procedure
SetField(FieldName: String; Data: Integer);  begin   
Query.Params.ParamByName(FieldName).AsInteger:=Data;  end;begin  if
(FrmMode = 'NEW') then  begin    Query.SQL.Text:='insert into Table1
values (0, :GivenName, :FamilyName, :Age, :Country, :Email,  :Phone,
:Postal)';//    Query.Edit;  end  else  begin   
Query.SQL.Text:='update Table1 set GivenName=:GivenName,
FamilyName:=FamilyName, Age=:Age, Country=:Country, Email=:Email,
Phone=:Phone, Postal=:Postal, where IDNo=:IDNo';//    Query.Edit;   
SetField('IDNo', IDNo);  end;  setField('GivenName', edtGiven.Text); 
setField('FamilyName', edtFamily.Text);  setField('Age',
StrToInt(trim(medtAge.text)));  SetField('Country', cbxCountry.Text); 
setField('Email', edtEmail.Text);  SetField('Phone', edtPhone.text); 
SetField('Postal', memPostal.Lines.Text);  Query.ExecSQL(); 
Query.Close();  Close();end;

----------------------------------------------------------------------
rvk · 2021-05-10 06:00

You're still have that AsianLadiesQuery in there and you fill in
Query.

Don't use INSERT and UPDATE with Query.Edit.
They don't mix.
Use INSERT/UPDATE with Query.Parambyname and Query.ExelSQL
OR
Use Query.Edit with Query.Findbyname and Query.Post.
But don't mix them.

Please copy and paste actual code because you're still missing the
comma after :gender and mussing quotes around the statement.

----------------------------------------------------------------------
HopefulGuy · 2021-05-10 06:20

Re-read the code. I fixed it but when I tried to post my message it
said you had posted a message. So, the code shown in #9 is correct.

----------------------------------------------------------------------
rvk · 2021-05-10 07:00

Yes, that looks better.
Final note... You shouldn't use Query.Close() on a ExecSQL.

With SELECT you get a result-dataset and you do need Open/Close.
With UPDATE you don't get a result-dataset and you only use ExecSQL
and no Open/Close.

----------------------------------------------------------------------
Zvoni · 2021-05-10 09:12

Not knowing, what connection-parameters are set, i'm missing the
Transaction.Commit after an UPDATE/INSERT

----------------------------------------------------------------------
egsuh · 2021-05-10 09:15

You have to check whether the errors come from DB server or Pascal.
Following statement still has error. You have written

FamilyName := FamilyName,  which should be FamilyName = :FamilyName

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
Query.SQL.Text:='update Table1 set GivenName=:GivenName,
FamilyName:=FamilyName, Age=:Age, Country=:Country, Email=:Email,
Phone=:Phone, Postal=:Postal, where IDNo=:IDNo';
And there should not be a comma (,) before where.

Please run your code and look at the error report whether it is SQL
systax error or Free Pascal Execution error. SQL Syntax error will
raise Pascal Execution error.

----------------------------------------------------------------------
HopefulGuy · 2021-05-10 17:15

Quote from: Zvoni on May 10, 2021, 11:12:37 amNot knowing, what
connection-parameters are set, i'm missing the Transaction.Commit
after an UPDATE/INSERT

Do I need to use a transaction? I didn't have a transaction.start or
anything. I don't see the point of a transaction when this is never
going to be multi-user/multi-access.

----------------------------------------------------------------------
Zvoni · 2021-05-10 17:53

IIRC from another thread, a Transaction is mandatory

----------------------------------------------------------------------
HopefulGuy · 2021-05-10 18:13

It's still not working. I added a commit line
(SQLTransaction1.Commit(); ) , I ran the program. It reported no
errors at all. But when I look at the database, the fields I changed
in the program are unchanged on disk. So what am I doing wrong now?

----------------------------------------------------------------------
af0815 · 2021-05-10 19:15

I use often  aQuery.Options := [sqoAutoApplyUpdates,sqoAutoCommit] ,
if not i have to apply the updates and do the transaction in the
correct position by myself.

----------------------------------------------------------------------
HopefulGuy · 2021-05-10 21:08

Quote from: af0815 on May 10, 2021, 09:15:22 pmI use often 
aQuery.Options := [sqoAutoApplyUpdates,sqoAutoCommit] , if not i have
to apply the updates and do the transaction in the correct position by
myself.

Interesting. And how does that work? I'm still learning the database
engine, so forgive me for potentially stupid questions. If I set the
query options to autocommit and autoapplyupdates do I just say
query.FieldByName('Postal').AsString := 'This is my address' or
something?

IMHO the documentation for the SQL engine needs to be rewriten and
expanded. What's there is good, but it's hard for me to find anything.

I tried this, and it's still not working. I have the query set for
AutoApplyUpdates and AutoCommit. But when I call
Query.FieldByName('age').AsInteger := strtoint(trimmedtAge.Text))
nothing gets updated. No errors are thrown, everything SEEMS to be
working well. But all updates are discarded.

----------------------------------------------------------------------
egsuh · 2021-05-11 03:34

Transaction works as temporary storage of changes to the database.
When you change database with insert, update or delete, the changes
are stored in the temporary memory, and saved to the physical media
when transaction is committed or discarded if transaction is
rollbacked.

So if you are using TSQLConnection then you should have a transaction
linked to the database.

The general process is in the following order. You can set the
transaction as autocommit but I'll write them down so that you can
understand the concept and process.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
Query1.Close;Query1.SQL.Text := 'insert into table1 values (:v1, :v2,
...);';if not Transaction.Active then Transaction.StartTransaction;  
// Transaction := Active does the same thing. Query1.Prepare;
Query1.ParamByName('v1').AsString:= string1;
Query1.ParamByName('v2').AsString:= string2;// ...................
Query1.ExecSQL;   Transaction.Commit; // permanently store changes
since the start of transaction. or transaction.rollback to discard the
changesQuery1.Close; // this is not necessary, but anyway
conceptually. 
You may not explicitly start and commit transaction by setting them
automatically do them, but anyway this is the flow of SQL operation.

And Query.FieldByName('Postal').AsString := 'my address' will change
the local dataset of Query (which is a copy of whole table, in your
PC), and do not change the record in the server. To do that, you have
to call ApplyUpdate, etc. But your examples are not related with this.
You are using SQL statements.  You should write
Query.ParamByName('Postal').AsString, instead.

----------------------------------------------------------------------
af0815 · 2021-05-11 07:31

Is the Data tranfered from the local Buffer to the DB-Server ? I did
not found any ApplyUpdates ? See
https://wiki.freepascal.org/Working_With_TSQLQuery chapter Cached
Updates. This is the reason for sqoAutoApplyUpdates in the
queryoptions.

sqoAutoCommit is for automatic tranactionhandling. If you have no
special code for not using this, it is the easiest way. You need
transaction handling manual, if you must make safe if eg. in one table
the data is deleted and inserted in another table. With the
transaction you can say: "All or nothing". So you can avoid
inconsistence of data.

----------------------------------------------------------------------
Zvoni · 2021-05-11 07:37

Quote from: HopefulGuy on May 10, 2021, 08:13:39 pmIt's still not
working. I added a commit line (SQLTransaction1.Commit(); ) , I ran
the program. It reported no errors at all. But when I look at the
database, the fields I changed in the program are unchanged on disk.
So what am I doing wrong now?
And your Transaction is correctly linked to the Connection and Query?

----------------------------------------------------------------------
egsuh · 2021-05-11 09:32

QuoteIs the Data tranfered from the local Buffer to the DB-Server ? I
did not found any ApplyUpdates ? See
https://wiki.freepascal.org/Working_With_TSQLQuery chapter Cached
Updates. This is the reason for sqoAutoApplyUpdates in the
queryoptions.
There are methods of TCustomBufDataSet.ApplyUpdates and
TCustomSQLQuery.ApplyUpdates, which are ancestors of TSQLQuery.

This is used when you open TSQLDataset using select SQL statements, do
some modifications to it (using TDataSet's edit, insert, post, etc.),
and ApplyUpdates will save the changes. Primary key or key fields must
be pre-defined. I have used this, but not frequently, and do not know
exactly how sqoAutoApplyUpdates, etc. operate. I prefer to use SQL
statement updateorinsert, using matching fields.

----------------------------------------------------------------------
af0815 · 2021-05-11 11:14

Back to the roots. Have your table a PK (Primary Key) ? Is this key
listed in the select statement ?

If you have a good, simple Select-Statement without joins, Query is
able to autocreate the nessesary insert, update, delete messages by
itself.

You mus only set the dataset in the correct state. For a simple test,
put a connetion, transaction, query, dataset, dbgrid and a dbnavigator
on a form. Connect these components together and set a select
statement in the query. In the designer, if you make the connection
and the query active you will see the actual data in the db. Make it
inactive. Now make a button and set in the click the connection and
query active. Make a second button and place there the applyupdates an
make the connection inactive. Now run your program. You should able to
browse, edit, delete insert data with the navigator and the grid.

If you close the programm and reopen, your changes should be saved. If
this work you have the basics.

----------------------------------------------------------------------
Zvoni · 2021-05-11 13:57

Quote from: af0815 on May 11, 2021, 01:14:10 pmBack to the roots. Have
your table a PK (Primary Key) ? Is this key listed in the select
statement ?

If you have a good, simple Select-Statement without joins, Query is
able to autocreate the nessesary insert, update, delete messages by
itself.

You mus only set the dataset in the correct state. For a simple test,
put a connetion, transaction, query, dataset, dbgrid and a dbnavigator
on a form. Connect these components together and set a select
statement in the query. In the designer, if you make the connection
and the query active you will see the actual data in the db. Make it
inactive. Now make a button and set in the click the connection and
query active. Make a second button and place there the applyupdates an
make the connection inactive. Now run your program. You should able to
browse, edit, delete insert data with the navigator and the grid.

If you close the programm and reopen, your changes should be saved. If
this work you have the basics.

DON'T! Your Lazarus will go KABOOM!
It's a Bug, and fixed in Trunk

See here: https://forum.lazarus.freepascal.org/index.php/topic,53653.m
sg397188.html#msg397188

----------------------------------------------------------------------
af0815 · 2021-05-11 15:01

Ok, my Lazarus with mssqldb driver did not know about the Kaboom
error. So save early, save often :-)

----------------------------------------------------------------------
HopefulGuy · 2021-05-11 16:11

Quote from: Zvoni on May 11, 2021, 09:37:25 amQuote from: HopefulGuy
on May 10, 2021, 08:13:39 pmIt's still not working. I added a commit
line (SQLTransaction1.Commit(); ) , I ran the program. It reported no
errors at all. But when I look at the database, the fields I changed
in the program are unchanged on disk. So what am I doing wrong now?
And your Transaction is correctly linked to the Connection and Query?

Yes, I have a Query object that is linked to the Tansaction item. The
Connection and DataSource are present on the parent form. The data is
Read in just fine. It's assigned the proper fields on the form and
shows up fine. But if I change something and hit the save button, it
goes through all the assignments, and nothing happens. I even have a
Transaction.Commit at the end, and nothing. I believe I don't need the
Query.ExecSQL

----------------------------------------------------------------------
af0815 · 2021-05-11 17:09

ExecSQl is only for insert, update and delete statements meaningfull,
For a normal query with select as base and autocreated insert, delet
and update stemants you should only use open (or active).

----------------------------------------------------------------------
rvk · 2021-05-11 17:16

Quote from: HopefulGuy on May 11, 2021, 06:11:48 pmI even have a
Transaction.Commit at the end, and nothing. I believe I don't need the
Query.ExecSQL
Please post the code you have now.
(Your previous code mixed different methods and was incorrect)

----------------------------------------------------------------------
HopefulGuy · 2021-05-11 21:15

Quote from: rvk on May 11, 2021, 07:16:10 pmPlease post the code you
have now.
(Your previous code mixed different methods and was incorrect)

Code currently looks like this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TfrmData.btnSaveClick(Sender: TObject);  procedure SetField(FieldName:
String; Data : String);  begin   
Query.FieldByName(FieldName).AsString:=Data;  end;  procedure
SetField(FieldName: String; Data: Integer);  begin   
Query.FieldByName(FieldName).AsInteger:=Data;  end;begin(*
 SQLTransaction1.StartTransaction();  Query.Edit;  if (FrmMode =
'NEW') then   Query.SQL.Text:='insert into Table1 values (0, :Gender,
:GivenName, :FamilyName, :Age, :ImgCount, :Email, :Phone, :Postal)' 
else    Query.SQL.Text:='update Table1 set ChatLocation=:Gender,
:GivenName, :FamilyName, :Age, :ImgCount, :Email, :Phone, :Postal
where IDNo=:IDNo'; *)  setField('Gender', cbxContactLocation.Text); 
setField('GivenName', edtGiven.Text);  setField('FamilyName',
edtFamily.Text);  setField('Age', StrToInt(trim(medtAge.text))); 
setField('Email', edtEmail.Text);  SetField('Phone', edtPhone.text); 
SetField('Postal', memPostal.Lines.Text);//  Query.ExecSQL(); 
SQLTransaction1.Commit();  Close();end;

----------------------------------------------------------------------
dseligo · 2021-05-11 21:41

Try to uncomment 'insert', 'update' and 'ExecSQL' lines. 

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TfrmData.btnSaveClick(Sender: TObject);      procedure
SetField(FieldName: String; Data : String);      begin       
Query.ParamByName(FieldName).AsString:=Data;      end;      procedure
SetField(FieldName: String; Data: Integer);      begin       
Query.ParamByName(FieldName).AsInteger:=Data;      end;    begin     
SQLTransaction1.StartTransaction();    //  Query.Edit; <-- no edit 
Query.Close;      if (FrmMode = 'NEW') then     
 Query.SQL.Text:='insert into Table1 values (0, :Gender, :GivenName,
:FamilyName, :Age, :ImgCount, :Email, :Phone, :Postal)'      else    
  Query.SQL.Text:='update Table1 set ChatLocation=:Gender, :GivenName,
:FamilyName, :Age, :ImgCount, :Email, :Phone, :Postal where
IDNo=:IDNo';      setField('Gender', cbxContactLocation.Text);     
setField('GivenName', edtGiven.Text);      setField('FamilyName',
edtFamily.Text);      setField('Age', StrToInt(trim(medtAge.text))); 
   setField('Email', edtEmail.Text);      SetField('Phone',
edtPhone.text);      SetField('Postal', memPostal.Lines.Text);      if
(FrmMode <> 'NEW') then        SetField('IDNo', ExistingID);     
Query.ExecSQL();      SQLTransaction1.Commit();      Close();    end;

----------------------------------------------------------------------
rvk · 2021-05-11 22:34

Yup. Your mixing your methods again.

Where is your Query.Post ???
And where is the assignment of :idno for the update ?

@dseligo, do not use Query.Fieldbyname with a direct insert/update in
Query.SQL.Text. It just doesn't work.

@HopefulGuy only use the correct statements with each other.

(following on an open query with a result-set)
Query.Edit or Query.Insert (with a select in sql.text so you have a
result dataset)
Query.Fieldbyname
Query.Post
Query.Applyupdates
Query.Transaction.commit (note that the open dataset is closed unless
you add something to the options)

or (and this really means OR)

Query.Sql.text with insert or update (with :variablefield as params)
Query.Params.parambyname (with :idno when update)
Query.ExecSql

That's it. Don't mix any of these commands.

Sometimes it is easier to work with an open result-dataset (if you
have already opened one for a sbgrid for example). And sometimes it's
easier to encase updatimg a record in a separate procedure where you
update it in a separate form.

Do note that if you update a record with a separate update, you'll
need to reopen any other result-dataset to show the update there too.

----------------------------------------------------------------------
HopefulGuy · 2021-05-11 23:40

Quote from: dseligo on May 11, 2021, 11:41:48 pmTry to uncomment
'insert', 'update' and 'ExecSQL' lines. 

Just tried. No effect. All three lines are uncommented. Here's the
code now (including extra lines I threw in just to ensure I was
getting the right values from the form):

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TfrmData.btnSaveClick(Sender: TObject);  procedure SetField(FieldName:
String; Data : String);  begin   
Query.FieldByName(FieldName).AsString:=Data;  end;  procedure
SetField(FieldName: String; Data: Integer);  begin   
Query.FieldByName(FieldName).AsInteger:=Data;  end;var testint :
integer;    teststr : string;begin 
SQLTransaction1.StartTransaction();  Query.Edit;  if (FrmMode = 'NEW')
then   Query.SQL.Text:='insert into Table1 values (0, :Gender,
:GivenName, :FamilyName, :Age, :ImgCount, :Email, :Phone, :Postal)' 
else    Query.SQL.Text:='update Table1 set ChatLocation=:Gender,
:GivenName, :FamilyName, :Age, :ImgCount, :Email, :Phone, :Postal
where IDNo=:IDNo';  setField('Gender', cbxContactLocation.Text); 
setField('GivenName', edtGiven.Text);  setField('FamilyName',
edtFamily.Text);  teststr := trim(medtAge.text);  testint :=
StrToInt(trim(medtAge.text));  setField('Age',
StrToInt(trim(medtAge.text)));  setField('Email', edtEmail.Text); 
SetField('Phone', edtPhone.text);  SetField('Postal',
memPostal.Lines.Text);  Query.ExecSQL();  SQLTransaction1.Commit(); 
Close();end;

----------------------------------------------------------------------
dseligo · 2021-05-12 00:43

Quote from: rvk on May 12, 2021, 12:34:03 am@dseligo, do not use
Query.Fieldbyname with a direct insert/update in Query.SQL.Text. It
just doesn't work.

I just copied his example, didn't even noticed that. Instead of
FieldByName it should be ParamByName.

----------------------------------------------------------------------
dseligo · 2021-05-12 00:48

Quote from: HopefulGuy on May 12, 2021, 01:40:57 amQuote from: dseligo
on May 11, 2021, 11:41:48 pmTry to uncomment 'insert', 'update' and
'ExecSQL' lines. 

Just tried. No effect. All three lines are uncommented. Here's the
code now (including extra lines I threw in just to ensure I was
getting the right values from the form):

I started post as a joke because you had most of DB related stuff
commented. I corrected code I posted, try it now.
And you need to provide 'id' if row already exists (in the ExistingID
variable).

----------------------------------------------------------------------
egsuh · 2021-05-12 01:44

Based on your explanations, I think what you need is following codes.
Set transaction to auto-commitretaining.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TfrmData.btnSaveClick(Sender: TObject);  procedure SetField(FieldName:
String; Data : String);  begin   
Query.FieldByName(FieldName).AsString:=Data;                // Not
query.parambyname  end;  procedure SetField(FieldName: String; Data:
Integer);  begin    Query.FieldByName(FieldName).AsInteger:=Data; 
end;begin    //  SQLTransaction1.StartTransaction();   //
 Query.Edit;  if (FrmMode = 'NEW') then Query.insert   // you need
this     //  Query.SQL.Text:='insert into Table1 values (0, :Gender,
:GivenName, :FamilyName, :Age, :ImgCount, :Email, :Phone, :Postal)' 
else Query.Edit;     // and this   //  Query.SQL.Text:='update Table1
set ChatLocation=:Gender, :GivenName, :FamilyName, :Age, :ImgCount,
:Email, :Phone, :Postal where IDNo=:IDNo'; *)   setField('Gender',
cbxContactLocation.Text);  setField('GivenName', edtGiven.Text); 
setField('FamilyName', edtFamily.Text);  setField('Age',
StrToInt(trim(medtAge.text)));  setField('Email', edtEmail.Text); 
SetField('Phone', edtPhone.text);  SetField('Postal',
memPostal.Lines.Text);    Query.Post;    Query.ApplyUpdates;     //
 Query.ExecSQL();  // SQLTransaction1.Commit();   // set this to auto-
commitretaining   //  Query.Close();    end;
Or, you should use  insert/update/delete + parambyname not fieldbyname
+  ExecSQL , as rvk pointed out.

----------------------------------------------------------------------
HopefulGuy · 2021-05-12 01:52

Quote from: rvk on May 12, 2021, 12:34:03 amYup. Your mixing your
methods again.

Where is your Query.Post ???
And where is the assignment of :idno for the update ?

@dseligo, do not use Query.Fieldbyname with a direct insert/update in
Query.SQL.Text. It just doesn't work.

@HopefulGuy only use the correct statements with each other.

(following on an open query with a result-set)
Query.Edit or Query.Insert (with a select in sql.text so you have a
result dataset)
Query.Fieldbyname
Query.Post
Query.Applyupdates
Query.Transaction.commit (note that the open dataset is closed unless
you add something to the options)

I see. I am getting mixed messages that's why it's not working. I
updated it and things look good on that end. Now for a related
question. I updated the (incomplete) code to include querying from
another table, one with a One-To-Many relationship with Table1 (i.e 1
record in Table1 can have multiple records in Table2 referencing it).
That works great for the select, but when I try to update something
screwy goes on. Here's what I tried, but I get an error saying the
operation cannot be performed on an inactive dataset:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TfrmLadyData.btnSaveClick(Sender: TObject);    procedure
SetField(FieldName: String; Data : String);    begin      if
(trim(data)+' ' = ' ') then         data := #0;     
Query.FieldByName(FieldName).AsString:=Data;    end;    procedure
SetField(FieldName: String; Data: Integer);    begin     
Query.FieldByName(FieldName).AsInteger:=Data;    end;  begin    if
(FrmMode = 'NEW') then    begin      Query.Insert();     
SetField('IDNo', 0); // Auto-Increment field, so 0 = next available
number      btnSave.Caption:='Add Record';    end    else    begin   
 Query.Close();      Query.SQL.Text := 'select * from Table1 where
IDNo = ' + IntToStr(IDNO);      Query.open();      Query.Edit();     
btnSave.Caption:='Update Record';    end;  setField('Gender',
cbxContactLocation.Text);  setField('GivenName', edtGiven.Text); 
setField('FamilyName', edtFamily.Text);  setField('Age',
StrToInt(trim(medtAge.text)));  setField('Email', edtEmail.Text); 
SetField('Phone', edtPhone.text);  SetField('Postal',
memPostal.Lines.Text);  Query.ExecSQL();  SQLTransaction1.Commit(); 
Close();end;

----------------------------------------------------------------------
egsuh · 2021-05-12 03:07

I know that you have background of BDE. What you are thinking is
operations on table. Now you are using TSQLQuery using select
statement. TTable or TSQLQuery are descendants of TDataSet. So, when
we say dataset, it means a structure that lie behind a table
structure.

When you open TSQLQuery using 'select * from table...', then you have
a local copy of the records, and what you see in the DBGrid or DBEdit
fields, or values from Query.FieldByName('fieldname').AsString are all
from this copy, not physical database in the server.

TDataSet.insert, TDataSet.edit, TDataSet.Post, etc. are operations on
this local copy, not server file. To apply the changes on this local
copy to server file, you have to call TDataSet.ApplyUpdates.

SQL statement  'insert into table..' is different from
TDataSet.Insert. This statement operates directly on server database,
and has nothing to do with current open dataset.


So in your program, with your open Query, i.e. Dataset, :

1) Query.insert should be done on active dataset.

2) If you want Query.Edit and ApplyUpdates, you have to find the
record using Locate, not SQL statement ('select * ...).


Now, to move on to SQL programming,

Use separate TSQLQuery --- I mean drop another TSQLQuery component,
and use it for SQL statements of 'insert into table1 values ...' or
'update table1 set ....' etc.

----------------------------------------------------------------------
HopefulGuy · 2021-05-12 05:36

Quote from: egsuh on May 12, 2021, 05:07:21 amNow, to move on to SQL
programming,

Use separate TSQLQuery --- I mean drop another TSQLQuery component,
and use it for SQL statements of 'insert into table1 values ...' or
'update table1 set ....' etc.

Okay, so Basically You're indicating that I should have one TSQLQuery
for each table I intend to access?

Seems overkill, but okay. So let me see if I have the logic in
connecting the components together correct. There are four components:
A: TSQLQuery
B: TSQLTransaction
C: TDataSource
D: TSQLConnector (or decendant)

The links go like this, right?
A.Database := D
B.Database := D
C.Database := D
D.Transaction := B

Is that right?

----------------------------------------------------------------------
egsuh · 2021-05-12 05:54

You should have followings and hook them in following way.

A.  TSQLConnection
B.  TSQLTransaction   Database=A.
C.  TSQLQuery           Database=A.      for Table1.   SQL.text: 
select * from table1 where...
D.  TSQLQuery           Database=A.      for Tabe2.    SQL.text: 
select * from table2 where...
E.  TSQLQuery           Database=A.      for update records. SQL.Text
:  update or insert into table1 or table2 values...


If you are using Data-aware controls, like TDBGrid, TDBNavigator,
TDBEditor, etc., then you need

F.   TDataSource      DataSet is  C or D. 
G.  TDBGrid, TDBNavigator,  TDBEdit          Datasource=F.

----------------------------------------------------------------------
rvk · 2021-05-12 12:15

Quote from: HopefulGuy on May 12, 2021, 03:52:14 amThat works great
for the select, but when I try to update something screwy goes on.
Here's what I tried, but I get an error saying the operation cannot be
performed on an inactive dataset:
About this code:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
Query.ExecSQL();SQLTransaction1.Commit();Close();
You still have ExecSQL with a "select" in SQL.Text.
I already told (twice) that that doesn't mix.

About the last line... the Close(). What does this close? Was it meant
to close the entire Form/Application (because it does).
If it was meant to close the dataset it should have been
Query.Close();
And then only if you have an Open somewhere.

The Query itself... where is it defined. Do you use it elsewhere (in a
DBGrid for example).
This is important. Because if you have a DBGrid with edit fields
besides it to edit a record... ALL your code can be much much much
much easier if you just use TDBEdits (which someone already
suggested).

So we can't judge the current code if we don't have the complete
picture.

----------------------------------------------------------------------
rvk · 2021-05-12 18:16

You can almost program an entire address-editing program with zero
lines of code if you use TDBEdits and a TDBNavigator

----------------------------------------------------------------------
HopefulGuy · 2021-05-13 00:41

Quote from: rvk on May 12, 2021, 08:16:28 pmYou can almost program an
entire address-editing program with zero lines of code if you use
TDBEdits and a TDBNavigator 

That's a nice idea, but I seem to he having issues. I had to start
over because the thumb drive I had the files on died. So I created a
new database, inserted a single bogus record for testing, and started
rebuilding. Now, on the information form, I have all Data-Aware
components, with fields defined and pointing to the dataset, but they
are not getting populated.

Also, for some reason, the first call to a no-condition query works
fine. But any subsequent calls return NO records. Here's the table:
Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  CREATE
TABLE `Contact` (  `IDNo` int(11) NOT NULL AUTO_INCREMENT, 
`GivenName` varchar(20) NOT NULL,  `FamilyName` varchar(20) NOT NULL, 
`email` varchar(90) NOT NULL,  `age` tinyint(3) unsigned NOT NULL, 
`phone` varchar(15) NOT NULL,  `postal` text NOT NULL,  `notes` text
NOT NULL,  PRIMARY KEY (`IDNo`),  UNIQUE KEY `Names`
(`GivenName`,`FamilyName`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 
Here's the one record:
Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i +------+---
--------+------------+---------------+------+----------------+--------
+-------+--------+| IDNo | GivenName | FamilyName | email         |
age  | phone          | postal | notes | gender |+------+-----------+-
-----------+---------------+------+----------------+--------+-------+-
-------+|    1 | Jane      | Doe        | spam@spam.com |   19 |
+0000000000000 |        |       | Female |+------+-----------+--------
----+---------------+------+----------------+--------+-------+--------
+ 
The query has a hard coded SQL (i.e. defined in the lfm file, not in
the pas) of a simple query: select * from Contact order by IDNo;
Here's the code that is SUPPOSED to correctly repopulate a ComboBox:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TfrmList.FormUpdate;var sCBXEntry : string;    intListCount :
integer;    strSQLExecuted : string;begin   
 cbxContactList.items.Clear;     cbxContactList.ItemIndex:=-1;  try  
if not (connection.Connected) then       connection.Connected:=true;
// hope thatworks       try         ReadQuery.close(); // in order to
force the Query to re-request info from the database engine;     
 except       on E:Exception do         MessageDlg('BUG?', 'Possible
BUG encountered. Exception Message: ' + E.Message, mtWarning, [mbOk],
0);       end;       strSQlExecuted:=Query.SQL.Text;     
 ReadQuery.Open();     while not (query.EOF) do     begin     
 sCBXEntry := Query.FieldByName('IDNo').AsString + ' - ' +
Query.FieldByName('GivenName').AsString + ' ' +
Query.FieldByName('FamilyName').AsString;     
 cbxContactList.Items.Add(sCBXEntry);       ReadQuery.Next();   
 end;     intListCount:=cbxContactList.Items.Count;    if
(cbxContactList.Items.Count = 0) then     begin     
 cbxContactList.Enabled := false;       cbxContactList.Text:='[NO
ENTRIES]';     end     else      cbxContactList.ItemIndex:=0;  except 
  on E:Exception do    begin       MessageDlg('Unkown error', 'An
unexpected error occured within the program. The message was: ' +
E.Message + '. Execution terminated.', mtError, [mbClose], 0);     
 halt(1);    end;  end;end; 
From my understanding, this should work. But it's not working AFTER
the first. The first time, no problems. But if I go into another form
and then back to this one and re-call FormUpdate() then the EOF is set
to true BEFORE reading the first record.

What am I doing wrong?

----------------------------------------------------------------------
egsuh · 2021-05-13 01:11

1.  With your program, try inserting

      ReadQuery.First;

    just after Requery.Open; . Not sure for now whether this will
solve the problem.

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.

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.

----------------------------------------------------------------------
HopefulGuy · 2021-05-13 02:08

Quote from: egsuh on May 13, 2021, 03:11:02 am1.  With your program,
try inserting

      ReadQuery.First;

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

Quote from: egsuh on May 13, 2021, 03:11:02 am2.  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.

Quote from: egsuh on May 13, 2021, 03:11:02 amBefore 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TfrmList.btnNewEditClick(Sender: TObject);  var strIDNo :
string;begin  frmEditInfo:=TfrmEditInfo.Create(self);  if
(Sender=btnEdit) then  begin    strIDNo:=trim(copy(cbxListLadies.Text,
1,pos(' - ', cbxListLadies.Text)-1));    frmEditInfo.SetupForm('EDIT',
strIdNo);  end  else  begin    frmEditInfo.SetupForm('NEW', '0'); 
end;  frmEditInfo.ShowModal();  FormUpdate();end; 
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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i object
frmEditInfo: TfrmEditInfo  Left = 327  Height = 823  Top = 144  Width
= 1047  Caption = 'Woman Information'  ClientHeight = 823  ClientWidth
= 1047  Position = poMainFormCenter  LCLVersion = '2.0.12.0'  object
dbedtGiven: TDBEdit    Left = 95    Height = 23    Top = 91    Width =
176    DataField = 'GivenName'    DataSource = DBData    CharCase =
ecNormal    MaxLength = 0    TabOrder = 0  end  object lblGiven:
TLabel    Left = 23    Height = 15    Top = 95    Width = 65   
Caption = 'Given Name'    ParentColor = False  end  object lbFamily:
TLabel    Left = 340    Height = 15    Top = 95    Width = 70   
Caption = 'Family Name'    ParentColor = False  end  object
dbedtFamily: TDBEdit    Left = 412    Height = 23    Top = 91    Width
= 176    DataField = 'FamilyName'    DataSource = DBData    CharCase =
ecNormal    MaxLength = 0    TabOrder = 1  end  object lblAge: TLabel 
  Left = 600    Height = 15    Top = 95    Width = 21    Caption =
'Age'    ParentColor = False  end  object dbedtAge: TDBEdit    Left =
628    Height = 23    Top = 91    Width = 20    DataField = 'Age'   
DataSource = DBData    CharCase = ecNormal    EditMask = '##;0;_'   
MaxLength = 2    TabOrder = 2  end  object dbmemPostal: TDBMemo   
Left = 95    Height = 90    Top = 112    Width = 345    DataField =
'postal'    DataSource = DBData    TabOrder = 3  end  object
lblPostal: TLabel    Left = 11    Height = 15    Top = 123    Width =
77    Caption = 'Postal Address'    ParentColor = False  end  object
dbedtemail: TDBEdit    Left = 550    Height = 23    Top = 112    Width
= 176    DataField = 'FamilyName'    DataSource = DBData    CharCase =
ecNormal    MaxLength = 0    TabOrder = 4  end  object lblEmail:
TLabel    Left = 468    Height = 15    Top = 116    Width = 74   
Caption = 'Email Address'    ParentColor = False  end  object
lblPhone: TLabel    Left = 468    Height = 15    Top = 144    Width =
64    Caption = 'Telephone #'    ParentColor = False  end  object
dbedtPhone: TDBEdit    Left = 550    Height = 23    Top = 140    Width
= 176    DataField = 'FamilyName'    DataSource = DBData    CharCase =
ecNormal    MaxLength = 0    TabOrder = 5  end  object lblGender:
TLabel    Left = 468    Height = 15    Top = 172    Width = 38   
Caption = 'Gender'    ParentColor = False  end  object dbcbxGender:
TDBComboBox    Left = 552    Height = 23    Top = 164    Width = 100 
 DataField = 'gender'    DataSource = DBData    ItemHeight = 15   
Items.Strings = (      'Female'      'Male'      'Other'    )   
MaxLength = 0    TabOrder = 6  end  object dbmemNotes: TDBMemo    Left
= 96    Height = 459    Top = 204    Width = 843    DataSource =
DBData    TabOrder = 7  end  object lblNotes: TLabel    Left = 11   
Height = 15    Top = 220    Width = 31    Caption = 'Notes'   
ParentColor = False  end  object btnSave: TButton    Left = 155   
Height = 25    Top = 716    Width = 75    Caption = 'Save Data'   
TabOrder = 8  end  object btnAbort: TButton    Left = 764    Height =
25    Top = 716    Width = 75    Caption = 'Close'    OnClick =
btnAbortClick    TabOrder = 9  end  object DBData: TDataSource    Left
= 28    Top = 16  end  object ReadQuery: TSQLQuery    IndexName =
'DEFAULT_ORDER'    MaxIndexesCount = 4    FieldDefs = <          item 
      Name = 'IDNo'        DataType = ftAutoInc        Precision = -1 
    end          item        Name = 'GivenName'        DataType =
ftString        Precision = -1        Size = 20      end         
item        Name = 'FamilyName'        DataType = ftString       
Precision = -1        Size = 20      end          item        Name =
'email'        DataType = ftString        Precision = -1        Size =
90      end          item        Name = 'age'        DataType =
ftWord        Precision = -1      end          item        Name =
'phone'        DataType = ftString        Precision = -1        Size =
15      end          item        Name = 'postal'        DataType =
ftMemo        Precision = -1      end          item        Name =
'notes'        DataType = ftMemo        Precision = -1      end>   
Database = frmList.Connection    Transaction = frmList.Transaction   
SQL.Strings = (      'Select * from Contact where IDNo = 1'    )   
Params = <>    DataSource = DBData    Left = 104    Top = 8  end 
object WriteQuery: TSQLQuery    IndexName = 'DEFAULT_ORDER'   
MaxIndexesCount = 4    FieldDefs = <          item        Name =
'IDNo'        DataType = ftAutoInc        Precision = -1      end   
    item        Name = 'GivenName'        DataType = ftString       
Precision = -1        Size = 20      end          item        Name =
'FamilyName'        DataType = ftString        Precision = -1       
Size = 20      end          item        Name = 'email'        DataType
= ftString        Precision = -1        Size = 90      end         
item        Name = 'age'        DataType = ftWord        Precision =
-1      end          item        Name = 'phone'        DataType =
ftString        Precision = -1        Size = 15      end         
item        Name = 'postal'        DataType = ftMemo        Precision
= -1      end          item        Name = 'notes'        DataType =
ftMemo        Precision = -1      end>    Database =
frmList.Connection    Transaction = frmList.Transaction    Params =
<>    DataSource = DBData    Left = 196    Top = 12  endend
What am I doing wrong this time?

----------------------------------------------------------------------
rvk · 2021-05-13 03:12

Quote from: HopefulGuy on May 13, 2021, 04:08:56 amWhat 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 · 2021-05-13 03:45

Quote from: rvk on May 13, 2021, 05:12:24 amSo 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 · 2021-05-13 05:11

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i object
frmEditInfo: TfrmEditInfo  Left = 327  Height = 823  Top = 144  Width
= 1047  Caption = 'Person Information'  ClientHeight = 823 
ClientWidth = 1047  Position = poMainFormCenter  LCLVersion =
'2.0.12.0'  object dbedtGiven: TDBEdit    Left = 95    Height = 23   
Top = 87    Width = 176    DataField = 'GivenName'    DataSource =
DBData    CharCase = ecNormal    MaxLength = 0    TabOrder = 0   
OnChange = DataChanged  end  object lblGiven: TLabel    Left = 23   
Height = 15    Top = 91    Width = 65    Caption = 'Given Name'   
ParentColor = False  end  object lbFamily: TLabel    Left = 340   
Height = 15    Top = 91    Width = 70    Caption = 'Family Name'   
ParentColor = False  end  object dbedtFamily: TDBEdit    Left = 412  
Height = 23    Top = 87    Width = 176    DataField = 'FamilyName'   
DataSource = DBData    CharCase = ecNormal    MaxLength = 0   
TabOrder = 1  end  object lblAge: TLabel    Left = 600    Height = 15 
  Top = 91    Width = 21    Caption = 'Age'    ParentColor = False 
end  object dbedtAge: TDBEdit    Left = 628    Height = 23    Top =
87    Width = 20    DataField = 'Age'    DataSource = DBData   
CharCase = ecNormal    EditMask = '##;0;_'    MaxLength = 2   
TabOrder = 2  end  object dbmemPostal: TDBMemo    Left = 95    Height
= 90    Top = 112    Width = 345    DataField = 'postal'    DataSource
= DBData    TabOrder = 3  end  object lblPostal: TLabel    Left = 11 
 Height = 15    Top = 123    Width = 77    Caption = 'Postal Address' 
  ParentColor = False  end  object dbedtemail: TDBEdit    Left = 550 
 Height = 23    Top = 112    Width = 176    DataField = 'email'   
DataSource = DBData    CharCase = ecNormal    MaxLength = 0   
TabOrder = 4  end  object lblEmail: TLabel    Left = 468    Height =
15    Top = 116    Width = 74    Caption = 'Email Address'   
ParentColor = False  end  object lblPhone: TLabel    Left = 468   
Height = 15    Top = 140    Width = 64    Caption = 'Telephone #'   
ParentColor = False  end  object dbedtPhone: TDBEdit    Left = 550   
Height = 23    Top = 136    Width = 176    DataField = 'phone'   
DataSource = DBData    CharCase = ecNormal    MaxLength = 0   
TabOrder = 5  end  object lblGender: TLabel    Left = 468    Height =
15    Top = 164    Width = 38    Caption = 'Gender'    ParentColor =
False  end  object dbcbxGender: TDBComboBox    Left = 550    Height =
23    Top = 160    Width = 100    DataField = 'gender'    DataSource =
DBData    ItemHeight = 15    Items.Strings = (      'Female'     
'Male'      'Other'    )    MaxLength = 0    TabOrder = 6  end  object
dbmemNotes: TDBMemo    Left = 96    Height = 459    Top = 204    Width
= 843    DataField = 'notes'    DataSource = DBData    TabOrder = 7 
end  object lblNotes: TLabel    Left = 11    Height = 15    Top = 220 
  Width = 31    Caption = 'Notes'    ParentColor = False  end  object
btnSave: TButton    Left = 155    Height = 25    Top = 716    Width =
75    Caption = 'Save Data'    OnClick = btnSaveClick    TabOrder = 8 
end  object btnAbort: TButton    Left = 764    Height = 25    Top =
716    Width = 75    Caption = 'Close'    OnClick = btnAbortClick   
TabOrder = 9  end  object DBData: TDataSource    DataSet = ReadQuery 
 Left = 28    Top = 16  end  object ReadQuery: TSQLQuery    IndexName
= 'DEFAULT_ORDER'    MaxIndexesCount = 4    FieldDefs = <>    Database
= frmList.Connection    Transaction = frmList.Transaction   
SQL.Strings = (      'Select * from Contact where IDNo = 1'    )   
Params = <>    Left = 104    Top = 16  end  object WriteQuery:
TSQLQuery    IndexName = 'DEFAULT_ORDER'    MaxIndexesCount = 4   
FieldDefs = <          item        Name = 'IDNo'        DataType =
ftAutoInc        Precision = -1      end          item        Name =
'GivenName'        DataType = ftString        Precision = -1       
Size = 20      end          item        Name = 'FamilyName'       
DataType = ftString        Precision = -1        Size = 20      end  
     item        Name = 'email'        DataType = ftString       
Precision = -1        Size = 90      end          item        Name =
'age'        DataType = ftWord        Precision = -1      end        
item        Name = 'phone'        DataType = ftString        Precision
= -1        Size = 15      end          item        Name = 'postal'  
    DataType = ftMemo        Precision = -1      end          item   
   Name = 'notes'        DataType = ftMemo        Precision = -1     
end>    Database = frmList.Connection    Transaction =
frmList.Transaction    SQL.Strings = (      'select * from Contact
where IDNo = :IDNo'    )    Params = <          item        DataType =
ftInteger        Name = 'IDNo'        ParamType = ptInput      end>  
Left = 196    Top = 16  end  object DBWrite: TDataSource    DataSet =
WriteQuery    Left = 252    Top = 16  endend
And here's the pas:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i unit
untEditInfo; {$mode objfpc}{$H+} interface uses  Classes, SysUtils,
DB, SQLDB, Forms, Controls, Graphics, Dialogs, DBCtrls, 
StdCtrls; type   { TfrmEditInfo }   TfrmEditInfo = class(TForm)   
btnSave: TButton;    btnAbort: TButton;    DBWrite: TDataSource;   
dbcbxGender: TDBComboBox;    DBData: TDataSource;    dbedtAge:
TDBEdit;    dbedtemail: TDBEdit;    dbedtPhone: TDBEdit;   
dbedtGiven: TDBEdit;    dbedtFamily: TDBEdit;    dbmemNotes: TDBMemo; 
  dbmemPostal: TDBMemo;    lblEmail: TLabel;    lblAge: TLabel;   
lblPhone: TLabel;    lblGiven: TLabel;    lbFamily: TLabel;   
lblGender: TLabel;    lblPostal: TLabel;    lblNotes: TLabel;   
ReadQuery: TSQLQuery;    WriteQuery: TSQLQuery;    procedure
btnAbortClick(Sender: TObject);    procedure btnSaveClick(Sender:
TObject);    procedure DataChanged(Sender: TObject);    procedure
SetupForm(strMode, strID : string);  private   public   end; var 
frmEditInfo: TfrmEditInfo; implementation var  strFormMode : string; 
intPersonNo : integer;  boolChanged : Boolean;{ TfrmEditInfo
} procedure TfrmEditInfo.btnAbortClick(Sender: TObject);begin  if
(boolChanged) then     if (MessageDlg('Confirm Close', 'Really Close?
Any unsaved data will be lost!', mtWarning, [mbYes,mbNo], 0) = mrYes)
then    close();end; procedure TfrmEditInfo.btnSaveClick(Sender:
TObject);  procedure SetInfoField(strFld, strVal : string);  begin   
WriteQuery.FieldByName(strFld).AsString:=strVal;  end;   procedure
SetInfoField(strFld : String; intVal : integer);  begin   
WriteQuery.FieldByName(strFld).AsInteger:=intVal;  end;begin 
boolChanged := False;  WriteQuery.Close();  WriteQuery.SQL.Text :=
ReadQuery.SQL.Text;  WriteQuery.Open();  if (strFormMode = 'NEW')
then  begin    WriteQuery.Insert();    SetInfoField('IDNo',
IntPersonNo);  end  else    WriteQuery.Edit; 
SetInfoField('GivenName', dbedtGiven.text); 
SetInfoField('FamilyName', dbedtFamily.Text);  SetInfoField('phone',
dbedtPhone.Text);  SetInfoField('postal', dbmemPostal.Text); 
SetInfoField('gender', dbcbxGender.Text);  SetInfoField('email',
dbedtEmail.Text);  SetInfoField('age', StrToInt(dbedtAge.Text)); 
SetInfoField('notes', dbmemNotes.Text);  WriteQuery.Post(); 
WriteQuery.ApplyUpdates();  Close();end; procedure
TfrmEditInfo.DataChanged(Sender: TObject);begin   
 BoolChanged:=True;end; procedure TfrmEditInfo.SetupForm(strMode,
strID : string);begin  strFormMode:=strMode; 
intPersonNo:=StrToInt(strID);  if (strFormMode = 'EDIT') then  begin 
 ReadQuery.SQL.Text:='select * from Contact where IDNo = ' + strID;  
ReadQuery.Open();  end;end; {$R *.lfm} end. 
Thanks for all your help!

----------------------------------------------------------------------
rvk · 2021-05-13 06:12

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 

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.

----------------------------------------------------------------------
HopefulGuy · 2021-05-13 07:22

Quote from: rvk on May 13, 2021, 08:12:24 amIf 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.

Quote from: rvk on May 13, 2021, 08:12:24 amNext... 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.

Quote from: rvk on May 13, 2021, 08:12:24 amIf you change  that you'll
see you'll have hardly any code at all left 

We'll see.

Quote from: rvk on May 13, 2021, 08:12:24 amOne 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 · 2021-05-13 07:36

Quote from: HopefulGuy on May 13, 2021, 09:22:58 amQuote from: rvk on
May 13, 2021, 08:12:24 amIf 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.

Quote from: HopefulGuy on May 13, 2021, 09:22:58 amQuote from: rvk on
May 13, 2021, 08:12:24 amNext... 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.

Quote from: HopefulGuy on May 13, 2021, 09:22:58 amQuote from: rvk on
May 13, 2021, 08:12:24 amOne 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.

Quote from: HopefulGuy on May 13, 2021, 09:22:58 amOh, 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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;
Your saveclick will  be something like this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TfrmEditInfo.btnSaveClick(Sender: TObject);begin  ReadQuery.Post(); 
ReadQuery.ApplyUpdates();  TSQLTransaction(ReadQuery).Commit; // maybe
not needed but it doesn't hurt  Close();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.

----------------------------------------------------------------------
HopefulGuy · 2021-05-13 17:44

Quote from: rvk on May 13, 2021, 09:36:31 amQuote from: HopefulGuy on
May 13, 2021, 09:22:58 amOh, 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.
Quote from: rvk on May 13, 2021, 09:36:31 amEdit: 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 · 2021-05-13 17:57

Quote from: HopefulGuy on May 13, 2021, 07:44:12 pmActually, 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 · 2021-05-13 22:21

QuoteBTW 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.

QuoteBy 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.