Casting mysql reader result to string throws an exception in C#

Question

My function

public Permission[] GetPermissionsForGroupID(string groupID)
    {
        MySqlCommand command = new MySqlCommand();
        command.Connection = connection;
        command.CommandText = "SELECT `permissions`.`permissionName`, `groups_permissions`.`permissionValue` 
        FROM `db`.`groups_permissions`, `db`.`permissions` 
        WHERE `permissions`.`permissionID` = `groups_permissions`.`permissionID` 
        AND `groups_permissions`.`groupID` = '" + groupID + "'";
        MySqlDataReader reader = command.ExecuteReader();
        List<Permission> permissions = new List<Permission>();
        while (reader.Read())
        {
            permissions.Add(new Permission((string)reader[0], (int)reader[1]));
        }
        reader.Close();
        return permissions.ToArray();
    }

throws an System.InvalidCastException at

permissions.Add(new Permission((string)reader[0], (int)reader[1]));

I narrowed it down to (string)reader[0] where a varchar should be casted into a string.

reader[0] = permissions.permissionName = varchar

What could be the problem?

EDIT:

The value of reader[0] is a string "kick_user_power" and the value of reader[1] is an integer "75".

EDIT 2:

The constructor of Permission

public Permission(string permissionName, int permissionPower)
    {
        PermissionName = permissionName;
        PermissionPower = permissionPower;
    }

And, for better visualization, the values of reader[0] and reader[1]:

What is the value of reader[0], and, for that matter, of reader[1] ? — Hugh Jones
– Hugh Jones, Commented Mar 30, 2016 at 10:59
and the constructor for the Permission class - how does that look? — Hugh Jones
– Hugh Jones, Commented Mar 30, 2016 at 11:04

O. Jones · Accepted Answer · 2016-03-30 11:18:37Z

3

The dotnet SqlDataReader class includes functions for retrieving column data.

You probably want to use

   reader.GetString(0)

and

   reader.GetSqlInt32(1)

to retrieve your values.

In fact for best results you should use

  reader.IsDbNull(0)? "missing value" : reader.GetString(0)

answered Mar 30, 2016 at 11:18

O. Jones

109k17 gold badges134 silver badges187 bronze badges

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

1 Comment

Marco Frost Over a year ago

Nice idea. I will definetly refactor my code to use this approach. It worked perfectly fine. Thank you!

Remy · Accepted Answer · 2016-03-30 11:06:35Z

0

Could it be that it's not the cast to string that is the issue, but the cast to int?
Try

permissions.Add(new Permission(reader[0].ToString(), Convert.ToInt64(reader[1])));

Both ToString() and Convert.ToInt64 are a bit less "sensitive".

answered Mar 30, 2016 at 11:06

Remy

12.8k14 gold badges66 silver badges106 bronze badges

1 Comment

Marco Frost Over a year ago

Nope. It didn't work. I made sure it is reader[0] by separately assigning its values to variables. (I didn't show that in the question but mentioned it by saying that I narrowed it down to reader[0].)

Collectives™ on Stack Overflow

Casting mysql reader result to string throws an exception in C#

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related