I try to retrieve the id of the record(s) inserted with a query to no
avail. The closest I've got is, from
http://forum.pcsoft.fr/es-ES/pcsoft.us.windev/30754-windev-insert-query-get-last-inserted/read.awp,
HRetrieveItem but first it will only get me the last record, regardless
of who inserted it (if someone else added other records then this will
not be good) but regardless, it does not work anyways as I am inserting
the records using a transaction.
My DB is MS SQL 2012 using OLEDB and the simplest code that does not
work is like this:
// I defined a very simple query named QRY_InsertCompany like INSERT
INTO COMPANIES (NAME) VALUES ({paramName})
FUNCTION PRIVATE AddCompany(cCompany): int
QRY_InsertCompany.ParamName = cCompany
IF HExecuteQuery(QRY_InsertCompany, hWithTransaction) THEN
sLastRecord is string = HRetrieveItem (Companies, 1)
RESULT sLastRecord
END
RESULT 0
Ignoring the fact that the return value would be wrong as is a string
instead of int, it will return the last record of the table but it is
not the one inserted probably because of the transaction which I cannot
close for I need to use this ID on other inserts, so it does not help me
at all. Even if it worked, there is no guarantee that is MY record and
even if all that is satisfied it will work for one record, but what if I
do something like
INSERT INTO COMPANIES (NAME) SELECT NEW_NAME FROM NEWCOMPANIES
Or something like that, how would I retrieve all the inserted pks?
(In other languages I would use the OUTPUT clause of SQL Server Insert
command but I was not able to make it work with WinDev 21 and found
little help
TIA