ExecuteNonQuery() returns -1

29 Jun 2006 1:04 AM

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

29 Jun 2006 3:15 AM

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

<gall***@gmail.com> wrote in message
Show quote

news:1151543097.504639.321760@y41g2000cwy.googlegroups.com...
>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
>

29 Jun 2006 4:44 AM

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.

29 Jun 2006 8:08 PM

sirfunusa

You can still use a return or outpub parameter to get the rows affected

gall***@gmail.com wrote:
Show quote

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