How to get auto incremented value in text field

Classic Servoy
1

I am using MS Sql Server 2005 as backend. I am having a form with text fields,labels and two buttons Save and Cancel for to adding (insert in db table) new employee information. For empid I want to display next available empid in text field and rest of field should filled by the user and hits save button which adds a new record at the end in table and Cancel button to roll back the data entry.

Here’s what I did, 1. At Servoy level I had configured employee table columns empid as Row identity as ‘PK’ and Sequence type as ‘db identity’ & 2. Programmatically got the max empid from the database and incremented by 1 which is the next available empid for the new record.

Initially, at DB side I had created empid as auto increment that worked to create a new empid, and for roll back the data entry for new employee, it doesn’t worked as no matter we add or roll back insert/add, DB is going to auto increment value. In case of roll back I want DB to roll back auto increment empid by 1. That doesn’t worked so switch to get next available empid programmatically ( if need more clarification let me know)

Can anyone help me how to display next available id in text field. Any suggestion is appreciated.

2

Won’t you get into trouble as soon as you have 2 users starting to add a record at the same time? Let’s say you have 20 records in your table, the first user starts to add a new record so he gets the number 21. Now before he saves that new record in the database, a second user starts to add a new record. He would also get 21 as the next available number??

My suggestion would be to let the user create the new record, and when the user saves the record, set a lock on the table, use the database event onRecordInsert to get the correct data value and after saving release the lock. So the user would see the number as soon as he has saved the new record.

3

Thanks for the quick response,

Thanks for the suggestion. Sorry, I forgot to mention this functionality is going to be used by only one user i.e Admin. I think I need not to worry about locking table. If no, then is there other way where in I can get next available emp id value and display in text field.

4

Well as long as you can be sure that only 1 user is going to be adding records in this table. I would suggest to store that value in a separete table and use that to show the correct next value. That will be faster as looking for the max value every time you need it.

5

Can you please explain in detail regarding creating separate table, how it works. How to add new values to seprate table and what about existing value once admin is going hit cancel button.

6

If you are sure that it is only going to be used by 1 user than you could also retrieve the max value from the table when you start with a new record.
it would look something like this:

  var _ds = databaseManager.getDataSetByQuery('example_data','SELECT MAX(emp_nr) FROM EMPLOYEE',null,1);
  var _maxNr = _ds[0][0];
  
  foundset.emp_nr = _maxNr +1;

This way you have full control over this value so if you dont save the record (rollback) than the value is not stored and will be used for the next new record.

7

Thank you very much jdbruijn.

You explained me very well. It is working.

8

jdbruijn:
If you are sure that it is only going to be used by 1 user than you could also retrieve the max value from the table when you start with a new record.
it would look something like this:

  var _ds = databaseManager.getDataSetByQuery('example_data','SELECT MAX(emp_nr) FROM EMPLOYEE',null,1);

var _maxNr = _ds[0][0];

foundset.emp_nr = _maxNr +1;



This way you have full control over this value so if you dont save the record (rollback) than the value is not stored and will be used for the next new record.

Hi Jos, this is not save, if you use this with multiple users, you will end up with same id’s!
You have to use some record locking to be 100% sure you won’t end up with the same id’s!

9

Hi Harjo, I know this is not save for multiple users. I said the samething in my first post, but as said is only has to work for a single user. So in that case it will work, but like you said in a multiple user environment this will give you problems later on. I use a general settings table that has the most recent value and when I save a new record I lock my settings record. Retrieve and update the value in the settings record. And finally use that value as the ticket-id in my work table.

10

Indeed, you also need some looping, because if the record is locked, what do you do than?

this is the code we use:

var theCounter = 1
	while (!databaseManager.acquireLock(main_to_seq_invoice, -1)) {
		application.sleep(500)
		theCounter++
		if (theCounter > 60) {
			// stop if 60 times you attempted to lock
			//showdialogcode here that says that it's not going to happen this time
			plugins.dialogs.showWarningDialog('i18n:1.error', 'i18n:1.message.failedlock', 'i18n:1.ok');
			return null
		}
	}
	databaseManager.refreshRecordFromDatabase(main_to_seq_invoice, 1)
	var invoicecode = main_to_seq_invoice.sequencenumber
	main_to_seq_invoice.sequencenumber += 1
	databaseManager.saveData()
	databaseManager.releaseAllLocks();
	return invoicecode
11

Ah yes, I forgot about the loop.
I also use a try…finally with the releaselock inside the finally block to make sure that the lock gets released even when somehting goes wrong.

12

Thanks both of you