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

Question

I am just inserting a record in sqlserver database using stored procedure. In this code Binddata() is the method where i have binbed the gridview with records.

here is the code:

Private Sub btnadd_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles btnadd.Click

    sqlSP = "INSERT_E_CLIENTMASTER_REC"
    strConnection = _
        ConfigurationManager.ConnectionStrings("connectstring").ConnectionString
    Dim conn As New SqlConnection(strConnection)

    conn.Open()

    Dim cmd As New SqlCommand(sqlSP, conn)

    cmd.CommandType = CommandType.StoredProcedure

    cmd.Parameters.Add(
        New SqlParameter("@CLIENTCODE", SqlDbType.VarChar, 100, _
            ParameterDirection.Input, False, 0, 0, "", _
            DataRowVersion.Proposed, txtclientcode.Text))
    'cmd.Parameters.Add( _
    '    New SqlParameter("@CLIENT_WEBXID", SqlDbType.NVarChar, _
    '        Convert.ToString(txtwebxid.Text))) _
    cmd.Parameters.Add( _
        New SqlParameter("@CLIENT_WEBXID", SqlDbType.VarChar, 100, _
            ParameterDirection.Input, False, 0, 0, "", _
            DataRowVersion.Proposed, txtwebxid.Text))

    cmd.Parameters.Add( _
        New SqlParameter("@ToEmail", SqlDbType.VarChar, 100, _
            ParameterDirection.Input, False, 0, 0, "", _
            DataRowVersion.Proposed, txttoemail.Text))

    cmd.Parameters.Add( _
        New SqlParameter("@ClientName", SqlDbType.VarChar, 100, _
        ParameterDirection.Input, _
        False, 0, 0, "", DataRowVersion.Proposed, txtclientname.Text))

    cmd.Parameters.Add(_
        New SqlParameter("@CcEmail", SqlDbType.VarChar, 100, _
        ParameterDirection.Input, False, 0, 0, "", _
        DataRowVersion.Proposed, txtccname.Text))

    Dim i As Int32 = cmd.ExecuteNonQuery()
    If (i < 0) Then
        BindData()
        MsgBox("Record Inserted successfully ", MsgBoxStyle.OkOnly)
        txtclientcode.Text = ""
        txtwebxid.Text = ""
        txttoemail.Text = ""
        txtclientname.Text = ""
        txtccname.Text = ""

    Else
        MsgBox("Record Not Inserted successfully ", MsgBoxStyle.OkOnly)
    End If

End Sub

In this code cmd.ExecuteNonQuery() is returning -1 so i have given (i<0) condition actually it should be positive value when rows are affected.

Thanks in advance.

score 1 · Answer 1 · edited May 23 '17 at 10:34

1

Is the record saved to the database? If so, then that's because the stored procedure is not returning a value.

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.

If you need to return a value from the stored procedure, look at this StackOverFlow Question

On a side note - Look into using the "USING Block" for your connections

edited May 23 '17 at 10:34

Community

1
1

answered Jun 02 '11 at 07:00

Saif Khan

18,402
29
102
147

Hello. I am not returning any value from stored procedure.but ExecuteNonQuery()method returns number of rows affected which is a positive number. But I am getting -1 value that is wrong, so is my code correct. Thanks for answering – Amruta Jun 02 '11 at 10:11
The ExecuteNonQuery() will return rows affected when performing an INSERT, UPDATE or DELETE. The stored procedure you have is not returning a value that's why you are geting -1 as the return value. – Saif Khan Jun 02 '11 at 22:12
I have written stored procedure for INSERT, UPDATE or DELETE. Records are getting inserted/updated/deleted, but if i give if(i>0) condition then it enters the else block not the if block b'coz i value on debugging is -1.I don't know whats going wrong. – Amruta Jun 03 '11 at 07:00

score 0 · Answer 2 · answered Dec 21 '14 at 15:52

0

Remove the line "SET NOCOUNT ON;" from your SProc.

SET NOCOUNT (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms189837.aspx

answered Dec 21 '14 at 15:52

Qua285

137
1
3
12

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

2 Answers2

Linked