I can not return the rows affected just using

I can not return the rows affected just using
int a = myCommand.ExecuteNonQuery(); it always return -1
WHY?The documentation says:
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.
Is your NonQuery a single UPDATE, INSERT, or DELETE statement? If it's
anything more than that (a batch with several statements, for example),
the docs say you'll get -1.
Steve Kass
Drew University
Kenny M. wrote:

>I can not return the rows affected just using
>int a = myCommand.ExecuteNonQuery(); it always return -1
>WHY?
>
>|||Yeap it is an update query with 3 conditions
ALTER PROCEDURE dbo.UpdateTicketStatus
@.NroTicket VARCHAR(12) = NULL,
@.IDAgencia VARCHAR(12) = NULL,
AS
SET NOCOUNT ON
DECLARE @.Sqlstring VARCHAR(3000)
SELECT @.Sqlstring = 'UPDATE Ventas SET Status = ''A'''
SELECT @.Sqlstring = @.Sqlstring + ' WHERE DateDiff(n, FechaHora, GetDate())
<= 5'
SELECT @.Sqlstring = @.Sqlstring + ' AND NroTicket = ''' +
CONVERT(VARCHAR,@.NroTicket) + ''''
SELECT @.Sqlstring = @.Sqlstring + ' AND ID = ''' +
CONVERT(VARCHAR,@.IDAgencia) + ''''
EXECUTE (@.Sqlstring)|||You could try adding RETURN(@.@.ROWCOUNT) to the end of the sproc -- I seem to
recall using that at one point for this purpose... Another possible
workaround is to remove SET NOCOUNT ON -- ExecuteNonQuery might use the
rowcount internally to get its value.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:906602A3-4E31-458E-865D-102EFD87CEBE@.microsoft.com...
> Yeap it is an update query with 3 conditions
>
> ALTER PROCEDURE dbo.UpdateTicketStatus
> @.NroTicket VARCHAR(12) = NULL,
> @.IDAgencia VARCHAR(12) = NULL,
> AS
> SET NOCOUNT ON
> DECLARE @.Sqlstring VARCHAR(3000)
> SELECT @.Sqlstring = 'UPDATE Ventas SET Status = ''A'''
> SELECT @.Sqlstring = @.Sqlstring + ' WHERE DateDiff(n, FechaHora, GetDate())
> <= 5'
> SELECT @.Sqlstring = @.Sqlstring + ' AND NroTicket = ''' +
> CONVERT(VARCHAR,@.NroTicket) + ''''
> SELECT @.Sqlstring = @.Sqlstring + ' AND ID = ''' +
> CONVERT(VARCHAR,@.IDAgencia) + ''''
> EXECUTE (@.Sqlstring)|||Kenny,
The procedure isn't just an UPDATE statement - it's an EXECUTE statement.
And while you didn't provide the command text of myCommand, it's not an
UPDATE statement either - it's a procedure call. I suspect you will
only get
the rowcount returned if the command text of myCommand is 'UPDATE ...'
SK
Kenny M. wrote:

>Yeap it is an update query with 3 conditions
>
>ALTER PROCEDURE dbo.UpdateTicketStatus
> @.NroTicket VARCHAR(12) = NULL,
> @.IDAgencia VARCHAR(12) = NULL,
>AS
>SET NOCOUNT ON
>DECLARE @.Sqlstring VARCHAR(3000)
>SELECT @.Sqlstring = 'UPDATE Ventas SET Status = ''A'''
>SELECT @.Sqlstring = @.Sqlstring + ' WHERE DateDiff(n, FechaHora, GetDate())
><= 5'
>SELECT @.Sqlstring = @.Sqlstring + ' AND NroTicket = ''' +
>CONVERT(VARCHAR,@.NroTicket) + ''''
>SELECT @.Sqlstring = @.Sqlstring + ' AND ID = ''' +
>CONVERT(VARCHAR,@.IDAgencia) + ''''
>EXECUTE (@.Sqlstring)
>|||umm ok I got it, thks
"Steve Kass" wrote:

> Kenny,
> The procedure isn't just an UPDATE statement - it's an EXECUTE statement
.
> And while you didn't provide the command text of myCommand, it's not an
> UPDATE statement either - it's a procedure call. I suspect you will
> only get
> the rowcount returned if the command text of myCommand is 'UPDATE ...'
> SK
>
> Kenny M. wrote:
>
>

I can not return the rows affected

I can not return the rows affected by just using
int a = myCommand.ExecuteNonQuery(); because the return is always -1
I have to use an Output parameter in my SP all the time.. anybody knows WHY?
'Do you have 'SET NOCOUNT ON' somewhere in your stored procedure or as a
default setting? This can cause the behavior you're seeing.