I am working on an application in ODC. Everything was working fine, but suddenly, one of my screens stopped loading data and started showing the following error:
“Error converting data type NVARCHAR to BIGINT.”
The screen uses SQL to fetch data.
Could you please help me identify the root cause of this error and explain how to troubleshoot and fix it? Also, how can I identify which column or parameter is causing the NVARCHAR-to-BIGINT conversion issue?SQL - SELECT COUNT(1)FROM {EntityA} JOIN {EntityB} ON {EntityA}.[Code] = {EntityB}.[Code] WHERE {EntityB}.[IsActive] = 1 AND ( @AmountFrom = '' OR TRY_CAST({EntityA}.[Amount] AS INT) >= @AmountFrom) AND ( @AmountTo = '' OR TRY_CAST({EntityA}.[Amount] AS INT) <= @AmountTo)
Hi @Vinod Kumar ,I will suggest - Comment out the WHERE conditions one by one to identify which condition is causing the error, then fix it accordingly.
If the issue persists, temporarily remove the JOIN and execute the query again. If it works, check the JOIN condition and the attributes in the second entity.
Ensure that the attributes used in the JOIN have compatible data types. If their data types differ, apply the appropriate conversion to avoid SQL Server conversion errors.Also see this post as well-https://www.outsystems.com/forums/discussion/26187/error-converting-data-type-nvarchar-to-bigint/https://www.outsystems.com/forums/discussion/36252/dynamic-queries-error-converting-data-type-nvarchar-to-bigint/https://www.outsystems.com/forums/discussion/68924/help-resolve-error-converting-data-type-nvarchar-to-bigint/Hope this will help you.
Hi,
I suggest checking the data types of the attributes used in your JOIN conditions and ensuring they are compatible.
Since you mentioned that it was working previously but suddenly stopped loading data, it would also be worth checking whether any recent changes to the entity attributes, data, or SQL query could have introduced a type mismatch or conversion issue.
Hi Vinod,
It would help if you would share the data types of the attributes of EntityA and EntityB and your parameters. If you want help, please show respect to the community by providing as much information as possible.
BIGINT is normally a OutSystems Long Integer and NVARCHAR is normally OutSystems Text. One thing that looks odd to me is @AmountTo = ''. If @AmountTo is a Long Integer, that looks like it could be the problem.
Separately from your issue, if you can create your data model to avoid casts in your queries, the SQL has more chance of using an index to improve performance of the query.
Hope this helps.
Kind regards,
Stuart.
Hi
I think the issue might be with @AmountFrom or @AmountTo, as they may be passed as text while being compared with a numeric value. I’ll check the parameter values and data types, along with the join columns and the Amount field, to identify where the conversion is failing. We can then fix it by handling the values and conversions properly.