Lazarus

Programming => Databases => Topic started by: HopefulGuy on May 10, 2021, 06:51:58 am

Title: TSQLQuery.ExecSQL not making changes
Post by: HopefulGuy on May 10, 2021, 06:51:58 am
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][+][-]
  1. Procedure TfrmData.btnSaveClick(Sender: TObject);
  2.   procedure SetField(FieldName: String; Data : String);
  3.   begin
  4.     Query.FieldByName(FieldName).AsString:=Data;
  5.   end;
  6.   procedure SetField(FieldName: String; Data: Integer);
  7.   begin
  8.     Query.FieldByName(FieldName).AsInteger:=Data;
  9.   end;
  10. begin
  11.   Query.Edit;
  12.   if (FrmMode = 'NEW') then
  13.     Query.SQL.Text:='inert into Table1 values (0, :Gender, :GivenName, :FamilyName, :Age,  :Email,  :Phone, :Postal)'
  14.   else
  15.   begin
  16.     Query.SQL.Text:='update Table1 set Gender=:Gender GivenName=:GivenName, FamilyName:=FamilyName, Age=:Age, , Email=:Email, Phone=:Phone, Postal=:Postal where IDNo=:IDNo';
  17.     SetField('IDNo', IDNo);
  18.   end;
  19.   setField('Gender', cbxGender.Text);
  20.   setField('GivenName', edtGiven.Text);
  21.   setField('FamilyName', edtFamily.Text);
  22.   setField('Age', StrToInt(trim(medtAge.text)));
  23.   setField('Email', edtEmail.Text);
  24.   SetField('Phone', edtPhone.text);
  25.   SetField('Postal', memPostal.Lines.Text);
  26.   Query.ExecSQL();
  27.   Query.Close();
  28. end;
  29.  
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?
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: Gustavo 'Gus' Carreno on May 10, 2021, 07:21:36 am
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
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: HopefulGuy on May 10, 2021, 07:26:45 am
Hopefully that fixed things for you.   :)
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: Gustavo 'Gus' Carreno on May 10, 2021, 07:33:11 am
Hey HopefulGuy,

Hopefully that fixed things for you.   :)

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

Cheers,
Gus
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: Gustavo 'Gus' Carreno on May 10, 2021, 07:38:55 am
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
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: rvk on May 10, 2021, 07:41:25 am
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).

Title: Re: TSQLQuery.ExecSQL not making changes
Post by: HopefulGuy on May 10, 2021, 07:44:09 am
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.
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: rvk on May 10, 2021, 07:51:54 am
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
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: HopefulGuy on May 10, 2021, 07:54:18 am
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.

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

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

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.
Quote
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).
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][+][-]
  1. begin
  2.   if (FrmMode = 'NEW') then
  3.   begin
  4.     Query.SQL.Text:='insert into Table1 values (0, :Gender, :GivenName, :FamilyName, :Age,  :Email,  :Phone, :Postal)';
  5.     AsianLadiesQuery.Edit;
  6.   end
  7.   else
  8.   begin
  9.     Query.SQL.Text:=update Table1 set Gender=:Gender GivenName=:GivenName, FamilyName:=FamilyName, Age=:Age, Email=:Email, Phone=:Phone, Postal=:Postal where IDNo=:IDNo'
  10.    AsianLadiesQuery.Edit;
  11.    SetField('IDNo', LadyIDNo);
  12.  end;
  13.  setField('GivenName', edtGiven.Text);
  14.  
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: HopefulGuy on May 10, 2021, 07:58:19 am
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
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][+][-]
  1. procedure TfrmData.btnSaveClick(Sender: TObject);
  2.   procedure SetField(FieldName: String; Data : String);
  3.   begin
  4.     Query.Params.ParamByName(FieldName).AsString:=Data;
  5.   end;
  6.   procedure SetField(FieldName: String; Data: Integer);
  7.   begin
  8.     Query.Params.ParamByName(FieldName).AsInteger:=Data;
  9.   end;
  10. begin
  11.   if (FrmMode = 'NEW') then
  12.   begin
  13.     Query.SQL.Text:='insert into Table1 values (0, :GivenName, :FamilyName, :Age, :Country, :Email,  :Phone, :Postal)';
  14. //    Query.Edit;
  15.   end
  16.   else
  17.   begin
  18.     Query.SQL.Text:='update Table1 set GivenName=:GivenName, FamilyName:=FamilyName, Age=:Age, Country=:Country, Email=:Email, Phone=:Phone, Postal=:Postal, where IDNo=:IDNo';
  19. //    Query.Edit;
  20.     SetField('IDNo', IDNo);
  21.   end;
  22.   setField('GivenName', edtGiven.Text);
  23.   setField('FamilyName', edtFamily.Text);
  24.   setField('Age', StrToInt(trim(medtAge.text)));
  25.   SetField('Country', cbxCountry.Text);
  26.   setField('Email', edtEmail.Text);
  27.   SetField('Phone', edtPhone.text);
  28.   SetField('Postal', memPostal.Lines.Text);
  29.   Query.ExecSQL();
  30.   Query.Close();
  31.   Close();
  32. end;
  33.  
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: rvk on May 10, 2021, 08:00:46 am
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.
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: HopefulGuy on May 10, 2021, 08:20:20 am
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.
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: rvk on May 10, 2021, 09:00:14 am
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.


