Cannot get id of inserted row
Hi I'm trying to get the Inserted record's id. I'm trying this script but have no luck.
var qry = toolbox.executeQuery('INSERT INTO import.import_shipment_voucher(import_shipment_voucher_vendor_id,import_shipment_voucher_date,import_shipment_voucher_refno,import_shipment_voucher_status) VALUES (<?value("vendorId")?>,<?value("voucherDate")?>,<?value("referenceNo")?>,<?value("voucherStatus")?>);',params);
var qryId = qry.lastInsertId;
var q = qryId.toInt;
toolbox.messageBox("",mywindow,"",q);
In order to do this your insert query needs to be modified slightly, you need to use the RETURNING statement to tell PostgreSQL to actually return that information to you.
var qry = toolbox.executeQuery('INSERT INTO import.import_shipment_voucher(import_shipment_voucher_vendor_id,import_shipment_voucher_date,import_shipment_voucher_refno,import_shipment_voucher_status) VALUES (<?value("vendorId")?>,<?value("voucherDate")?>,<?value("referenceNo")?>,<?value("voucherStatus")?>) RETURNING import_shipment_voucher_vendor_id;',params); // I am guessing which column you want returned.
if (qry.first())
{
var q = qry.value("import_shipment_voucher_vendor_id");
toolbox.messageBox("",mywindow,"",q); //Note in 4.0 and above this will be QMessageBox.information(mywindow, "Information", q);
}
