How to update fields from joined tables

[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?

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

[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

[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;

[dartas]

Thanks.