Title: Re: TSQLQuery.ExecSQL not making changes
Post by: Zvoni on May 10, 2021, 11:12:37 am
Not knowing, what connection-parameters are set, i'm missing the Transaction.Commit after an UPDATE/INSERT
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: egsuh on May 10, 2021, 11:15:24 am
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][+][-]
  1. 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.
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: HopefulGuy on May 10, 2021, 07:15:27 pm
Not 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.
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: Zvoni on May 10, 2021, 07:53:16 pm
IIRC from another thread, a Transaction is mandatory
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: HopefulGuy on May 10, 2021, 08:13:39 pm
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?
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: af0815 on May 10, 2021, 09:15:22 pm
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.
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: HopefulGuy on May 10, 2021, 11:08:05 pm
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.

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.
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: egsuh on May 11, 2021, 05:34:19 am
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][+][-]
  1. Query1.Close;
  2. Query1.SQL.Text := 'insert into table1 values (:v1, :v2, ...);';
  3. if not Transaction.Active then Transaction.StartTransaction;   // Transaction := Active does the same thing.
  4. Query1.Prepare;
  5. Query1.ParamByName('v1').AsString:= string1;
  6. Query1.ParamByName('v2').AsString:= string2;
  7. // ...................
  8. Query1.ExecSQL;  
  9.  
  10. Transaction.Commit; // permanently store changes since the start of transaction. or transaction.rollback to discard the changes
  11. Query1.Close; // this is not necessary, but anyway conceptually.
  12.  

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.

Title: Re: TSQLQuery.ExecSQL not making changes
Post by: af0815 on May 11, 2021, 09:31:41 am
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.
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: Zvoni on May 11, 2021, 09:37:25 am
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?
And your Transaction is correctly linked to the Connection and Query?
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: egsuh on May 11, 2021, 11:32:43 am
Quote
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.

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.
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: af0815 on May 11, 2021, 01:14:10 pm
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.

Title: Re: TSQLQuery.ExecSQL not making changes
Post by: Zvoni on May 11, 2021, 03:57:58 pm
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.

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.msg397188.html#msg397188
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: af0815 on May 11, 2021, 05:01:53 pm
Ok, my Lazarus with mssqldb driver did not know about the Kaboom error. So save early, save often :-)
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: HopefulGuy on May 11, 2021, 06:11:48 pm
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?
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
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: af0815 on May 11, 2021, 07:09:53 pm
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).
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: rvk on May 11, 2021, 07:16:10 pm
I 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)
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: HopefulGuy on May 11, 2021, 11:15:41 pm
Please post the code you have now.
(Your previous code mixed different methods and was incorrect)

