Putting numbers inside the Quick Search results in the following error: "The conversion of the nvarchar value overflowed an int column. The statement has been terminated."

  • 16 February 2021
  • 0 replies
  • 513 views

Userlevel 5
Badge +20
 

Putting numbers inside the Quick Search results in the following error: "The conversion of the nvarchar value overflowed an int column. The statement has been terminated."

kbt167996

PRODUCT
K2 Cloud
K2 Five
K2 blackpearl
This article was created in response to a support issue logged with K2. The content may include typographical errors and may be revised at any time without notice. This article is not considered official documentation for K2 software and is provided "as is" with no warranties.

Issue

When using the Quick Search on a List View, if you input numbers only you get the following error:
 

The conversion of the nvarchar value '215355804001' overflowed an int column. The statement has been terminated. Where the numbers listed would be the numbers you inputted into the search.

Image

Full error:

 

The conversion of the nvarchar value '12345678901' overflowed an int column. The statement has been terminated.

 

Type: SourceCode.SmartObjects.Client.SmartObjectException
Source:
SourceCode.SmartObjects.Client
Method Base
Member Type:
Method
Name:
GetSmartObjectReader
Module
Scope Name:
SourceCode.SmartObjects.Client.dll
Declaring Type
Full Name:
SourceCode.SmartObjects.Client.SmartObjectClientServer
Stack Trace:
at SourceCode.SmartObjects.Client.SmartObjectClientServer.GetSmartObjectReader(SmartObject smartObject, ExecuteListReaderOptions options, Dictionary`2& propertyReference)
at SourceCode.Forms.AppFramework.FormsApiToXml.GetSmartObjectReader(ExecuteListReaderOptions options)
at SourceCode.Forms.AppFramework.FormsApiToXml.ToListResults(SmartMethodBase executedMethod, Boolean overrideListCount)
at SourceCode.Forms.AppFramework.FormRuntime.SmartObjectExecution(BrokerSmartObject brokerSmo, SmartObjectBrokerMetaData brokerMetaData, Boolean outputDependencies, SmartObjectClientServer smoClientServer, Queue`1 soInstances)
at SourceCode.Forms.AppFramework.FormRuntime.WorkXmlToApi(BrokerPackageCollection packages, SmartObjectClientServer smoClientServer)
at SourceCode.Forms.AppFramework.FormRuntime.WorkXmlToApi(Stream input, Stream output)
at SourceCode.Forms.Runtime.AJAXCall.ProcessRequestInternal(HttpContext context)

 

Symptoms

This issue occurs if the List View is setup using a SQL Service Instance SmartObject that has an Int column that can be searched. SQL limits the amount of numbers that can be inputted into a Int column which is why this error appears.

Resolution

Inside the SQL Service Instance SmartObject that you are using, you need to find the SQL Table that it is using inside the database. Inside this database you need to change the Int column to a BigInt. This will allow for more digits to be searched. If this is not an option, then on the Quick Search bar you need to change the drop-down field from All Fields to the column you wish to search. Keep in mind that if you select the column that was created in the SQL Table as an Int, you will still get the error as this is a SQL limitation.

Another workaround is to use a SmartBox SmartObject and select a Number type instead of using a SQL Service Instance as this will allow you to input as many digits as you wish (up to 256).

Keep in mind that in the Quick Search field, if you input a single letter it will NOT error.  The error only happens if it is strictly numbers only.

 

 

Please Note: A bug report was filed with this KB article so a fix may be implemented in a future version of the product.  

0 replies

Be the first to reply!

Reply