Refreshing Autoinc fields

salim_naufal · Post by **salim_naufal** » Mon 14 Dec 2009 18:24

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"

Dimon · Post by **Dimon** » Tue 15 Dec 2009 09:59

New values of autoincrement fields should be set automatically.

salim_naufal · Post by **salim_naufal** » Tue 15 Dec 2009 10:06

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

Dimon · Post by **Dimon** » Wed 16 Dec 2009 14:49

You don't need to use the SELECT statement in the SQLInsert query. Use only the following query:

Code: Select all

INSERT INTO SUPPLIERS 
(Name, ...) 
VALUES 
(:Name, ...) 
SET :ID = SCOPE_IDENTITY()

and the autoincrement field will be set automatically.

salim_naufal · Post by **salim_naufal** » Wed 16 Dec 2009 15:08

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

Dimon · Post by **Dimon** » Thu 17 Dec 2009 15:02

Please open the SDACDemo project and start it. Select the Query demo, enter the following query:

Code: Select all

SELECT * FROM emp

and try to insert a new row.
Is the EMPNO field set automatically?

salim_naufal · Post by **salim_naufal** » Fri 18 Dec 2009 09:51

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

salim_naufal · Post by **salim_naufal** » Fri 18 Dec 2009 09:51

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

Dimon · Post by **Dimon** » Tue 22 Dec 2009 10:22

If you need you can use that query in the SQLInsert property:

Code: Select all

INSERT INTO SUPPLIERS 
(SupplierNumber, ...) 
VALUES 
(:SupplierNumber, ...) 
SET :SupplierID = SCOPE_IDENTITY()

and the autoincrement field will be set automatically.

salim_naufal · Post by **salim_naufal** » Tue 22 Dec 2009 10:55

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

Dimon · Post by **Dimon** » Tue 22 Dec 2009 17:09

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

Code: Select all

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

Also set the TMSQuery.Options.ReturnParams property to True.