Query is always returning -1 in C#

Question

I am using following code to check if record exist.

SqlCommand check_Campaign_Name = new SqlCommand("SELECT * FROM Campaign_Summary  WHERE ([Compaign_Name] = @user) ", conn);
check_Campaign_Name.Parameters.AddWithValue("@user", txtBox_LastClick_Campaign.Text);

int CampaignExist = check_Campaign_Name.ExecuteNonQuery();

richTextBox1.Text = CampaignExist.ToString();

But I am always getting -1 in the integer CampaignExist. Don't know where I am doing wrong.

Answer 1

ExecuteNonQuery is not supposed to return the number of rows SELECTED, but the number of rows modified by an INSERT/UPDATE/DELETE command. You should use a SqlDataReader and check with its property HasRows or use an aggregate function like COUNT and ExecuteScalar
(Probably the best choiche if you want to just retrieve the number of rows)

SqlCommand check_Campaign_Name = new SqlCommand("SELECT COUNT(*) FROM Campaign_Summary " + 
                                 "WHERE Compaign_Name = @user", conn);
check_Campaign_Name.Parameters.AddWithValue("@user", txtBox_LastClick_Campaign.Text);
int rowCount = Convert.ToInt32(check_Campaign_Name.ExecuteScalar());

However, if you want only to know if the row exists or not, then the COUNT approach is considered less efficient than using the EXISTS statement.

string cmdText = @"IF EXISTS (SELECT Compaign_Name FROM Campaign_Summary 
                   WHERE Compaign_Name = @user)
                   SELECT 1 ELSE SELECT O";
SqlCommand check_Campaign_Name = new SqlCommand(cmdText, conn);
int rowExists = Convert.ToInt32(check_Campaign_Name.ExecuteScalar());

This second approach just allows to know if there are rows that fits the WHERE statement or not.
So it is not exactly like COUNT(*) where you get the exact number of rows.

Answer 2

Use executescalar method and count on query

SqlCommand check_Campaign_Name = new SqlCommand("SELECT COUNT(1) FROM Campaign_Summary  WHERE ([Compaign_Name] = @user) ", conn);
check_Campaign_Name.Parameters.AddWithValue("@user", txtBox_LastClick_Campaign.Text);

int CampaignExist = Convert.ToInt32(check_Campaign_Name.ExecuteScalar());

richTextBox1.Text = CampaignExist.ToString();

Answer 3

From MSDN,

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

As your query is a SELECT and not one of the mentioned UPDATE, INSERT or DELETEs, it will always return -1.

Answer 4

Try with Execute scalar MSDN

int CampaignExist = Convert.ToInt(check_Campaign_Name.ExecuteScalar());

---

SqlCommand check_Campaign_Name = new SqlCommand("SELECT COUNT(1) FROM Campaign_Summary  WHERE ([Compaign_Name] = @user) ", conn);
check_Campaign_Name.Parameters.AddWithValue("@user", txtBox_LastClick_Campaign.Text);

int CampaignExist = Convert.ToInt32(check_Campaign_Name.ExecuteScalar());

richTextBox1.Text = CampaignExist.ToString();

Answer 5

ExecuteNonQuery is not used to retrieve results

You can use one of the following methods:

1. SqlCommand.ExecuteScalar (I would prefer this)

2. DataAdapter.Fill