Stored Procedure with nullable params

Hi Guys,

I'm trying to make my SQL Server Stored Procedure work with the datagrid. It has many nullable parameters ( = @param or @param is null), and looking at the post here it should be okay. But I get the error on run (error CS7036: There is no argument given that corresponds to the required formal parameter 'param' of 'ItInventoryDataService.GetSpEquipments(string, bool?, int?, int?, int?, int?, int?, string, string, string, string, string, string, string, string, string)' )

I've tried to add null to each parameter on the Invoke data source method using null, ${null}, etc. and with this it does run, but the grid is empty.

Any thoughts?

Maybe your stored procedure does not return any items. Did you check that? Also did you check if there are any exception in Radzen's output window?

Yes, the stored procedure runs successfully from within SSMS. The only exception was the error CS7036 that I wrote about in the original post.

You said it runs without error but the grid is empty. You can use the debugger to inspect what the stored procedure returned.

@korchev hello,

This should be a pretty easy one for you. In the forum post I linked to when opening this case, I was under the impression that Radzen recognized nullable parameters in stored procedures - obviously I must be confused. I've tried debugging, but the process seems to halt before returning anything of value - probably not doing that correctly either.

So, instead I'm sending the stored procedure the parameters as null to keep Radzen happy. Looks like this:

This is what Radzen puts in the designer.cs for the SP:

    protected async System.Threading.Tasks.Task Load()
        var itInventoryDataGetSpEquipmentsResult = await ItInventoryData.GetSpEquipments($"null", null, null, null, etc.........);
        getSpEquipmentsResult = itInventoryDataGetSpEquipmentsResult;

The problem seems to be that for strings and dates Radzen inserts $"null" instead of null. Bools and integers are fine and expressed correctly.

If I manually change the $"null" params to null in the designer.cs with Visual Studio and run it, then the app runs correctly.

Some silly little thing I'm doing incorrectly, I'm sure.


Indeed Radzen treats string and date parameters in a special manner and add quotes (to spare the user the need to do that). We should handle null as a special value and ignore it.

As a workaround I can suggest creating a page property for those parameters and setting it to null. Then use the page property as the stored procedure parameter value e.g. ${endUserName}. Another thing you can try is ${null} - this could fool the current implementation and prevent it from adding quotes.

1 Like

Thanks Dude, I'll try it...

@korchev, well none of those solutions worked, they all produce empty strings, not nulls.

Parameter as ${null} produced $"{null}" in the designer.cs, which results in an empty string being sent to the SP.

Setting a page property to null produced a $"{propertyname}" in the designer.cs and results in an empty string being sent to the SP.

Event setting a page property to ${null} and then using the property as the SP parameter results in an empty string.

Any thoughts? Thanks,

That is correct. The only solution until we fix that issue is to use the code generation ignore list or create a custom method which invokes the stored procedure with the right parameters.

    public partial class HomeComponent
      public async Task<IQueryable<Models.Northwind.SalesByCategory>> GetSalesByCategory(string categoryName)
        return await NorthwindService.GetSalesByCategory(categoryName);

Okay, thank you. I hope this fix makes it into a build soon. I rely heavily on this stuff to help compensate for my poor Entity Framework skills.