Rowsaffected and ExecuteNonQuery

[Heike]

In the VB help I can read, that the command ExecuteNonQuery returns the number of affected rows. I tried it, but I always get a -1. Have some of you experiences with this? Here is my code:

Public Function DokumentLoeschen() As Boolean

Dim blnOK As Boolean = True
Dim objDBCommand As New OleDb.OleDbCommand()
Dim objDBParam As OleDb.OleDbParameter
Dim anz As Integer

Try
objDBCommand.Connection = objIBS_SQL.OleDbConnection_SYSTEM
objDBCommand.CommandText = objIBS_SQL.qryDeleteA_IBSDOC.CommandText
objIBS_SQL.blnCopyParameter(objIBS_SQL.qryDeleteA_IBSDOC, objDBCommand)

With objDBCommand
If objIBS_SQL.blnOpenConnection(.Connection()) Then
WhereBedingung(objDBCommand)
anz = .ExecuteNonQuery()
MsgBox (anz)
End If
End With
Catch
blnOK = False
MsgBox("clsIBS_DokumentInfo.DokumentLoeschen: " & Err.Description)
Finally
objDBCommand = Nothing
objDBParam = Nothing
End Try

Return blnOK
End Function

[mkapadia]

hi,

Am stuck with the same problem. Any pointers?

[Asgorath]

Remarks
You can use the ExecuteNonQuery to perform catalog operations (for example, querying the structure of a database or creating database objects such as tables), or to change the data in a database without using a DataSet by executing UPDATE, INSERT, or DELETE statements.

Although the ExecuteNonQuery does not return any rows, any output parameters or return values mapped to parameters are populated with data.

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.



SqlDataReader.RecordsAffected - Gets the number of rows changed, inserted, or deleted by execution of the Transact-SQL statement.

Regards
Jorge

[mkapadia]

Thanks for the reply Jorge.

But i guess I need to tell you more about my case.

I have a update query, which updates a particular record, based on whether the record was updated or it wasnt, I do some other processing.

Till date it was working fine. now I am not sure someone has changed "some settings" due to which I do not get the records affected by the update query ( 1 record)
instead I get a return value of less than 1 i.e -1

dim intR as integer

objCommand.Commandtext = "Update MyTable set FieldValue=1 where OtherField=2"
'Some more stms..for connections,etc
intR = objCommand.ExecuteNonQuery
if intR > 0 then
    'Do some processing
else
   'Do some other processing
End if

However, I see that the record IS getting updated properly.

[Asgorath]

Strange this code works fine for i get 1 row afftected

Me.SqlCommand1.Connection = Me.SqlConnection1
Me.SqlCommand1.CommandType = CommandType.StoredProcedure
Me.SqlCommand1.CommandText = "grava_cor"
Me.SqlCommand1.Parameters.Clear()
Me.SqlCommand1.Parameters.Add("@cor", SqlDbType.Char).Value = Me.TextBox1.Text
Dim a As Integer
a = Me.SqlCommand1.ExecuteNonQuery()
MessageBox.Show(a.ToString)

Regards
Jorge

[mkapadia]

thats what i am sying, it used to work before, but all of a sudden its stopped!!

[FishGuy]

Does the SP still have the "Set NoCount" Property set?

[techgnome]

The main diff I see is SQLCommand vs OleDbCommand .... That tells me you aren't using SQL Server. So the problem may lay in the DBMS not reporting RowsAffected.

-tg

[mkapadia]

FishGuy: I am not using a SP here, its a direct Update Command. Secondly, I did run set noCount OFF from the Query Analyzer. Apart from this, a different application accessing a completely different database on the same database server, too suddenly finds that the update query (ExecuteNonQuery) doesnt return the number of rows affected. Maybe depending on the number of rows affected from the executenonquery isnt a good idea but these applications have been running for quite sometime now and it would take some time for us to modify the code to get the @@RowCount which still returns the correct value.

techgnome: The code posted in the first post is of OLEDBCommand, but I am using SQL COmmand object for accessing my SQL server. Sorry for not speifying this earlier.

Thanks for your replies guys.

[mkapadia]

shall i BUMP this thing?

[veryjonny]

not sure, but i guess you try something like

DBCC traceoff(3640)

http://www.sql-server-performance.com/rd_traceflags.asp

Eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. This is similar to the session setting of SET NOCOUNT ON, but when set as a trace flag, every client session is handled this way.

[mkapadia]

tried that as well, restarted SQL server no luck