Getting ID value after insert

[JensFudge]

Hi

I am using SDAC to handle a Delphi app on a MS SQL server.
I want to insert a record in a master table, and thereafter some records in a detail table.
How do I obtain the ID of the master table with SDAC?

The ID field is a identity field (autoinc).

I tried with

query.insert;
query.Fieldbyname('blabla').asString := 'blabla';
query.post;
ID := query.FieldByName('ID').asInteger;

But I always get 0 back in the ID field.

Anyone know how to do this?

Thanks

Jens Fudge

[Dimon]

If you use SDAC SQL Generator, check that the value of the TMSQuery.Options.QueryIdentity property is True and you have INDENTITY columns in the result set. In this case an identity field value will be set automatically.
If you use your own SQL query then add this line to the query:
SET :Identity_field = SCOPE_IDENTITY()
In this case you will get the field value as an output parameter.

[JensFudge]

Thanks for the fast respons, but I cannot get it to work. I build the query manually like this:

var
aQry : TMSQuery;
begin
aQry := TMSQuery.Create(nil);

try
aQry.Connection := msConnection;
aQry.SQL.Add('Select booking.BookingID, booking.BilID, Booking.BookingTime, booking.BrugerID, Booking.KundeID, booking.Note');
aQry.SQL.Add('FROM');
aQry.SQL.Add('BOOKING');

aQry.SQLInsert.Add('INSERT INTO booking');
aQry.SQLInsert.Add('(BilID, KundeID, BrugerID, BookingTime, Note)');
aQry.SQLInsert.Add('VALUES');
aQry.SQLInsert.Add('(:BilID, :KundeID, :BrugerID, :BookingTime, :Note)');
aQry.SQLInsert.Add('SET :BookingID = SCOPE_IDENTITY()');

aQry.SQLRefresh.Add('SELECT BilID, KundeID, BrugerID, BookingTime, Note FROM booking');
aQry.SQLRefresh.Add('WHERE');
aQry.SQLRefresh.Add('BookingID = :BookingID');

aQry.Options.QueryIdentity := true;

aQry.Open;
aQry.Append;
aQry.FieldByName('BOOKINGTIME').AsDateTime := now;
aQry.FieldByName('BRUGERID').AsInteger := 12;
aQry.Post;
ShowMessage(intTostr(aQry.FieldByName('BOOKINGID').asInteger));
finally
aQry.Free;
end;
end;


The refresh part I only inserted to try to get it to work, but that didnt make any difference, same with options.queryIdentity := true;
The Identity field is BOOKINGID.

The ShowMessage just show a 0 every time.
I have checked the database, it does in fact insert the record correctly and the BOOKINGID field is correctly filled in.

If I stick the
aQry.RefreshOptions := aQry.RefreshOptions + [roAfterInsert];
in before opening the query, I get an EDatabaseError: "Refresh failed, found 0 records"


Thanks for the help
Jens Fudge

[Lithium™]

I'm never have any problems to get identity value after post.
I suspect your problems occurs because two select clauses in your query are presented. You need to call msQuery.Open and then msQuery.OpenNext for second fetch, which just set value to 'BOOKINGID' field.
You must set Options.StrictUpdate to FALSE to avoid exception 'Refresh failed, found 0 records', that raised after first fetch, because this one return nothing.

In general I'm not recommend such query constructions. It's better to execute a separate query to insert records rather than results are selected.

[JensFudge]

I'm never have any problems to get identity value after post.
I suspect your problems occurs because two select clauses in your query are presented. You need to call msQuery.Open and then msQuery.OpenNext for second fetch, which just set value to 'BOOKINGID' field.
You must set Options.StrictUpdate to FALSE to avoid exception 'Refresh failed, found 0 records', that raised after first fetch, because this one return nothing.

In general I'm not recommend such query constructions. It's better to execute a separate query to insert records rather than results are selected.

Lithium, thanks for the effort to pitch in here, but I think you missed the point somewhere.
I am trying to insert a record in a table, and get whatever ID MSSQLServer decided to stick in the Identity field.

I suspect your problems occurs because two select clauses in your query are presented. You need to call msQuery.Open and then msQuery.OpenNext for second fetch

I have only one Select statement..
A TMSQuery's SQL consists of a SQL (for selecting), a InsertSQL, a UpdateSQL, a DeleteSQL and a RefreshSQL.
So there is nothing OpenNext about it
I have no first fetch and secon fetch.. just one fetch alltogether.

