OData issue with stored procedures and text with spaces

fstonge · September 15, 2021, 7:19pm

Hi there,

I am facing a very troublesome issue with Radzen Beta. I am creating an application that requires me to use stored procedures to save data, and in some cases, I get an error 500 when I have a space in the text I am try to save. For example, I have a stored procedure I am calling to save the data.
As soon as I have a space in the at_note field, I get this error message:

If there is no space in the text, it saves correctly:

I know the problem is not my stored procedure, as I have tested directly in SQL Server Management Studio. I have also run the profiler, but the query never gets to the server.
I have spent 3 days trying to find a solution, but I can't even single step in visual studio, it seems to explode before getting to any code. I have the feeling this is coming from the Odata level.
Any help would be appreciated.
Thanks

fstonge · September 15, 2021, 8:06pm

Here is the full stack of the error, as seen in the developer tool's network under Chrome:

fstonge · September 15, 2021, 8:20pm

I was finally able to make this work using the function encodeURI when passing the parameter in Radzen:

fstonge · September 16, 2021, 2:58pm

Well, it works in Radzen design mode, but it doesn't work once deployed.

If anyone has any clue about this, any help is appreciated!

Thanks!

mumfie · September 16, 2021, 4:05pm

Hi @fstonge ,
Have you tried a hard refresh of the web page and is the error generated identical to the original error?

fstonge · September 16, 2021, 4:25pm

A hard refresh of the page does not resolve the issue. The issue seems to come from the way the parameters are passed to a stored procedure in odata. The parameters are sent in the URL, and there is a encode/decode problem either in Radzen or in odata. The strange thing is, if I encodeURI the parameter value, it works in Radzen or in Visual Studio, but it does not work once deployed in IIS.
Thanks

mumfie · September 16, 2021, 10:09pm

@fstonge
I just tested this locally with Radzen 2.62.2 angular, .net core 3.1, MSQL with a simplified example and it worked without issue with embedded spaces.

fstonge · September 17, 2021, 12:59pm

@mumfie,

Thank you for testing that out. I am using Radzen Beta 2.70.0-beta.0, Angular 11 and dotnet core 5.08 and MsSQL server 2019. I guess I should count myself lucky in only having this tiny issue! Our internal standard is Angular 11, so I felt I had no choice but going with Radzen Beta. I am also very new to Radzen, so still learning. I ended up going around the issue with an ugly piece of code (encodeURI, replace all % with p in Radzen, then do the reverse in MSSQL and use a DecodeURL function to the to the text). I will remove that when the issue is fixed. If it might help improve the beta version, I will try to create a tiny test project that replicates the issue. Thanks again for confirming this issue is not in the stable version!

mumfie · September 17, 2021, 3:57pm

Hi @fstonge
Glad you got it working. I just tested using Radzen Beta 2.70.0-beta.0 locally and that also works.
I also changed the textbox to a text area control, Infer Schema again and did not encounter any issues with save.

fstonge · September 17, 2021, 4:07pm

I also have stored procedures where I did not encounter the problem, but I could not find a consistent reason why some fail so badly. This might be related to the way the stored procedure is written, as it changes the way Radzen infers the functions. Or it can also be due to the type of database server, as Radzen must infer differently for MySQL than is does for MsSQL. For example, I have stored procedure returning data, but as soon as I was using temporary table, Radzen could not infer the data being referred. I ended up having a set of stored procedures without temp tables I would compile before inferring, and the another one that I would compile after the database was inferred. Because the data return was the same, it ended up working fine. Anyway, thanks again for the tests.