Getting count of rows from MySQL database in C#

Question

I want to count the number of rows in a MySql database and return the result in a label.

This is my code:

string connection = "SERVER= ###; PORT=###; DATABASE=###; UID=###; PASSWORD=###; CharSet=utf8;";

MySqlConnection mySqlCon = new MySqlConnection(connection);
MySqlCommand mySqlCmd = mySqlCon.CreateCommand();


mySqlCmd.CommandText = "Select count(*) as myCount from ###.###";
mySqlCon.Open();

int returnValue = (int) mySqlCmd.ExecuteScalar();

lblCLientNumber.Text = returnValue.ToString();

The error I get is: Specified cast is not valid.

But when I run the SQL command in phpMyAdmin the result is accurate.

I'm not sure where am I making a mistake.

What is the output of Console.WriteLine(typeof(mySqlCmd.ExecuteScalar()))? — Tripp Kinetics
– Tripp Kinetics, Commented Apr 6, 2018 at 16:19

Ollie Beumkes · Accepted Answer · 2018-04-06 16:22:08Z

2

Try using

returnValue = int.Parse(mySqlCmd.ExecuteScalar().ToString());

C# Is asking for an integer;

Take a look here Getting MySQL record count with C# this question has been asked before

answered Apr 6, 2018 at 16:22

Ollie Beumkes

2992 silver badges17 bronze badges

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

Comments

Shai Aharoni · Accepted Answer · 2018-04-06 16:19:52Z

1

You are getting this error because ExecuteScalar function returns an object.

You could do the following:

int returnValue = int.Parse(mySqlCmd.ExecuteScalar().toString());

answered Apr 6, 2018 at 16:19

Shai Aharoni

1,9671 gold badge13 silver badges26 bronze badges

Comments

neildt · Accepted Answer · 2018-04-06 16:28:51Z

1

Try

int returnValue = Convert.ToInt32(mySqlCmd.ExecuteScalar());

However, my preference would be to reference the field. For example something like

using(MySqlCommand cmd = new MySqlCommand("Select count(*) as myCount from 
###.###", connection))
{
    using(MySqlDataReader rdr = cmd.ExecuteReader())
    {
        if(rdr.Read()) 
        {
            returnValue = Convert.ToInt32(rdr["myCount"]);
        }
    }
    rdr.Close();
}

edited Apr 6, 2018 at 16:28

answered Apr 6, 2018 at 16:20

neildt

5,40311 gold badges61 silver badges112 bronze badges

Collectives™ on Stack Overflow

Getting count of rows from MySQL database in C#

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related