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?

It is an INT. I just tried to parse it as string and then convert to an int and that is working for some reason. — Gerard van den Bosch
– Gerard van den Bosch, Commented Aug 8, 2013 at 17:50

Ed Gibbs · Accepted Answer · 2013-08-08 18:18:58Z

2

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"]);
}

edited Aug 8, 2013 at 18:18

answered Aug 8, 2013 at 17:54

Ed Gibbs

26.4k4 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Gerard van den Bosch Over a year ago

That is a good points, only the MySQL datareader is not familiar with DBOrdinal?

Gerard van den Bosch Over a year ago

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

Ed Gibbs Over a year ago

Thanks Gerard, and good catch! I've updated my answer to include your code for the MySQL data reader; I think a lot of folks will benefit from it :)

Collectives™ on Stack Overflow

C# MySQL sum InvalidCastException

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related