CODEWITHSUNDEEP
c#sql
Nikita Bhalla
Nikita Bhalla

Reputation: 25

Executenonquery have -1 when i execute a statement

I am trying to fetch rows from a table and insert them into another table. For that I have written this simple code but I am not able to figure out what is the issue.

As you can see I have used ExecuteNonQuery after executing my statement and then used messagebox to see the output but the output in message box is -1 

for (int i = 1; i <= 10; i++)
{
    SqlCommand cmd = new SqlCommand("Select exp_date from tbl_expences where exp_id='" + i + "'", con);

    int j = cmd.ExecuteNonQuery();

    MessageBox.Show(Convert.ToString(j));

    if (j > 1)
    {
       string date = cmd.ExecuteScalar().ToString();

       MessageBox.Show(date);
       SqlCommand cmd1 = new SqlCommand("update other_expensive set exp_date='" + date + "' where exp_id='" + i + "'", con);
    }
}

Upvotes: 0

Views: 605

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239784

Why would you run 20 (possibly 30, depending on what else happens in this code) statements for this?

From the documentation of ExecuteNonQuery:

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.

(Emphasis added)

Why not just:

update oe set exp_date=e.exp_date
from
    other_expensive oe
       inner join
    tbl_expense e
       on
          oe.exp_id = e.exp_id
where
    e.exp_id between 1 and 10

Which will do all of the updates in parallel, using one query, and avoid the loop, and the "step-by-step" approach you seem to be trying to use.

Upvotes: 0

Femaref
Femaref

Reputation: 61477

ExecuteNonQuery() is used for INSERT, UPDATE or DELETE. Use ExecuteReader() if you want to use SELECT.

Upvotes: 2

Related Questions