I do agree, i should just send INSERT INTO ... to the table, and this would insert a record.. This would however not return the ID in the BOOKINGID field. So just doing a Insert wont work.
I cannot do an insert followed by a Select either, as another user could have inserted a record (or many more for that matter..)

I think however I have solved the problem by inserting an eventhandler on the BEFOREUPDATEEXECUTE event, setting the params.parambyname('BOOKINGID') to a ptInputOutput parameter...

Regards

Jens Fudge

[Lithium™]

Oh, I'm sorry, there is missed point realy .
I've seen "EDatabaseError: Refresh failed, found 0 records" first, and I have such problem early. So I was shown all queries are one select statement... But Options.SrtictUpdate = FALSE you can use for avoid exception above along.

[Dimon]

To solve the problem you should set ParamType of the BookingID parameter to ptInputOutput in the TMSQuery.BeforeUpdateExecute event handler, like this:

Code: Select all

Params.ParamByName('BookingID').ParamType := ptInputOutput;

[tinof]

Hi,

i have the same problem with SDAC 6.1.6 / Delphi 2009 / MSSQL 2005.
The sample connects with the northwind - database.

If i set the query.sql property to

Code: Select all

 "SELECT * FROM categories"

and leave all properties at default i get the categoryID after an insert as expected.

If i now use the SQL - Query Builder from SDAC i get the i.e. the update statement i get

Code: Select all

INSERT INTO categories
  (CategoryName, Description, Picture)
VALUES
  (:CategoryName, :Description, :Picture)
SET :CategoryID = SCOPE_IDENTITY()

But i never get an CategoryID - value after insert / append now.

- Tfield.autogeneratevalue = arDefault / arNone /arAutoInc --> no affect
- TMSQuery.Options.QueryIdentity = true/false --> no affect
- TMSQuery.BeforeUpdateExecute :
Params.ParamByName('CategoryID').ParamType:=ptInputOutput;
--> no affect

If i delete the Update SQL Statement and leave the "SELECT * FROM .." - it will work.

If i set debug=on i see, that Params.ParamByName('Category_ID').ParamType:=ptInputOutput; influences the final code. But i dont get an ID back.


What's wrong with my code?

Thanks für any Help!
Tino

[tinof]

* SOLVED *

we have to set

MSQuery.Options.ReturnParams:=true!

complete:

1. place an TMSQuery, generate the Scripts
2. set Options -> ReturnParams = true
3. edit TMSQuery.BeforeUpdateExecute:

Code: Select all

procedure Tdm_Projekt.queryBeforeUpdateExecute(
  Sender: TCustomMSDataSet; StatementTypes: TStatementTypes; Params: TMSParams);
begin
  If stInsert IN statementTypes then  // otherwise there is no param with this name
    Params.ParamByName('query_ID').ParamType := ptInputOutput;
end;

And you get an ID.

Regards
Tino

[AndreyZ]

You have found the correct solution.

[MosCom]

Hi @tinof

I'm going your way, but I have not been successful. I can not get the last number in memory because I do not use INSERT INTO. For this reason, the record gets mixed up when someone else gets the last number.

What exactly should I do on the Delphi side?

Very pleased to be of assistance. Best regards

* SOLVED *

we have to set

MSQuery.Options.ReturnParams:=true!

complete:

1. place an TMSQuery, generate the Scripts
2. set Options -> ReturnParams = true
3. edit TMSQuery.BeforeUpdateExecute:

Code: Select all

procedure Tdm_Projekt.queryBeforeUpdateExecute(
  Sender: TCustomMSDataSet; StatementTypes: TStatementTypes; Params: TMSParams);
begin
  If stInsert IN statementTypes then  // otherwise there is no param with this name
    Params.ParamByName('query_ID').ParamType := ptInputOutput;
end;

And you get an ID.

Regards
Tino

[ViktorV]

To solve the issue, please try to set the TMSQuery.Options.QueryIdentity property to True.
If it does not help to solve the problem, please compose a small sample demonstrating the issue and send it to usvia form e-support: https://www.devart.com/company/contactform.html including scripts for creating database objects.

[MosCom]

Hi @ViktorV,

I am using uniquery and I do not have the QueryIdentity property.

Best Regards.

To solve the issue, please try to set the TMSQuery.Options.QueryIdentity property to True.
If it does not help to solve the problem, please compose a small sample demonstrating the issue and send it to usvia form e-support: https://www.devart.com/company/contactform.html including scripts for creating database objects.

[ViktorV]

You can use next code:

Code: Select all

UniQuery.SpecificOptions.Values['SQL Server.QueryIdentity'] := 'True';