Lazarus

Programming => Databases => Topic started by: kapibara on February 23, 2017, 01:32:01 am

Title: Saving DBGrid data
Post by: kapibara on February 23, 2017, 01:32:01 am

There is a DBGrid with two columns: GroupTypes and Groups. The user should fill in a name for each group and these names saved in the groups table. But the group names are not saved despite calling ApplyUpdates and Commit in the grids AfterPost event.

Minimal sample project attached. (sqlite)

The DBGrid is populated with a left join query, so all GroupTypes are shown despite no groups are present yet:

Code: Pascal  [Select][+][-]

1. SELECT * FROM grouptypes
2.   LEFT JOIN groups
3.     ON grouptypes.grouptype_id=
4.        groups.grouptype_id
5.  WHERE cat_id=:cat_id
6.  

The InsertSQL of the Grids Query:

Code: Pascal  [Select][+][-]

1. INSERT INTO
2.   groups(group_name, grouptype_id)
3.   VALUES(:group_name, :grouptype_id)
4.  

And the UpdateSQL:

Code: Pascal  [Select][+][-]

1. UPDATE groups
2.    SET group_name=:group_name,
3.        grouptype_id=:grouptype_id
4.  WHERE group_id=:old_group_id

Title: Re: Saving DBGrid data
Post by: rvk on February 23, 2017, 10:24:23 am

You have several problems.

To try the problem seem more clear, please do the following:
Right click your grid and choose the Edit Columns.
Now remove all the predefined columns.
Make you form much wider so all the auto-generated columns will fit.

Now run your program.
Your form will look like attached image.
You see all the grouptypes on the left and your groups on the right.

When you are changing a field on the right, you effectively TRY to change a record. That record is the first in the combined join query. But.... you see there that there isn't actually a group-record. It's just a join with NULL. But for the grid it seems like you want to UPDATE something.

You could also see this visually if you put a ShowMessage in the qryGridBeforeInsert(). You'll see that when you "change" the group_name, you won't see the showmessage popup because there is never an INSERT done. The query tries to do an UPDATE on a record that doesn't yet exist.

Now with all the fields visible you could trigger an INSERT by adding another row and filling out the 3 last fields. (like you see in the second image) Now when you restart your program you'll see the INSERT is done correctly.

So... it's just really difficult to catch and distinguish between UPDATE and INSERT in a multi-join query.
(It might be better to just add a separate form where you can do the insert and updates.)

Title: Re: Saving DBGrid data
Post by: kapibara on February 24, 2017, 01:29:48 am

Ah yes! You are right, there is no "group" record to update yet. Will try to work something out. Thx.