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
– Nalaka526

2013-05-07 03:48:42 +00:00
Commented May 7, 2013 at 3:48 — Nalaka526
– Nalaka526, Commented May 7, 2013 at 3:48
@nalaka it returns 2 columns. NIK and Password

Cignitor
– Cignitor

2013-05-07 10:49:06 +00:00
Commented May 7, 2013 at 10:49 — Cignitor
– Cignitor, Commented May 7, 2013 at 10:49

Community · Accepted Answer · 2017-05-23 11:57:19Z

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, 2017 at 11:57

CommunityBot

11 silver badge

answered May 7, 2013 at 3:52

Damith

63.2k13 gold badges106 silver badges154 bronze badges

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

2 Comments

Cignitor Over a year ago

executescalar return error, because cannot implicitly convert object to int

Damith Over a year ago

yes, you need to convert it to int value. executescalar return object value

Adrian · Accepted Answer · 2013-05-07 03:53:23Z

0

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.

answered May 7, 2013 at 3:53

Adrian

2,8751 gold badge25 silver badges33 bronze badges

Collectives™ on Stack Overflow

Different mysql Count return value

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related