1 Topic by CDB (edited by CDB 2020-04-29 11:10:04)

  • CDB
  • Member
  • Offline
  • Registered: 2019-12-16
  • Posts: 370

Topic: Problem with SQLexecute WHERE statement

Being too clever for my own good.!  I need to save an entry form to two different tables in the database.

So, I have used the inbuilt SAVE mechanism for one table and then in the 'after_click' event (which I'm using as a sort of trigger SQL) I have the follwoing code.

procedure frmMain_Button3_OnAfterClick (Sender: TObject);
var dbid: integer;
begin
    dbid := frmMain.Button3.dbGeneralTableId;

    sqlexecute('INSERT INTO productSupplier (supplier_part_number) VALUES ("'+frmMain.edtSupplierCode.text+'") WHERE ID = '+IntToStr(dbid));
    sqlexecute('INSERT INTO productSupplier (description)VALUES ("'+frmMain.edtDeascription.text+'") WHERE ID = '+IntToStr(dbid)+')');
    //sqlexecute('INSERT INTO productSupplier (notes)VALUES ("'+frmMain.memPartNotes+'") WHERE ID = ("'+dbid+'")');
end;

Neither format of the SQL statement works as shown above - I get a 'NEAR WHERE' error message.

Ignore the image box picture, I got fed up searching for the correct picture so just selected the first one I came to on my PC.

Post's attachments

Attachment icon sql_where_error.PNG 48.21 kb, 134 downloads since 2020-04-29 

On a clear disk you can seek forever
  • CDB
  • Member
  • Offline
  • Registered: 2019-12-16
  • Posts: 370

Re: Problem with SQLexecute WHERE statement

I have just had a thought, maybe I don't have to use the 'where' clause at all, as both tables should be in sync as far as a new record is concerned. Though for belts and braces it is probably better to ensure they are using the same record ID.

On a clear disk you can seek forever

3 Reply by derek (edited by derek 2020-04-29 12:07:07)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,674

Re: Problem with SQLexecute WHERE statement

Hi,
I like to keep it simple where possible with minimal script, so I'd try it with 2 'save' buttons on the form.
The first save button saves to the main table and is configured to stay on the form after clicking.
The second save button is hidden and is configured to close the form after clicking.
The only script you need is an 'on after click' of the first save button to automatically click the second save button.
If your tables are unrelated, this is all you need to do.
If they are related, your script also needs to shuttle the key back to the form so it's included as part of the second save.
That all sounds way more complicated that it actually is - have a look at the attached and you'll see how it works.
Derek.

Post's attachments

Attachment icon save to different tables.zip 337.59 kb, 330 downloads since 2020-04-29 

  • Registered: 2015-11-13
  • Posts: 652

Re: Problem with SQLexecute WHERE statement

CDB,
For future reference the SQL INSERT does not have a WHERE clause. You can override the autoincrement id as follows:

sqlexecute('INSERT INTO productSupplier (id,supplier_part_number) VALUES ('+IntToStr(dbid) +',"'+frmMain.edtSupplierCode.text+'")');
  • CDB
  • Member
  • Offline
  • Registered: 2019-12-16
  • Posts: 370

Re: Problem with SQLexecute WHERE statement

Thank you both.

@Derek,  It never occurred to me to use a hidden save button.

@ehwagner, It just goes to show how much better everything works when using the correct 'grammar' for statement.

On a clear disk you can seek forever