CODEWITHSUNDEEP
vb.net
Amruta
Amruta

Reputation: 731

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

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.

Upvotes: 0

Views: 6079

Answers (2)

Qua285
Qua285

Reputation: 307

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

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

Upvotes: 0

Saif Khan
Saif Khan

Reputation: 18812

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

Upvotes: 1

Related Questions