Code currently looks like this:
Code: Pascal  [Select][+][-]
  1. procedure TfrmData.btnSaveClick(Sender: TObject);
  2.   procedure SetField(FieldName: String; Data : String);
  3.   begin
  4.     Query.FieldByName(FieldName).AsString:=Data;
  5.   end;
  6.   procedure SetField(FieldName: String; Data: Integer);
  7.   begin
  8.     Query.FieldByName(FieldName).AsInteger:=Data;
  9.   end;
  10. begin
  11. (*  SQLTransaction1.StartTransaction();
  12.   Query.Edit;
  13.   if (FrmMode = 'NEW') then
  14.    Query.SQL.Text:='insert into Table1 values (0, :Gender, :GivenName, :FamilyName, :Age, :ImgCount, :Email, :Phone, :Postal)'
  15.   else
  16.     Query.SQL.Text:='update Table1 set ChatLocation=:Gender, :GivenName, :FamilyName, :Age, :ImgCount, :Email, :Phone, :Postal where IDNo=:IDNo'; *)
  17.   setField('Gender', cbxContactLocation.Text);
  18.   setField('GivenName', edtGiven.Text);
  19.   setField('FamilyName', edtFamily.Text);
  20.   setField('Age', StrToInt(trim(medtAge.text)));
  21.   setField('Email', edtEmail.Text);
  22.   SetField('Phone', edtPhone.text);
  23.   SetField('Postal', memPostal.Lines.Text);
  24. //  Query.ExecSQL();
  25.   SQLTransaction1.Commit();
  26.   Close();
  27. end;
  28.  
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: dseligo on May 11, 2021, 11:41:48 pm
Try to uncomment 'insert', 'update' and 'ExecSQL' lines.  :)

Code: Pascal  [Select][+][-]
  1. procedure TfrmData.btnSaveClick(Sender: TObject);
  2.       procedure SetField(FieldName: String; Data : String);
  3.       begin
  4.         Query.ParamByName(FieldName).AsString:=Data;
  5.       end;
  6.       procedure SetField(FieldName: String; Data: Integer);
  7.       begin
  8.         Query.ParamByName(FieldName).AsInteger:=Data;
  9.       end;
  10.     begin
  11.       SQLTransaction1.StartTransaction();
  12.     //  Query.Edit; <-- no edit
  13.   Query.Close;
  14.       if (FrmMode = 'NEW') then
  15.        Query.SQL.Text:='insert into Table1 values (0, :Gender, :GivenName, :FamilyName, :Age, :ImgCount, :Email, :Phone, :Postal)'
  16.       else
  17.         Query.SQL.Text:='update Table1 set ChatLocation=:Gender, :GivenName, :FamilyName, :Age, :ImgCount, :Email, :Phone, :Postal where IDNo=:IDNo';
  18.       setField('Gender', cbxContactLocation.Text);
  19.       setField('GivenName', edtGiven.Text);
  20.       setField('FamilyName', edtFamily.Text);
  21.       setField('Age', StrToInt(trim(medtAge.text)));
  22.       setField('Email', edtEmail.Text);
  23.       SetField('Phone', edtPhone.text);
  24.       SetField('Postal', memPostal.Lines.Text);
  25.       if (FrmMode <> 'NEW') then
  26.         SetField('IDNo', ExistingID);
  27.       Query.ExecSQL();
  28.       SQLTransaction1.Commit();
  29.       Close();
  30.     end;
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: rvk on May 12, 2021, 12:34:03 am
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.

Title: Re: TSQLQuery.ExecSQL not making changes
Post by: HopefulGuy on May 12, 2021, 01:40:57 am
Try 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][+][-]
  1. procedure TfrmData.btnSaveClick(Sender: TObject);
  2.   procedure SetField(FieldName: String; Data : String);
  3.   begin
  4.     Query.FieldByName(FieldName).AsString:=Data;
  5.   end;
  6.   procedure SetField(FieldName: String; Data: Integer);
  7.   begin
  8.     Query.FieldByName(FieldName).AsInteger:=Data;
  9.   end;
  10. var testint : integer;
  11.     teststr : string;
  12. begin
  13.   SQLTransaction1.StartTransaction();
  14.   Query.Edit;
  15.   if (FrmMode = 'NEW') then
  16.    Query.SQL.Text:='insert into Table1 values (0, :Gender, :GivenName, :FamilyName, :Age, :ImgCount, :Email, :Phone, :Postal)'
  17.   else
  18.     Query.SQL.Text:='update Table1 set ChatLocation=:Gender, :GivenName, :FamilyName, :Age, :ImgCount, :Email, :Phone, :Postal where IDNo=:IDNo';
  19.   setField('Gender', cbxContactLocation.Text);
  20.   setField('GivenName', edtGiven.Text);
  21.   setField('FamilyName', edtFamily.Text);
  22.   teststr := trim(medtAge.text);
  23.   testint := StrToInt(trim(medtAge.text));
  24.   setField('Age', StrToInt(trim(medtAge.text)));
  25.   setField('Email', edtEmail.Text);
  26.   SetField('Phone', edtPhone.text);
  27.   SetField('Postal', memPostal.Lines.Text);
  28.   Query.ExecSQL();
  29.   SQLTransaction1.Commit();
  30.   Close();
  31. end;
  32.  
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: dseligo on May 12, 2021, 02:43:01 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.
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: dseligo on May 12, 2021, 02:48:14 am
Try 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).
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: egsuh on May 12, 2021, 03:44:26 am
Based on your explanations, I think what you need is following codes. Set transaction to auto-commitretaining.

