Multiple records inserted with RuntimeListViewRowCount option

Multiple records inserted with RuntimeListViewRowCount option

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 Setup

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.

SmartObject with List Method
SmartObject with List Method

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.

RuntimeListViewRowCount setting
RuntimeListViewRowCount setting

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.

Test List View
List View with the page count shown

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.

List View Rule
List View Rule

This is the configuration for the SO_TestInsertSP SmartObject.

SmartObject event configuration
SmartObject event configuration

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.

Multiple records inserted with RuntimeListViewRowCount option
Multiple records inserted with RuntimeListViewRowCount option

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.

Execute type method
Execute type method

3. Update K2 smartofrms rules

With the new SmartObject created, we will update the smartforms rule.

Updated rule
Updated 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!

 

Have fun!

Ji Kai

2 thoughts on “Multiple records inserted with RuntimeListViewRowCount option

    1. Are you still having the multiple records issue after updating the SmartObject to Execute type method?

Leave a Reply