How to update fields from joined tables

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
jürgen
Posts: 3
Joined: Fri 19 Jun 2009 11:03

How to update fields from joined tables

Post by jürgen » Sun 19 Jul 2009 10:39

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
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Mon 20 Jul 2009 14:41

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
Posts: 18
Joined: Mon 12 Jan 2009 14:40

Post by dartas » Wed 22 Jul 2009 08:07

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
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Wed 22 Jul 2009 14:11

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
Posts: 18
Joined: Mon 12 Jan 2009 14:40

Post by dartas » Thu 23 Jul 2009 10:46

Thanks.

Post Reply