Code: Pascal  [Select][+][-]
  1. procedure TfrmData.btnSaveClick(Sender: TObject);
  2.   procedure SetField(FieldName: String; Data : String);
  3.   begin
  4.     Query.FieldByName(FieldName).AsString:=Data;                // Not query.parambyname
  5.   end;
  6.   procedure SetField(FieldName: String; Data: Integer);
  7.   begin
  8.     Query.FieldByName(FieldName).AsInteger:=Data;
  9.   end;
  10. begin
  11.     //  SQLTransaction1.StartTransaction();
  12.    //  Query.Edit;
  13.   if (FrmMode = 'NEW') then Query.insert   // you need this
  14.      //  Query.SQL.Text:='insert into Table1 values (0, :Gender, :GivenName, :FamilyName, :Age, :ImgCount, :Email, :Phone, :Postal)'
  15.   else Query.Edit;     // and this
  16.    //  Query.SQL.Text:='update Table1 set ChatLocation=:Gender, :GivenName, :FamilyName, :Age, :ImgCount, :Email, :Phone, :Postal where IDNo=:IDNo'; *)
  17.  
  18.   setField('Gender', cbxContactLocation.Text);
  19.   setField('GivenName', edtGiven.Text);
  20.   setField('FamilyName', edtFamily.Text);
  21.   setField('Age', StrToInt(trim(medtAge.text)));
  22.   setField('Email', edtEmail.Text);
  23.   SetField('Phone', edtPhone.text);
  24.   SetField('Postal', memPostal.Lines.Text);
  25.  
  26.    Query.Post;
  27.    Query.ApplyUpdates;
  28.  
  29.    //  Query.ExecSQL();
  30.   // SQLTransaction1.Commit();   // set this to auto-commitretaining
  31.    //  Query.Close();    
  32. end;

