0

I have a Delphi XE3 Firemonkey app with LiveBindings to a SQL Server. I can connect and see all the data on a grid. I have a field ("id") which is an "identity field" on SQL (more commonly known as an AutoInc).

When I try to add a record I get "Field 'id' must have a value."

In the past, the fix for this has been to turn off the required field boolean. (With DBISAM, for example, you make the field in the database itself not required and the DB engine fills it in when it gets there, but you can't make an identity field not required in MSSQL.)

I've turned off the requirement for the TField itself, but that has no effect.

RRUZ
  • 134,889
  • 20
  • 356
  • 483
blake
  • 1

1 Answers1

2

I had the same problem with my application. The solution for that is a little bit tricky. First you have to set the IDENTITY_INSERT to ON.

procedure TdmUnit1.Identity(Table: string; OnOff: boolean);
var s: string;
begin
  if OnOff = true then
    s := 'ON'
  else
    s := 'OFF';
  s := 'SET IDENTITY_INSERT ' + '[dbo].[' + Table + '] ' + s;
  SQLQuery1.SQL.Clear;
  SQLQuery1.SQL.Add(s);
  SQLQuery1.ExecSQL;
end;

Then you have to look for the highest index inside your table. That you can realize with the OnNewRecord-Event:

procedure TdmUnit1.ClientDataSet1NewRecord(DataSet: TDataSet);
var
  s: string;
  i: integer;
begin
  s := 'SELECT IDENT_CURRENT(''AnyTable'')';
  SQLQuery1.SQL.Clear;
  SQLQuery1.SQL.Add(s);
  SQLQuery1.ExecSQL;
  SQLQuery1.Active := true;
  i := SQLQuery1.Fields[0].AsInteger;
  ClientDataSet1.FieldByName('DB_IDX').AsInteger := i+1;
  SQLQuery1.Active := false;
end;

After ApplyUpdates(0), you have to switch OFF the IDENTITY_INSERT.

user1802906
  • 21
  • 2
  • Sorry, just saw this. I just went traditional for this app but I'll give this a try next chance I have. – blake Nov 25 '12 at 03:16