Error searching - unexpected nr of records affected

Classic Servoy
1

This is a simple application that displays a customer record and associated transactions. The customer record displayed is determined by the login username. So I am trying to filter the foundsets from the SQL Server data tables to only have the records for the relevant customer. I have tried the code below in the form OnLoad, the OnShow and also in the login form when a successful login happens:

		var currentUsername = utils.stringTrim(security.getUserName());
		var currentAccount = '';
				
		var customerFoundset = databaseManager.getFoundSet('statements', 'customer');
		if (customerFoundset.find())
		{
			servoyusername = currentUsername;
			if (customerFoundset.search())
			{
				currentAccount = customerFoundset.accountcode; 
			}
		}
			
		currentAccount = utils.stringTrim(currentAccount);
				
		var success = databaseManager.addTableFilterParam('statements', 'customer', 'accountcode', '=', currentAccount, 'accountfilter');

However this produces the following in the server log:

2011-12-12 15:54:46,310 ERROR [http-8080-3] com.servoy.j2db.util.Debug - Error executing sql: update customer set servoyusername=? where accountcode = ? and accountcode = ? with params: ['CAR000101' ,type: java.lang.String, 'ADA0001 ' ,type: java.lang.String, 'ADA0001' ,type: java.lang.String]
com.servoy.j2db.util.ServoyException: Update/insert failed, unexpected nr of records affected: expected 1, actual 2

Why is it trying to update the customer table? I’m searching on it!

2

Alan,

this line:

servoyusername = currentUsername;

is setting a value in your current foundset, not the one you are searching in.

That is causing the update.

The error you get is a bit stranger.
Do the pk columns you have defined on the customer table really define unique records?
It seems there are 2 records with servoyusername ‘CAR000101’ and accountcode ‘ADA0001’.

Rob

3

Thanks Rob - upon closer investigation there was indeed duplication.

I’m not clear about the other issue - if I’m returning a foundset from getFoundSet() and going into Find() mode on that, then isn’t that what’s being searched ?

4

Alan,

servoyusername = currentUsername;

is really a shortcut for setting a value of the foundset tied to the form on the selected record:

foundset.getSelectedRecord().servoyusername = currentUsername;

But you want to search in the foundset you have created, you should do:

customerFoundset.servoyusername = currentUsername;

Rob

5

Many thanks Rob, I will try that.