Problem with querying identity with SDAC 4.00.0.7 / D7 / Vista / SQL-Server 2000

[PeterCH]

Hi

When I append a record to a table with Identity field i can't get the ID value back to my app. I Upgraded to 4.00.0.7, in older versions i never had problems with identity.

I Created a small test-project with a single TMSQuery (Q), in it's BeforeUpdateExecute-Statement i change the parametertype of the last parameter to InputOutput as suggested in the books, this works fine,
Params.Count is 4 and I don't get any error.

Code: Select all

procedure TForm1.QBeforeUpdateExecute(Sender: TCustomMSDataSet;
  StatementTypes: TStatementTypes; Params: TMSParams);
begin
 if stInsert in StatementTypes then
 Params[Params.Count - 1].ParamType := ptInputOutput;

 MessageDlg(IntToStr(Params.Count), mtWarning, [mbOK], 0);
end;

After executing i tried to access the id-Value, i don't have any parameters (Paramcount = 0) and the value of the ID-field is null.

I would be very glad if someone could give me a hint in what i am doing wrong. Thanks.

To show my settings, table and query I put all into single Button-OnClick-Event and post it below:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
var
 SQL : String;
begin
 DB.Server := 'localhost';
 DB.Username := 'sa';
 DB.Password := 'xxx';
 DB.LoginPrompt := false;
 DB.Database := 'Northwind';
 DB.Open;

 SQL := 'if not exists (select null from sysObjects where Name = ''IDTest'' and Type = ''u'')' +chr(13)+chr(10);
 SQL := SQL + 'CREATE TABLE IDTest (ID int identity primary key,Val varchar(10))';

 DB.ExecSQL(SQL,[]);

 Q.Options.QueryIdentity := true;

 Q.SQL.Clear;
 Q.SQL.Add('SELECT * FROM IDTest');
 Q.SQLInsert.Clear;
 Q.SQLInsert.Add('INSERT INTO IDTest (Val) VALUES (:Val)');
 Q.SQLInsert.Add('SET :ID = SCOPE_IDENTITY()');

 Q.Open;
 Q.Append;
 Q.FieldValues['Val'] := 'test';
 Q.Post;

 MessageDlg(Q.SQLInsert.Text, mtWarning, [mbOK], 0);

 MessageDlg(IntToStr(Q.ParamCount), mtWarning, [mbOK], 0);

 if Q.FieldByName('ID').IsNull then
  MessageDlg('null', mtWarning, [mbOK], 0)
  else
  MessageDlg(IntToStr(Q.FieldValues['ID']), mtWarning, [mbOK], 0);
end;

[Antaeus]

We tested this issue with several previous versions of SDAC, but it worked with neither. What version of SDAC did you use before? You can see the exact version of SDAC in the About sheet of TMyConnection Editor.

To make this work, you should turn on the ReturnParams options:

Code: Select all

  Q.Options.ReturnParams := True;