Jump to content
Nathan Wild

Refresh Current Record To Access Value Of Newly Inserted AutoInc Field

Recommended Posts

I am trying to append records to a pair of tables in a master/detail relationship.  The master dataset is a writable FDQuery with an AutoInc key field.  When I post to this dataset the autoinc field has a value of -1.  I want to go on and write out detail records, but nothing I've tried will give me the actual value for autoinc field in the newly posted master dataset.

 

I am not able to call FDQuery.Refesh() as it gives me a "SQLBind Parameter has not been called for parameter" error (I've posted about this before and apparently it is a known bug).

 

FDQuery.RefreshRecord() gives me an error "[FireDAC][DApt]-400. Fetch command fetched [0] instead of [1] record. Possible reasons: update table does not have PK or row identifier, record has been changed/deleted by another user"

 

As my master dataset is sorted by the autoinc key field, I was able to solve the issue by calling FDQuery.First() and FDQuery.Last(), but this feels wrong, so I thought I would check in and see if anyone had a suggestion for how this *SHOULD* be done?

 

Thanks in advance...

Share this post


Link to post

Usualy I use "returning" to get the value of new autoincremented field (Firebird).

Share this post


Link to post
1 hour ago, Ruslan said:

Usualy I use "returning" to get the value of new autoincremented field (Firebird).

What do you mean by "returning"?

Share this post


Link to post

"returning" mentioned here is part of sql command, it >returns values of auto id and other expressions.

 

Maybe this is not available in Pervasive. Here is firebird documentation explaining it:

http://www.firebirdsql.org/refdocs/langrefupd21-insert.html

 

This is of course a solution coming from the database side. Firedac offers client side solutions.

Share this post


Link to post
On 4/12/2019 at 1:20 PM, Nathan Wild said:

I am using Pervasive.

AFAIK, Pervasive is not a directly supported database in FireDAC (only via ODBC). Therefore it may be necessary to handle those AutoInc fields manually as described in the documentation.

Share this post


Link to post

If you can call the Generator directly, like in Interbase/Firebird and other db's, I would prefer
that approach. I can't find any documentation that this is possible with Pervasive db's, but you can
check it out.

 

Create a query that calls the Generator and wrap it in a function like this. (The example is made in
Interbase syntax).

 

function GetGenID(GeneratorName: string): integer;
begin
    with FDQGetGenID do
    begin
        SQL.Clear;
        SQL.ADD('SELECT GEN_ID ('+GeneratorName+', 1) from rdb$database');
        Open;        
        Result := Fields[0].AsInteger;
        Close;
    end;
end;

Then when you insert new data you will call the GetGenID function.

FDQuery.insert;
FDQueryID.Value:=GetGenID('GeneratorName');
FDQueryMASTERDATA.AsString:= 'Some value';
FDQuery.Post;

 

You can also place the call to GetGenID in the FDQuery events, like OnNewRecord or BeforeInsert.

 

---
If this approach is not possible with Pervasive, then this approach could work. It is at least working with Interbase.
In this example I have used a DBGrid and A DataSource component to connect to the FDQuery. And the query has two fields
called "ID" and "MASTERDATA".

 

Set the AutoGenerator parameter value of ID field to arAutoInc (if you've not already done so).

 

In the OnDataChange Event of the DataSource put the following code:

 

if (FDQMasterID.Value < 0) and (FDQMaster.State in [dsInsert]) and not FDQMasterMASTERDATA.IsNull then
begin
   FDQMaster.Post;
   FDQMaster.Refresh;
end;

 

You need to check if at least one of the fields has a value apart from the ID field, or you will get
an error on the Post command, if the user adds to empty records.
 

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×