ExecuteNonQuery() returns -1

[gallian]

I am trying to insert new records in table via stored proc
EMP_Calendar_SAVE_sp. There is no exception thorwn but RowsAffected is
-1. I have tested the store proc, it works fine. Any Help?



Code as below:=
Public Sub EMP_Calendar_SAVE_sp(ByVal EmployeeCode As String, ByVal
CalendarType As String, ByVal StartDate As Date, ByVal EndDate As Date)
Dim SelectCommandText As String = "EMP_Calendar_SAVE_sp"
Dim MySqlCommand As New SqlCommand(cmdText:=SelectCommandText,
connection:=MyBase.m_Connection, transaction:=MyBase.m_Transaction)
MySqlCommand.CommandType = CommandType.StoredProcedure

MySqlCommand.Parameters.Add(New SqlParameter("@EmployeeCode",
EmployeeCode))
MySqlCommand.Parameters.Add(New SqlParameter("@CalendarType",
CalendarType))
MySqlCommand.Parameters.Add(New SqlParameter("@StartDate",
StartDate))
MySqlCommand.Parameters.Add(New SqlParameter("@EndDate",
EndDate))

Dim RowsAffected As Integer
Try
RowsAffected = MySqlCommand.ExecuteNonQuery()
Catch ex As Exception
Dim Message As String = "Error calling stored procedure '"
+ SelectCommandText + "'" + Environment.NewLine _
+ "EmployeeCode='" + EmployeeCode +
"'" + Environment.NewLine _
+ "CalenderType='" + CalendarType +
"'" + Environment.NewLine _
+ "StartDate='" + StartDate + "'" +
Environment.NewLine _
+ "EndDate='" + EndDate
Throw New Exception(Message)
End Try
End Sub

 

[Brendan Green]

According to the MSDN documentation, ExecuteNonQuery() only returns the rows
affected for INSERT, DELETE and UPDATE statements. Any other statement
(presumably including calls to a stored proc) will return -1.

Quote:
"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. If a rollback occurs, the return value is also -1."

 

[gallian]

Thanks for reply.

This does imply that even if calling store procedure is doing INSERT,
it will still return -1, because Insert is not done explicitly.