Why this query returning -1?

Question

I'm trying to get the max id of the table category using this code

string maxid = "";
string query = "SELECT MAX(Cat_ID) + 1 FROM Category";

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["JokerCafe"].ConnectionString);

try
{
     conn.Open();
     SqlCommand cmd = new SqlCommand(query, conn);
     maxid = cmd.ExecuteNonQuery().ToString();
}
catch (Exception ex)
{
     MessageBox.Show(ex.Message);
}
finally
{
     conn.Close();
}

return maxid;

I run this query in sql it is returning exact value but when try to execute it from code it returns -1. Kindly guide me what's going wrong with it?

`ExecuteNonQuery`.. as in.. don't query, just do some SQL. If you want to return stuff.. use `ExecuteScalar`. — Simon Whitehead, May 07 '14 at 03:08
possible duplicate of [ExecuteNonQuery()](http://stackoverflow.com/questions/5349114/executenonquery) — JLRishe, May 07 '14 at 03:59

score 4 · Accepted Answer · edited May 31 '14 at 19:14

ExecuteNonQuery() will return the affected row count. For example if you are trying to execute any update statement or delete statement through ExecuteNonQuery() method then it will return the number of affected rows.

But, if you want to fetch a value from specific field then you need to try ExecuteScalar() method. It will return Object type value. Using this method you can fetch only a single value record.

object val = command.ExecuteScalar();
if (val != null)
{
   //Do your stuff here.
}

score 2 · Answer 2 · answered May 07 '14 at 03:45

ExecuteScaler is your solution

It executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

so do modify your code to

maxid = cmd.ExecuteScalar().ToString();

or

maxid = cmd.ExecuteScalar() as string; //to be safe side if return value is null

and you'll get the value expected from the query

Why this query returning -1?

2 Answers2