Different mysql Count return value

Question

i have the following query

SELECT count( * ) COLUMN_NAME FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'ptbs.ms_karyawan'

and execute them on mysql wamp, it returns 2 columns

but then, when i execute the query above using C#, it returns -1 columns, here is the code

  private void button1_Click(object sender, EventArgs e)
    {
        int table = dbc.Count("ptbs.ms_karyawan");
        msg.Sukses("Jumlah table adalah :"+table, "Login");
    }

and here is the dbc.count() code

  public int Count(string table_name)
    {
        string query = "SELECT count( * ) COLUMN_NAME FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = '"+table_name+"'";
        int table = 0;

        if (OpenConnection() == true)
        {
            //create mysql command
            MySqlCommand cmd = new MySqlCommand();

            //Assign the query using CommandText
            cmd.CommandText = query;

            //Assign the connection using Connection
            cmd.Connection = connection;

            //Execute query
            table = cmd.ExecuteNonQuery();

            //close connection
            this.CloseConnection();                             
        }
        return table;
    }

why did they return different value, though i'm using the same query ? what did i do wrong ?

".. it returns 2 columns", Columns or Rows? – Nalaka526 May 07 '13 at 03:48 — Nalaka526, May 07 '13 at 03:48
@nalaka it returns 2 columns. NIK and Password – Cignitor May 07 '13 at 10:49 — Cignitor, May 07 '13 at 10:49

score 4 · Accepted Answer · edited May 23 '17 at 11:57

4

you need to execute Executescalar method

var retVal = cmd.Executescalar(); 
table = Convert.ToInt32(retVal);

ExecuteNonQuery return only affected row count.

But Executescalar returns first column of the first row in the result set returned by the query

if you need more information about casting value which return from ExcureScalar please check below SO answers

Int32.TryParse() or (int?)command.ExecuteScalar()

edited May 23 '17 at 11:57

Community

1
1

answered May 07 '13 at 03:52

Damith

62,401
13
102
153

executescalar return error, because cannot implicitly convert object to int – Cignitor May 07 '13 at 10:47
yes, you need to convert it to int value. executescalar return object value – Damith May 07 '13 at 10:52

score 0 · Answer 2 · answered May 07 '13 at 03:53

You're using MySQLClient.ExecuteNonQuery to execute a query ExecuteNonQuery is used for running things like INSERT or UPDATE statements, not SELECT. Because of this you're not affecting any rows, and getting a result of -1. You need to execute the query in such a way that you get a result set back that you can read the value of your count ( * ) COLUMN_NAME from.

Different mysql Count return value

2 Answers2