Last week, I helped a developer troubleshoot a weird issue where multiple records inserted with RuntimeListViewRowCount option. In case you are wonder what is RuntimeListViewRowCount, it is a web.config setting that enables all List Views in K2 smartforms to return and display the total row counts. It is disabled by default, due to past performance issues and you can read more of this setting here.
In this article, I will be showing you the scenario that will cause the issue and how to work around it.
The following is a sample setup of the environment to reproduce the issue.
1. SQL Stored Procedure
This is a stored procedure that does some processing and finally inserts the data to a SQL table and returns a result. Take note of how @Result is returned from the TSQL.
ALTER PROCEDURE [dbo].[TestInsertSP] @Name VARCHAR(255) AS BEGIN DECLARE @Result VARCHAR(5) -- some other processing INSERT INTO [dbo].[TestInsertTable] VALUES(@Name) -- some other processing SET @Result = 'OK' SELECT @Result -- return result END
2. The SQL Stored Procedure SmartObject
The Stored Procedure is generated as a List type SmartObject method. Why is it List, but not Read or Execute type? This is due to the last line of the stored procedure being a “select” statement. It doesn’t matter whether you are return one or multiple records. When the SmartObject server reflects the stored procedure, it will always evaluate it as a List type when the last line is a “select” statement.
3. The K2 smartforms runtime and RuntimeListViewRowCount
Next, we will edit the K2 smartforms runtime site’s web.config and set the RuntimeListViewRowCount true. This will enable the page count for all List Views in K2 smartforms.
4. A K2 smartforms List View for testing
Lastly, we create a List View for testing. The following shows my test view. Notice that when RuntimeListViewRowCount is set to true, you will see that the paging bar will have the total page count indicated.
The rule in this form is pretty straight forward. Clicking Insert Data button will call on the stored procedure SmartObject above to insert the current date/time as the data, then it will refresh the list. Take note here that the method name is List.
This is the configuration for the SO_TestInsertSP SmartObject.
We shall proceed to test the List View now.
Testing the issue – Multiple records inserted with RuntimeListViewRowCount option
The test is simple. Click on Insert Data button and you expects to see one new record created, as per how the stored procedure is written above. But instead, we see 2 records inserted.
So how do we resolve this multiple entry issue?
The Problem & Solution
Why are we seeing 2 records inserted here? This is because the RuntimeListViewRowCount option will cause the SmartObject to be executed twice in K2 smartforms. First time to get the result, the second time to get the total records count. Although this SmartObject is not used to load the List View content, smartforms will still try to execute List methods twice in a List View. Do remember that the SmartObject created earlier is generated with a List method.
So how to resolve the problem of multiple records inserted with RuntimeListViewRowCount option? The answer is to change the SmartObject method type to anything but List type. It does not make sense for this SmartObject to have a List type method when it only return a result status.
1. Update the Stored Procedure
First, we need to ensure the last line of the stored procedure is not a “select” statement. This is how I changed it.
ALTER PROCEDURE [dbo].[TestInsertSP] @Name VARCHAR(255), @Result VARCHAR(5) = NULL OUTPUT AS BEGIN -- some other processing INSERT INTO [dbo].[TestInsertTable] VALUES(@Name) -- some other processing SET @Result = 'OK' END
2. Refresh Service Instance and re-create the SmartObject
After updating the stored procedure, we need to refresh the Service Instance. You will see that the method is now an Execute type. We will recreate the SmartObject using the same GUID.
3. Update K2 smartofrms rules
With the new SmartObject created, we will update the smartforms rule.
4. Test again
Now go ahead and test it again. This time round, you will see that there is only one record created when you click on the Insert Data button. Simple!
- Multiple records inserted with RuntimeListViewRowCount option - 30 May 2017
- Friendly Error Message on K2 smartforms - 04 May 2017
- Add company logo and change K2 login form descriptions - 02 Aug 2016