BorlandTalk.com

Matthew Noordermeer · Guest

I am trying to use 2 x TBetterADODataSet to define a master-detail
relationship (D7, SQLServer 2000). However, I get the above error whenever
I post a new record. In the detail TBetterADODataSet I have the following
settings -

CursorLocation clUseClient
CursorType ctStatic
Update_Criteria adCriteriaKey

ResyncAutoIncrement is set to true, and the TAutoIncField.AutoGenerateValue
[primary key is an autoincrement IDENTITY field] is set to arAutoInc.

I can get it to post OK if I use a Resync_Command but then it doesn't allow
deletion. I have searched www.tamaracka.com and tried most suggestions. Any
ideas or suggestions would be gratefully received [wasted 2 days on this!]

Regards

Mike

Figaro · Guest

Hi Matthew!
Last time I've received this message it was based on incorrect
Resync_Command property.
CommandText - SELECT ID_Unit, SName, Name FROM Unit ORDER BY SName
Resync_Command - SELECT ID_Unit, SName, Name FROM Unit WHERE ID_Unit = ?
ID_Unit - Integer, IDENTITY , Primary key

Matthew Noordermeer · Guest

I forgot to mention I am using MDAC version 2.80 and TBetterADODataSet
version 4.04.

Regards

Mike

Matthew Noordermeer · Guest

Hi Figaro

Thanks ... this is probably my problem too ... I must admit I don't really
know how it works.

In your example, my resync_command property would have been

SELECT ID_Unit, SName, Name FROM Unit WHERE ID_Unit = :ID_Unit

Why the question mark in your example? Is it a SQLServer syntax?

Regards

Mike

Figaro · Guest

Matthew, question mark is ADO parameter sign!
Here is an example of Master/Detail. I take this from my application. It
works without any problem. Look, please, at DELPHI parameter in Detail SQL
CommandText and ADO parameters in Resync_Command's

MASTER
CommandText
SELECT ID_Store, StoreNum, Name FROM Store ORDER BY StoreNum
Resync_Command
SELECT ID_Store, StoreNum, Name FROM Store WHERE ID_Store = ?

DETAIL
CommandText
SELECT ID_Cell, ID_Store, CellNum FROM Cells WHERE ID_Store = :ID_Store
ORDER BY CellNum
Resync_Command
SELECT ID_Cell, ID_Store, CellNum FROM Cells WHERE ID_Cell = ?

Matthew Noordermeer · Guest

Hi Figaro

Thanks. I've tried the same thing with mine but it still doesn't work. In
your DETAIL, is ID_Cell your autoincrementing IDENTITY field?

What are your settings for CursorLocation, CursorType, Update_Criteria, and
ResyncAutoIncrement? Thanks for your help.

Regards

Mike

Figaro · Guest

Send me your master and detail queries and . I'll try to look closely on
your problem.

Matthew Noordermeer · Guest

Hi Figaro

Many thanks for taking the time to help. Below is the information you
requested. DivisionNo [MASTER primary key] is a uniqueidentifier field and a
value is assigned on the OnNewRecord event by a call to CreateGuid. This
seems to work OK. It's when I go to post a new record in the DETAIL table
there is a problem - however if I close the application and run it again the
record was inserted. DivisionBeltNo [DETAIL primary key] is an
autoincrementing integer.

Regards

Mike

MASTER
-----------

Query

SELECT DivisionNo, DivisionDesc, Gender,LowWeight,HighWeight,LowAge,HighAge
FROM Divisions
ORDER BY LowAge,HighAge,LowWeight,Gender

Resync_Command

SELECT DivisionNo, DivisionDesc, Gender, LowWeight,HighWeight,LowAge,HighAge
FROM Divisions
WHERE DivisionNo = ?

DETAIL
----------

Query

SELECT DivisionBeltNo, DivisionNo, BeltNo
FROM DivisionBelts
WHERE DivisionNo= :DivisionNo

Resync_Command

SELECT DivisionBeltNo, DivisionNo, BeltNo
FROM DivisionBelts
WHERE DivisionBeltNo = ?

Figaro · Guest

It's my way to work with TBetterADODataSet. Look at property setting.

procedure TdmWWMain.PrepareBADODataSet(AnBADODataSet: TBetterADODataSet;
ParametersArray, ValuesArray: Variant; ATable: string;
IndexFieldNames: string = '';
AMasterDataSource: TDataSource = nil);
var
i: integer;
begin

AnBADODataSet.Close;

for i:=VarArrayLowBound(ParametersArray, 1) to
VarArrayHighBound(ParametersArray, 1) do begin
AnBADODataSet.Parameters.ParamByName(ParametersArray[i]).Value :=
ValuesArray[i];
end;
AnBADODataSet.MasterFields := '';
AnBADODataSet.DataSource := AMasterDataSource;

AnBADODataSet.Update_Resync := [ResyncAutoIncrement, ResyncUpdates,
ResyncInserts];
AnBADODataSet.Update_Criteria := adCriteriaKey;
if AnBADODataSet.CursorLocation = clUseClient then begin
AnBADODataSet.CursorType := ctStatic;
AnBADODataSet.JoinsResolution := jrAuto;
AnBADODataSet.MarshalOptions := moMarshalModifiedOnly;
AnBADODataSet.RefreshType := rtRequery;
AnBADODataSet.Unique_Table := ATable;
AnBADODataSet.IndexFieldNames := IndexFieldNames;

end else begin
AnBADODataSet.CursorType := ctKeySet;
AnBADODataSet.JoinsResolution := jrManual;
AnBADODataSet.IndexFieldNames := '';
end;

try
AnBADODataSet.Prepared := true;
AnBADODataSet.Open;
except
AnBADODataSet.Prepared := false;
AnBADODataSet.Open;
end;
end;

Figaro · Guest

If there are additional parameters in queries use "ParametersArray,
ValuesArray: Variant;" arrays.

in Form.OnShow
// Open Master
dmMain.PrepareBADODataSet(dmMain.aqStore, VarArrayOf([]), VarArrayOf([]),
'Store');
// Open Detail
dmMain.PrepareBADODataSet(dmMain.aqStoreCells, VarArrayOf([]),
VarArrayOf([]), 'Cells', '', dmMain.dsStore);

Matthew Noordermeer · Guest

Thanks Figaro. Unfortunately it still doesn't work. Curiously, if I change
the database so that the DETAIL primary key is a uniqueidentifier, I can get
it to work OK. Not ideal, but maybe I'll leave it like this.

Thanks again for your help.

Regards

Mike

Matthew Noordermeer · Guest

Hi Figaro

I think the difference is I can set the primary key by calling CreateGuid
.... I don't need to get the primary key value from the server. I'm sure this
is the problem. DivisionBeltNo (see the Resync_Command below) is the
IDENTITY field - I don't think the value is being obtained from the server
although ResyncAutoIncrement is set to true.

Resync_Command query for the DETAIL ......

SELECT DivisionBeltNo, DivisionNo, BeltNo
FROM DivisionBelts
WHERE DivisionBeltNo = ?

Regards

Mike

Matthew Noordermeer · Guest

Hi Brian

Foreign key constraints and DRI. I've tried it with them removed and still
have the same problem.

Regards

Mike