Page 1 of 1
How to update fields from joined tables
Posted: Sun 19 Jul 2009 10:39
by jürgen
In my Query i get the resultset from two joined tables.
The rows are displayed in a DBGrid.
How can i update the fields of BOTH tables?
Posted: Mon 20 Jul 2009 14:41
by Dimon
To solve the problem you should generate update SQL statements manually using the TMyQuery.SQLUpdate property. To create a SQLUpdate statement at design-time, you can use the query statement editor.
You can find more detailed information about these properties in the SDAC Help.
Posted: Wed 22 Jul 2009 08:07
by dartas
Hello,
I found that in sdac this not work, but in SQL Management studio all ok
Insert statment in query statments editor:
Code: Select all
INSERT INTO [table1]
([value1], [value2], [value3])
VALUES (:[value1], :[value2], :[value3])
SET :[value0] = SCOPE_IDENTITY()
INSERT INTO [table2]
([value0], [value4], [value5], [value6])
VALUES (:[value0], :[value4], :[value5], :[value6])
problem what scope_identity doesn't return any value from db
D2009, SDAC 4.70.0.48
Posted: Wed 22 Jul 2009 14:11
by Dimon
To solve the problem you should use the following SQL:
Code: Select all
INSERT INTO [table1]
([value1], [value2], [value3])
VALUES (:[value1], :[value2], :[value3])
SET :[value0] = SCOPE_IDENTITY()
INSERT INTO [table2]
([value0], [value4], [value5], [value6])
VALUES (SCOPE_IDENTITY(), :[value4], :[value5], :[value6])
Also you should set ParamType of the value0 parameter to ptInputOutput in the TMSQuery.BeforeUpdateExecute event handler, like this:
Code: Select all
Params.ParamByName('value0').ParamType := ptInputOutput;
Posted: Thu 23 Jul 2009 10:46
by dartas
Thanks.