c# alter table name add column

Question

Command executed successfully look at here,..

string strConn = "Data Source=USER\\SQLExpress; Initial Catalog=empdata; Integrated Security=true";
        SqlConnection conn = new SqlConnection(strConn);
        conn.Open();

        SqlCommand sqlCmd = new SqlCommand("alter table empreg ADD '"+ this.comboBox1.Text +"' varchar(50)", conn);

        int res=sqlCmd.ExecuteNonQuery();
        if (res == 1)
        { MessageBox.Show("New column created"); }
        else
        { MessageBox.Show("sorry unable to create"); }

But it goes to 2nd part of if "sorry unable to create" result and the thing is that in SQL studio we can see that column created. How is it possible ???

return of ExecuteNonQuery is number of affected rows, maybe adding column is interpreted as no changed rows — Milan Halada, Apr 28 '14 at 12:04
@ARJUN from [MSDN](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery(v=vs.110).aspx): _For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. ... For all other types of statements, the return value is -1_ — dkozl, Apr 28 '14 at 12:06
sorry, i didn't get you.. well, if no changed then it should return 0 (zero) right... — ARJUN, Apr 28 '14 at 12:08
As you can see with all these answers.. [MSDN Reference](http://msdn.microsoft.com/library) is a great resource to start when you'll have a doubt in the future, it'll save you some time.. — Andre Figueiredo, Apr 28 '14 at 12:09

score 1 · Answer 1 · answered Apr 28 '14 at 12:05

your result is the amount of affected rows, 1 doesn't mean you got an error.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery%28v=vs.110%29.aspx

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.

score 1 · Answer 2 · answered Apr 28 '14 at 12:05

1

From the MSDN page:

"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."

You need to handle res ==-1, not res ==1

answered Apr 28 '14 at 12:05

Jay

2,077
5
24
39

that was interesting, but may i know why it is -1 ? is there any reason behind this ? – ARJUN Apr 28 '14 at 12:15
I have no idea why MS decided to use -1. – Jay Apr 28 '14 at 12:17

score 1 · Answer 3 · answered Apr 28 '14 at 12:06

from http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery(v=vs.110).aspx

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.

score 0 · Answer 4 · answered Apr 28 '14 at 12:05

0

SqlCommand.ExecuteNonQuery => "Executes a Transact-SQL statement against the connection and returns the number of rows affected."

It will always return number of rows altered or not

c# alter table name add column

5 Answers5