"Value cannot be null" error in query with aggregate count

Hello,

I would like to ask if this is a normal behavior.

I have a database paged query as follows:
image

All is fine on my page except if I set a filter that does not exist, e,g, search for a name that did not match any records. This will return an Error 500 with the error:
message: "Value cannot be null. ↵Parameter name: o"

And it still shows the results of the previous query on the page (maybe because it did not returned back anything yet to the server page?) instead of showing nothing on the BS table.

I am thinking that because there is no match, then there is no value for p.id and it cannot do the join because of this, hence the error “Value cannot be null”.

What I did to test it out is to create a view table and use it instead for the join. The view table itself has the count aggregate. It works when I do this. Even if it returned no results, it still returns response 200. Thus, the table on my page does not show any records which is good since there are supposed to be no results.

Is this a normal behavior? Has anyone encountered the same or has not experienced the issue?

Thank you.

Is it possible that you have a position that has no corresponding payrate_history entry? If so, the left join will give results from the position table that are then null in the prh table and you are using a count on the prh table.

If you simply want to ignore those that provide a null prh record, you could put a WHERE condition that prh.id IS NOT NULL

But COUNT will return an integer right? If it is Null then it should return a 0 instead?

You’re right - it should but thought that it may be a peculiarity. Looking more closely at the console message you posted, it looks more like a parameter within the db connector, probably the offset which may get assigned to a ‘o’, that is struggling for some reason. Nothing to do with your DB. When no records return it doesn’t have any offset to work with to determine paging start points.

More to do with the connection scripts so most likely a job for the Wappler team. Sorry I can’t help more.

Thanks for looking into this @bpj. For the meantime, I am using the view in the LEFT JOIN. Maybe someone has encountered the same and has a solution, else, I assume this is a bug.

Hi @Teodor, maybe you can also have this investigated by the team?
It seems that if there are JOINS in my queries and I have an aggregate using COUNT, if there are no results in the other table, it will return the error “Value cannot be null”. Seems there is an issue during the JOIN?
I am already using a LEFT JOIN. My current workaround is to use table views created by the same queries.

Are you sure your filter parameter is always supplied? Maybe it is empty sometime?

Hello @George,
It is a simple query with no filters, like this one:

This doesn’t work:

So I used table view instead:

The table view used is below:

image

You can see that the table view is the same query as the one that is returning the error.

It is not completely clear to me when you are getting the error when you use count or not?

Can you provide an isolated test case? So @patrick can investigate

Hi @George, I am using COUNT. The table has no records yet. So it should return nothing.

USING NORMAL TABLE:
Here’s the query using the normal table:

Here’s the error when using the normal table:

USING VIEW TABLE:
Here is the query using view table:
image

Here’s the output when using view table:
image

They are basically using the same SELECT query with COUNT. But when I am using the view table, there is no “Value cannot be null” error.
Here’s the query used to create the view table:

I am only getting the error when using COUNT with the normal table.

Hope this is clear.

could you post the StackTraceString in the orgException, that should help me debug the error

Hi @patrick,

"StackTraceString": "   at Newtonsoft.Json.Linq.JToken.FromObjectInternal(Object o, JsonSerializer jsonSerializer)\r\n   at DMXzone.ServerConnect.Database.Connector.Execute(SqlBuilder sql) in d:\\Develop\\DMXzone Server Connect\\ASPNET_Source\\DMXzone Database Connector\\Database\\Connector.cs:line 154\r\n   at DMXzone.ServerConnect.Module.DbConnectorModule.Paged(JObject options) in d:\\Develop\\DMXzone Server Connect\\ASPNET_Source\\DMXzone Database Connector\\Module\\DbConnectorModule.cs:line 103",

Thank you.

I updated the DatabaseConnector, below is the new DLL. Replace the file in your Bin folder and upload it to your server.

DatabaseConnector.zip (28.2 KB)

Let me know if it works or not.

Hi @patrick, it is working now!!!

image

Thanks for this fix!

Sorry, but I checked the other query that I have (the first one reported on this post).
The Total rows returned is 80, but when I checked directly in the database, it should be 74.
image

I am wondering, since I have a LIMIT of 10, and the query was “divided” into 8 offsets, is it automatically assuming that I have a total of 80 rows?

image

From the screenshot above, it should really be only 74. i.e. 10x7 + 4 = 74.

Here’s my query:
image

For paged queries there are 2 queries executed on the server, first it a query for counting the total records and then the actual limited query. The count query was returning NULL, that was the previous error and I added some extra checks and return 0 when count didn’t work. With this new update I have updated the count query and hope that it returns the correct records count now.

DatabaseConnector.zip (28.2 KB)

Hello @patrick, I think there is something wrong with the fix.
I have tried via Open in browser and this is the error:
image

Looks like all my paged queries are returning the error Incorrect syntax near ')'.

Here’s the stacktrace just in case:

"StackTraceString": "   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\r\n   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\r\n   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)\r\n   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()\r\n   at System.Data.SqlClient.SqlDataReader.get_MetaData()\r\n   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)\r\n   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)\r\n   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)\r\n   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)\r\n   at System.Data.SqlClient.SqlCommand.ExecuteScalar()\r\n   at DMXzone.ServerConnect.Database.Connector.Execute(SqlBuilder sql) in d:\\Develop\\DMXzone Server Connect\\ASPNET_Source\\DMXzone Database Connector\\Database\\Connector.cs:line 156\r\n   at DMXzone.ServerConnect.Module.DbConnectorModule.Paged(JObject options) in d:\\Develop\\DMXzone Server Connect\\ASPNET_Source\\DMXzone Database Connector\\Module\\DbConnectorModule.cs:line 103"

I’ll revert to the previous files for the meantime while this is being fixed.

New try, there was a typo in the code but the compiler didn’t throw any errors for it.

DatabaseConnector.zip (28.2 KB)