Or, you should use  insert/update/delete + parambyname not fieldbyname +  ExecSQL , as rvk pointed out.
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: HopefulGuy on May 12, 2021, 03:52:14 am
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)

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][+][-]
  1. procedure TfrmLadyData.btnSaveClick(Sender: TObject);
  2.     procedure SetField(FieldName: String; Data : String);
  3.     begin
  4.       if (trim(data)+' ' = ' ') then
  5.          data := #0;
  6.       Query.FieldByName(FieldName).AsString:=Data;
  7.     end;
  8.     procedure SetField(FieldName: String; Data: Integer);
  9.     begin
  10.       Query.FieldByName(FieldName).AsInteger:=Data;
  11.     end;
  12.   begin
  13.     if (FrmMode = 'NEW') then
  14.     begin
  15.       Query.Insert();
  16.       SetField('IDNo', 0); // Auto-Increment field, so 0 = next available number
  17.       btnSave.Caption:='Add Record';
  18.     end
  19.     else
  20.     begin
  21.       Query.Close();
  22.       Query.SQL.Text := 'select * from Table1 where IDNo = ' + IntToStr(IDNO);
  23.       Query.open();
  24.       Query.Edit();
  25.       btnSave.Caption:='Update Record';
  26.     end;
  27.   setField('Gender', cbxContactLocation.Text);
  28.   setField('GivenName', edtGiven.Text);
  29.   setField('FamilyName', edtFamily.Text);
  30.   setField('Age', StrToInt(trim(medtAge.text)));
  31.   setField('Email', edtEmail.Text);
  32.   SetField('Phone', edtPhone.text);
  33.   SetField('Postal', memPostal.Lines.Text);
  34.   Query.ExecSQL();
  35.   SQLTransaction1.Commit();
  36.   Close();
  37. end;
  38.  
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: egsuh on May 12, 2021, 05:07:21 am
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.
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: HopefulGuy on May 12, 2021, 07:36:22 am
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.

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?
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: egsuh on May 12, 2021, 07:54:00 am
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.
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: rvk on May 12, 2021, 02:15:07 pm
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:
About this code:
Code: Pascal  [Select][+][-]
  1. Query.ExecSQL();
  2. SQLTransaction1.Commit();
  3. 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.
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: rvk on May 12, 2021, 08:16:28 pm
You can almost program an entire address-editing program with zero lines of code if you use TDBEdits and a TDBNavigator  8)
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: HopefulGuy on May 13, 2021, 02:41:33 am
You can almost program an entire address-editing program with zero lines of code if you use TDBEdits and a TDBNavigator  8)

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][+][-]
  1.  CREATE TABLE `Contact` (
  2.   `IDNo` int(11) NOT NULL AUTO_INCREMENT,
  3.   `GivenName` varchar(20) NOT NULL,
  4.   `FamilyName` varchar(20) NOT NULL,
  5.   `email` varchar(90) NOT NULL,
  6.   `age` tinyint(3) unsigned NOT NULL,
  7.   `phone` varchar(15) NOT NULL,
  8.   `postal` text NOT NULL,
  9.   `notes` text NOT NULL,
  10.   PRIMARY KEY (`IDNo`),
  11.   UNIQUE KEY `Names` (`GivenName`,`FamilyName`)
  12.  

Here's the one record:
Code: MySQL  [Select][+][-]
  1. +------+-----------+------------+---------------+------+----------------+--------+-------+--------+
  2. | IDNo | GivenName | FamilyName | email         | age  | phone          | postal | notes | gender |
  3. +------+-----------+------------+---------------+------+----------------+--------+-------+--------+
  4. |    1 | Jane      | Doe        | spam@spam.com |   19 | +0000000000000 |        |       | Female |
  5. +------+-----------+------------+---------------+------+----------------+--------+-------+--------+
  6.  

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][+][-]
  1. procedure TfrmList.FormUpdate;
  2. var sCBXEntry : string;
  3.     intListCount : integer;
  4.     strSQLExecuted : string;
  5. begin
  6.      cbxContactList.items.Clear;
  7.      cbxContactList.ItemIndex:=-1;
  8.   try
  9.     if not (connection.Connected) then
  10.        connection.Connected:=true; // hope thatworks
  11.        try
  12.          ReadQuery.close(); // in order to force the Query to re-request info from the database engine;
  13.        except
  14.        on E:Exception do
  15.          MessageDlg('BUG?', 'Possible BUG encountered. Exception Message: ' + E.Message, mtWarning, [mbOk], 0);
  16.        end;
  17.        strSQlExecuted:=Query.SQL.Text;
  18.        ReadQuery.Open();
  19.      while not (query.EOF) do
  20.      begin
  21.        sCBXEntry := Query.FieldByName('IDNo').AsString + ' - ' + Query.FieldByName('GivenName').AsString + ' ' + Query.FieldByName('FamilyName').AsString;
  22.        cbxContactList.Items.Add(sCBXEntry);
  23.        ReadQuery.Next();
  24.      end;
  25.      intListCount:=cbxContactList.Items.Count;
  26.     if (cbxContactList.Items.Count = 0) then
  27.      begin
  28.        cbxContactList.Enabled := false;
  29.        cbxContactList.Text:='[NO ENTRIES]';
  30.      end
  31.      else
  32.       cbxContactList.ItemIndex:=0;
  33.   except
  34.     on E:Exception do
  35.     begin
  36.        MessageDlg('Unkown error', 'An unexpected error occured within the program. The message was: ' + E.Message + '. Execution terminated.', mtError, [mbClose], 0);
  37.        halt(1);
  38.     end;
  39.   end;
  40. end;
  41.  

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?
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: egsuh on May 13, 2021, 03:11:02 am
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.
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: HopefulGuy 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?
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: rvk 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.
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: HopefulGuy 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. :)
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: HopefulGuy 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!
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: rvk 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.
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: HopefulGuy 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.
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: rvk 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.
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: HopefulGuy 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.
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: rvk 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.
Title: Re: TSQLQuery.ExecSQL not making changes
Post by: egsuh 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