Devart Forums

I have a simple program that inserts into a MsSQL server table that has an autoinc primary key field.
Using the TMSQuery component, I insert data then execute the MSQuery.Post method. Is there a way of retrieving automatically the Autoinc field?

For example, the table is

Customers: ID identity (autoinc), name nvarchar(100)
The Delphi 2009 application consists of a simple MSConnection, MSQuery, Datasource, dbnavigator and dbgrid.

What options should be set? (I did set the Field in the query to "AutoGeneratedValue" to "arAutoInc"

New values of autoincrement fields should be set automatically.

The values are set automatically in the database but I must refresh manually to get them in my Delphi dataset. For example:
MyQuery.insert;
...
MyQuery.Post;

then Simply using MyQuery.FieldByName('ID').AsInteger to get the autoinc field.
I don't know if this is done automatically or not.

My InsertQuery in the MSQuery is:

INSERT INTO SUPPLIERS
(Name, ...)
VALUES
(:Name, ...)
SET :ID = SCOPE_IDENTITY()
SELECT :Name = Name, ...
FROM SUPPLIERS
WHERE
ID = :Old_ID

You don't need to use the SELECT statement in the SQLInsert query. Use only the following query: and the autoincrement field will be set automatically.

I did do that. still nothing. I re-created the project and added a new MSQuery component. Tried the insertion with the defaults, set the ID field to autoinc, tried with the DMLRefresh option set to true... still nothing.
I am using SQL Server 2005. I tried everything. Still, the ID is not being refreshed.

What options should be set at the field level and at the MSQuery level?

Thanks
Salim

Please open the SDACDemo project and start it. Select the Query demo, enter the following query: and try to insert a new row.
Is the EMPNO field set automatically?

Hi, I did that and it worked. I returned to my project and tried to see how things work.

The Autoinc field value is returned only if the InsertSQL is kept blank.
In my case it is:

The actual insertsql command is:
INSERT INTO SUPPLIERS
(SupplierNumber, ...)
VALUES
(:SupplierNumber, ...)
SET :SupplierID = SCOPE_IDENTITY()

Where :SupplierID is my autoinc field.

Is this normal?
Thanks
Salim

Hi, I did that and it worked. I returned to my project and tried to see how things work.

The Autoinc field value is returned only if the InsertSQL is kept blank.
In my case it is:

The actual insertsql command is:
INSERT INTO SUPPLIERS
(SupplierNumber, ...)
VALUES
(:SupplierNumber, ...)
SET :SupplierID = SCOPE_IDENTITY()

Where :SupplierID is my autoinc field.

Is this normal?
Thanks
Salim

If you need you can use that query in the SQLInsert property: and the autoincrement field will be set automatically.

This is exactly what I have and it does not work.

To solve the problem you should set ParamType of the SupplierID parameter to ptInputOutput in the TMSQuery.BeforeUpdateExecute event handler, like this: Also set the TMSQuery.Options.ReturnParams property to True.