PC SOFT

PROFESSIONAL NEWSGROUPS
WINDEV, WEBDEV and WINDEV Mobile

Home | Recent messages | Connect |  | English US
Home → WINDEV 2024 → Retrieving ID of records
Retrieving ID of records
Started by Hugo, Nov., 08 2016 5:21 PM - 4 replies
#1
Posted on November, 08 2016 - 5:21 PM
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
#2
Posted on November, 08 2016 - 7:17 PM
Hello Hugo,

to do that, if it's supported by your target DB, you need to use the SQL
syntax sending back the created ID.
Once you have that and you can make it work in a sql manager, then you
can use it from windev using the hwithoutcorrection parameter

Best regards

--
Fabrice Harari
International WinDev, WebDev and WinDev mobile Consulting

Ready for you: WXShowroom.com, WXReplication (open source) and now WXEDM
(open source)

More information on http://www.fabriceharari.com


Le 11/8/2016 à 11:21 AM, Hugo a écrit :
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
#3
Posted on November, 14 2016 - 6:25 PM
Thanks for the answer Fabrice, but I still cannot get this going, very frustrating as this should be very easy. I am sure I am not grasping the basic concepts of WinDev as I just started, but I cannot find any example of how to do this which to me is very disappointing as it is kind of basic (updating a series of related tables inside a transaction)

Anyways, the closest I've got is something like this:

QRY_CompanyInsert is

INSERT INTO Companies ([NAME]) OUTPUT (Inserted.PK) VALUES ('{P1}')


You are probably asking why the quotes... me too, but if I do not surround the parameter in quotes ({P1}) then I get an error saying that the column name (with the value of the company I typed) does not exist... The simplified code I use is

IF SQLTransaction(sqlStart, GateAccessConnection) THEN
nFKCompany is int = AddCompany(COMBO_Company..DisplayedValue)
SQLTransaction(sqlCommit, GateAccessConnection)
END


FUNCTION PRIVATE AddCompany(cCompany): int

//{"QRY_CompanyInsert.P1"} = cCompany // Error the parameter does not exist
IF HExecuteQuery(QRY_CompanyInsert,GateAccessConnection,hWithTransaction+hQueryWithoutCorrection, cCompany) THEN
// sLastRecord is string = SQL.IdAuto // 0
// nLastRecord is int = {"QRY_CompanyInsert.PK"} //0
// nLastRecord is int = HRetrieveItem (Companies, 1) // Last record of table, but before transaction
nLastRecord is int = HRetrieveItem (QRY_CompanyInsert, 1) //0
RESULT nLastRecord
ELSE
Info(HErrorInfo())
END
RESULT 0


What is even more strange is that this code adds 3 records to the table!!!

I've tried many other things but this is the closest I've got, at least it inserts the records, 2 too many unfortunately
#4
Posted on November, 14 2016 - 6:48 PM
Hi Fabrice,

I see that my answer is lost...

Not my good month...
#5
Posted on April, 26 2018 - 7:06 AM
Hello,

I got the w23 42u version but still have the problem of duplicate rows during insert statement with output clause.

Any ideas ?
→ Go back to WINDEV 2024