TFDUpdateSQL, Stored Procedure - Setting field value from output variable

[Kyle Miller 1]

Given:

1. Database is MSSQL

2. TFDQuery & an TFDUpdateSQL assigned as the update object.

3. TFDUpdateSQL.InsertSql executes a stored procedure to perform insert, which affects a number of tables.

4. TFDUpdateSQL.InsertSql stored procedure returns an ID field value, which is the key field in TFDQuery. Ex: UserId.

5. UserId is an AutoInc TField in the TFDQuery.

 

What is the proper way to push the value returned by the stored procedure into the AutoInc field on an inserted record? Previous methods I used are not working.

 

Here is some code that does NOT work:

declare @SysUserId int;
exec dbo.SysUserUpdate @SysUserID = @SysUserID output, @UserName = :UserName, @Password=:Password,
     @FullName = :FullName, @EmailAddress = :EmailAddress, @CompanyLocationId = :CompanyLocationId,
     @IsActive = :IsActive,
     @SysApplicationKey = :SysApplicationKey, @ModuleAccess1 = :ModuleAccess1, @ModuleAccess2 = :ModuleAccess2,
     @PolicyAccess1 = :PolicyAccess1, @PolicyAccess2 = :PolicyAccess2

select :SysUserId=@SysUserId

 

[Michael Longneck 2]

Just

select @SysUserId as YourAutoIncFieldName

[Kyle Miller 1]

Thanks for the reply. I changed the Select as you have. No dice. The autoinc field still says -1 after InsertSql runs.

 

Any properties on the TFDAutoIncField or TFDQuery to check?

[Dmitry Arefiev 101]

You can try to do:

FDUpdateSQL1.Commands[arInsert].CommandKind := skSelect;

 

[Kyle Miller 1]

Perfect. Thank you guys.