tcsh tcsh - 2 months ago 14
MySQL Question

How to edit 2 rows in 2 different (but identical structure) database tables - Delphi

I use 2

TDBGrid
controls in my application.


  • The 1st DBGrid shows data from a table named
    Orders
    .

  • The 2nd DBGrid shows data from a table named
    Archive
    .



Both tables are in the same database, and have identical structure (same number and names of columns, as well as same settings).

Here is the structure for both tables:

image

I use 2 actions in my application:


  • The 1st action is
    Add Order
    .

  • The 2nd action is
    Edit Order
    .



When I use the
Add Order
form, it successfully adds details I enter into a new row in both tables.

So this works.

Now, I want to select one of those rows I just added and edit its information.

I use the
Edit Order
action for that. It displays a similar form with the information to edit and save. It should edit the info in both tables. It does, but the IDs change and I end up editing something on the 1st DBGrid, and in the 2nd DBGrid it changes the information to something else (from another ID).

Here is my code for the
Edit
action:

procedure TForm2.actEditComandaExecute(Sender: TObject);
begin
if (dbmodule.SQLConnection1.Connected) and (dbmodule.comenziDataSet.IsEmpty = false) then
begin
editcustomerform.Edit1.Text := dbmodule.comenziDataSetstare.Value;
editcustomerform.Edit2.Text := dbmodule.comenziDataSetclient.Value;
editcustomerform.Edit3.Text := dbmodule.comenziDataSettelefon.Value;
editcustomerform.Edit4.Text := dbmodule.comenziDataSetemail.Value;
editcustomerform.Edit5.Text := dbmodule.comenziDataSetdetalii.Value;
editcustomerform.Edit6.Text := dbmodule.comenziDataSetpret.Value;
editcustomerform.Edit7.Text := dbmodule.comenziDataSetlivrare.Value;
editcustomerform.Edit8.Text := dbmodule.comenziDataSetuser.Value;
editcustomerform.Edit9.Text := dbmodule.comenziDataSetstatus.Value;
if editcustomerform.ShowModal = mrOk then
begin
dbmodule.SQLQuery1.SQL.Clear;
dbmodule.SQLQuery1.SQL.Add('UPDATE `tipotask`.`comenzi` SET `stare`=''' + editcustomerform.Edit1.Text + ''', `client`=''' + editcustomerform.Edit2.Text + ''', `telefon`=''' + editcustomerform.Edit3.Text + ''', `email`=''' + editcustomerform.Edit4.Text + ''', `detalii`=''' + editcustomerform.Edit5.Text + ''', `pret`=''' + editcustomerform.Edit6.Text + ''', `livrare`=''' + editcustomerform.Edit7.Text + ''', `user`=''' + editcustomerform.Edit8.Text + ''', `status`=''' + editcustomerform.Edit9.Text + ''' WHERE `id`=''' + IntToStr(dbmodule.comenziDataSetid.Value) + ''';');
dbmodule.SQLQuery1.ExecSQL(true);
dbmodule.SQLQuery3.SQL.Clear;
dbmodule.SQLQuery3.SQL.Add('UPDATE `tipotask`.`arhiva` SET `stare`=''' + editcustomerform.Edit1.Text + ''', `client`=''' + editcustomerform.Edit2.Text + ''', `telefon`=''' + editcustomerform.Edit3.Text + ''', `email`=''' + editcustomerform.Edit4.Text + ''', `detalii`=''' + editcustomerform.Edit5.Text + ''', `pret`=''' + editcustomerform.Edit6.Text + ''', `livrare`=''' + editcustomerform.Edit7.Text + ''', `user`=''' + editcustomerform.Edit8.Text + ''', `status`=''' + editcustomerform.Edit9.Text + ''' WHERE `id`=''' + IntToStr(dbmodule.arhivaDataSetid.Value) + ''';');
dbmodule.SQLQuery3.ExecSQL(true);

//we need to refresh the data
actRefreshData.Execute;
end;
end;


Here is a video of what's happening. Please notice how the ID columns change and mess things up.

YouTube video - recorded what's happening

The end goal is to add a new order using the
Add Order
form, which works well, this adds the order in the 2 tables. This works well.

Whenever I need to
Edit
an order, I just select it in the
Orders
DBGrid and edit it. Once I do that, it should also change the corresponding information in the
Archive
table - just the record I'm editing.

How can I fix this?

I've been at it for a couple of hours now, and I can't figure it out. I don't understand ID fields properly (auto increment, primary, unique, index, etc). I can't seem to wrap my head around it.

I'm fairly new, so please do try to provide a complete explanation.

Answer

Based on the video you showed, the only way this can be happening is if dbmodule.arhivaDataSetid.Value is 1 when dbmodule.comenziDataSetid.Value is 3 instead. And it is clearly visible in the video that before you edit the 3rd record in the Orders DBGrid, you left the 1st record in the Archive DBGrid selected instead of the 3rd record.

Assuming the id values in both tables should always match each other for any given record, you should be using dbmodule.comenziDataSetid.Value instead of dbmodule.arhivaDataSetid.Value in both SQL statements when editing the Orders DBGrid. Likewise, when editing the Archive table, you should be using dbmodule.arhivaDataSetid.Value in both SQL statements. That way, you use the correct ID in both statements.

Alternatively, simply don't use multiple SQL statements at all. Use DB triggers instead to automatically insert/update a record in one table when a record is inserted/updated in the other table. No need to duplicate the work in code when the DB engine can do it for you on the server side.

That being said, the id field of each table is an auto-increment field, which means the value for that table gets incremented automatically whenever a new record is inserted into that table. So, if there is ever a time that a record is successfully inserted in one table but fails to insert into the other table, you will easily get your IDs out of sync. So, at the very least, your 2 INSERT statements should be wrapped inside a DB transaction so that if either INSERT fails, you can cancel the whole transaction without changing either table (same with your UPDATE statements). Also, you should consider having a foreign key reference on the two id fields to link them together so that if anyone ever deletes a record in one table, the corresponding record in the other table will be deleted as well.

Personally, I wouldn't rely on using auto-increment fields to link data in two tables together. Auto-increment fields are fine for identifying records within individual tables, but when data needs to be linked across tables, it is better to use a more reliable and unique identifier, such as an order number, or even a UUID.

Also, be aware that your code is subject to SQL injection attacks. You really should be using parameterized queries instead of manually building SQL statements. Or, at least use QuotedStr() instead of manually putting quotes around user-entered text.