Cannot insert or update columns from multiple tables (post #2) Need help please

Here is a really strange thing when I am using SQLOLEDB (Microsoft OLE DB
Provider for SQL Server) to update records from a dataset on a multi-tables
view containing an "instead of trigger". I am using a server-side cursor
(and I cannot use a client-side cursor in my case).

But if I change the SQL driver to use an ODBD DataSource (Microsoft OLE DB
Provider for ODBC Driver), is it working.

What is the problem with SQLOLEDB and I can I solve this issue?
I am using Delphi 7.0 with SQL 2000 (8.00.760 SP3) with an updated MDAC
(2.81.1117.0)

Example and source code appear at the end of this message.

Here is the answer of Microsoft support service :
[...]
Based on previous cases, if we can't find any warning or exception message
in the SQL Server trace file, it represents the issue is caused by client
application, not server side issue. Also, since the issue can be fixed after
changing to ODBC Provider, it seems the root cause is on the Dephi app side.
[...]

Thank you for helphing

Christian Dubois, Logilys inc.


Here is the code I use :
-----------------------------------------------------------------------------------------------------------------------------------------
procedure TForm1.Button1Click(Sender: TObject);
var
myDataSet: TADODataSet;
begin
myDataSet := TADODataSet.Create(Nil);
myDataSet.ConnectionString := 'Provider=SQLOLEDB.1;Password="";Persist
Security Info=True;User ID=sa;Initial Catalog=model;Data Source=(local)';
myDataSet.CursorLocation := clUseServer;
myDataSet.CursorType := ctKeyset;
myDataSet.CommandText := 'Select * From View_CliTest';
myDataSet.Active := True;

// I try to Edit the 1st record and I change a value from both CliTest and
CliCustTest
myDataSet.Edit;
myDataSet.FieldByName('CliNom').Value :=
myDataSet.FieldByName('CliNom').Value + 'x'; // CliNom is from table
CliTest
myDataSet.FieldByName('CliCust01').Value := 'Value 02'; // CliCust01 is
from Table CliCustTest

// I got the error Message on Post
// Cannot insert or update columns from multiple tables.
myDataSet.Post;
myDataSet.Active := False;
myDataSet.Free;
end;
-----------------------------------------------------------------------------------------------------------------------------------------

If I change the connectionstring to use an ODBC DataSource, is it working :
-----------------------------------------------------------------------------------------------------------------------------------------
myDataSet.ConnectionString := 'Provider=MSDASQL.1;Persist Security
Info=True;User ID=sa;Extended
Properties="DSN=model;UID=sa;PWD=;DATABASE=model"';

-----------------------------------------------------------------------------------------------------------------------------------------


If you want to reproduce the issue, here is the script to create tables and
view.
-----------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE [dbo].[CliTest] (
[CliNo] [int] NOT NULL ,
[CliNom] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[CliCustTest] (
[CLiNo] [int] NOT NULL ,
[CLiCust01] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[CLiCust26] [float] NULL ,
[CLiCust31] [smalldatetime] NULL ,
[CLiCust40] [tinyint] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CliTest] WITH NOCHECK ADD
CONSTRAINT [PK_CliTest] PRIMARY KEY CLUSTERED
(
[CliNo]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[CliCustTest] WITH NOCHECK ADD
CONSTRAINT [PK_CliCustTest] PRIMARY KEY CLUSTERED
(
[CLiNo]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE VIEW dbo.View_CliTest
WITH VIEW_METADATA
AS
SELECT dbo.CliTest.*, dbo.CliCustTest.CliCust01, dbo.CliCustTest.CliCust26,
dbo.CliCustTest.CliCust31, dbo.CliCustTest.CliCust40
FROM dbo.CliTest LEFT OUTER JOIN
dbo.CliCustTest WITH(NoLock) ON dbo.CliTest.CliNo =
dbo.CLiCustTest.CliNo
GO

CREATE TRIGGER T_Delete_CliTest ON [dbo].[View_CliTest]
INSTEAD OF DELETE
AS
BEGIN
Delete AliasCli From CliTest AliasCli INNER JOIN Inserted I ON
AliasCli.CliNo = I.CliNo
Delete AliasCliCust From CliCustTest AliasCliCust INNER JOIN Inserted N ON
AliasCliCust.CliNo = N.CliNo
END
GO

CREATE TRIGGER T_Insert_CliTest ON [dbo].[View_CliTest]
INSTEAD OF INSERT
AS
BEGIN

/* On fait un Insert dans la Table Cli en utilisant les valeurs dans
Inserted */
Insert CliTest
Select
CliNo, CliNom
From Inserted

Insert CliCustTest
Select
CliNo, CliCust01, CliCust26, CliCust31, CliCust40
From Inserted
END
GO

CREATE TRIGGER T_UpDate_CliTest ON [dbo].[View_CliTest]
INSTEAD OF UPDATE
AS
BEGIN

UpDate AliasCli Set
CliNom = I.CliNom
From CliTest AliasCli INNER JOIN Inserted I ON AliasCli.CliNo = I.CliNo

UpDate AliasCliCust Set
CliCust01 = N.CliCust01,
CliCust26 = N.CliCust26,
CliCust31 = N.CliCust31,
CliCust40 = N.CliCust40
From CliCustTest AliasCliCust INNER JOIN Inserted N ON AliasCliCust.CliNo
= N.CliNo
END
GO

Insert View_CliTest (CliNo, CliNom) Values (1, 'Client 1')
Insert View_CliTest (CliNo, CliNom) Values (2, 'Client 2')
Insert View_CliTest (CliNo, CliNom) Values (3, 'Client 3')
Insert View_CliTest (CliNo, CliNom) Values (4, 'Client 4')
Insert View_CliTest (CliNo, CliNom) Values (5, 'Client 5')
-----------------------------------------------------------------------------------------------------------------------------------------




.