C# MySQL sum InvalidCastException

Question

I have a query where I try to fetch the sum value but I get the InvalidCastException.

My query is:

SELECT e.clockNr,e.firstName,e.LastName,e.unionName,i.points 
FROM ( 
    SELECT employee.clockNr AS clockNr,
           employee.firstName AS firstName,
           employee.lastName AS lastName,
           Unions.name AS unionName 
           FROM employee,Unions 
           WHERE employee.active=1 AND employee.unionId = unions.id  
           GROUP BY employee.clockNr 
     ) e LEFT JOIN (
           SELECT infraction.clockNr AS clockNr, 
           CAST(SUM(Infraction.points) AS SIGNED) AS points 
           FROM infraction 
           WHERE infraction.infractionDate >=@startDate 
           AND infraction.infractionDate <=@endDate 
           GROUP BY infraction.clockNr 
     ) i ON e.clockNr = i.clockNr 
ORDER BY e.clockNr ASC

It is the 'points' column where it goes wrong. I have added the CAST to SIGNED but this is not helping.

The way I read out the column is:

int iGetPoints = Convert.ToInt32(reportReader["points"]);

Also tried:

int iGetPoints = (int)reportReader["points"];

But both raise the InvalidCastException. The query is tested in PHPMyAdmin and working fine there.

Can anyone see what I am doing wrong or give me a hint where to look for?

Answer 1

Because the points column is part of the left join, it can be null. I'm assuming that's the problem here. You'll need to test for null to avoid the cast exception:

// Note: this is for DataTableReader; see below for MySQL data reader
int iGetPoints = 0;
if (!reportReader.IsDBNull(reportReader.DBOrdinal("points"))) {
   iGetPoints = Convert.ToInt32(reportReader["points"]);
}

The IsDBNull method requires the index of the column name (it won't work with the name), hence the call to DBOrdinal to get the index from the name.

---

Note: the answer above will work for the "generic" System.Data.DataTableReader class, but not for the MySQL data reader. Gerard posted the changes needed for the MySQL reader in the comments below. They are:

int iGetPoints = 0;
if (reportReader["points"] != DBNull.Value) {
   iGetPoints = Convert.ToInt32(reportReader["points"]);
}