cmd.ExecuteNonQuery value of i integer is going -1 from 0

Question

Here i create table in database dynamically. User enters name as his wish and selects language radiobutton. So problem is after executing cmd.ExecuteNonQuery value of i integer is going -1 from 0. And shows that table couldnt be created but when i go to database its already created successfully. Please let me know where i am doing wrong. Thanx in Advance !!

protected void btnpaper_Click(object sender, EventArgs e)
    {
        try
        {                
                string conn = ConfigurationManager.ConnectionStrings["sqlconn"].ConnectionString;
                SqlConnection con = new SqlConnection(conn);
                con.Open();
                char[] arr = new char[] {'n','g','l','i','s','h'};
                string str = "CREATE TABLE " + Label1.Text.Trim() + 
                             txtpaperset.Text.Trim()+ rbtnEng.Text.TrimEnd(arr) +
                             "(" + "quesNo int NOT NULL PRIMARY KEY, " + 
                             "question varchar(1000) NOT NULL," + 
                             "ansA varchar(500) NOT NULL, " + 
                             "ansB varchar(500) NOT NULL, " + 
                             "ansC varchar(500) NOT NULL, " + 
                             "ansD varchar(500) NOT NULL, " + 
                             "rightAns varchar(50) NOT NULL " + ")";                    
                SqlCommand cmd = new SqlCommand(str, con);
                int i = cmd.ExecuteNonQuery();
                if (i > 0)
                {
                    lblerrormsg.Visible = true;
                    con.Close();
                }
                else
                {
                    lblerrormsg.Text = "Table Not Created Please Try with Different Name!";                        
                    con.Close();
                }                

        }
        catch (System.Exception excep)
        {
            MessageBox.Show(excep.Message);
        }      
    }

Is this an invititation for SQL-Injection? Use SQL-Parameters! Apart from that, always close a connection when you're finished with it, best by using the `using-statement`: `using(var con=new SqlConnection(conn)){con.Open(); //...}` (it'll close the connection implicitely, even in case of an exception). — Tim Schmelter, Jul 23 '12 at 20:22
@TimSchmelter: I'm not sure parameterized SQL works for DDL... — Jon Skeet, Jul 23 '12 at 20:24

score 9 · Accepted Answer · answered Jul 23 '12 at 20:24

Takes from MSDN Remarks on SqlCommand.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.

score 7 · Answer 2 · answered Jul 23 '12 at 20:24

From the docs for SqlCommand.ExecuteNonQuery (emphasis mine):

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.

Is your statement an UPDATE, INSERT or DELETE statement? Nope. Hence you're getting -1.

It's not clear what you mean by the value of i going to -1 "from" 0. It's never 0. It doesn't have a value until it's assigned one from the result of ExecuteNonQuery.

I want user to get to know if table creation done succefully or not. Hence i had used i integer. Please help with if i can use another way. — Rushikesh Korgaonkar, Jul 23 '12 at 20:33
@rickevans: I expect that if the table creation fails, you'll actually get an exception. Give it a try - see what happens if you try to recreate a table which already exists. — Jon Skeet, Jul 23 '12 at 22:56

score 0 · Answer 3 · edited May 23 '17 at 10:29

0

Is it by design?

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx

and also:

cmd.executenonquery is returning -1 in vb.net windows application

You are saying you're getting -1, correct?

edit: to answer the last part of your question, see this:

Check if table exists in SQL Server

edited May 23 '17 at 10:29

Community

1
1

answered Jul 23 '12 at 20:27

johnny

19,272
52
157
259

I m getting -1. all i want to do is to notify user that table creation has done successfully. – Rushikesh Korgaonkar Jul 23 '12 at 20:37
create an additional query to verify it. – johnny Jul 23 '12 at 21:06
1

I'm saying that once you do your create, you get your -1, then you issue your new query to see if the table is actually there. Make sense? Your just making a second query to see if the first one worked. – johnny Jul 23 '12 at 21:27

score -2 · Answer 4 · answered Apr 01 '13 at 02:40

Public Shared Function Insert(ByVal SProcedure As String, ByVal parameters() As SqlParameter) As Boolean Using cnn As New SqlConnection(Emplooyes) Try

            Dim cmd As SqlCommand = New SqlCommand(SProcedure, cnn)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddRange(parameters)

            If cnn.State = ConnectionState.Closed Then
                cnn.Open()
            End If

            Dim Ls As Integer
            Ls= cmd.ExecuteNonQuery()
            If Ls = -1 Then
                Return False
            Else
                Return True
            End If

        Catch ex As Exception
            Return False
        Finally
            cnn.Close()
        End Try
    End Using
End Function

Welcome to Stack Overflow! Would you consider adding some narrative to explain why this code works, and what makes it an answer to the question? This would be very helpful to the person asking the question, and anyone else who comes along. — Andrew Barber, Apr 07 '13 at 07:22

cmd.ExecuteNonQuery value of i integer is going -1 from 0

4 Answers4

Linked