Fog Creek Software
Discussion Board




Welcome! and rules

Joel on Software

SqlCommand.ExecuteNonQuery Bug or user error?

Can anyone explain to me why ExecuteNonQuery would return 10 rows instead of 5?  Only the 5 rows inserted are "affected".


        Dim conn As SqlConnection
        Dim cmd As SqlCommand
        Dim dr As SqlDataReader
        Dim txn As SqlTransaction
        conn = New SqlConnection(CONNSTR)
        conn.Open()
        cmd = conn.CreateCommand
        cmd.Connection = conn

        Dim x As Integer = 5

      cmd.CommandText = "insert into Random(id) " & _
                        "select top " & x & " person_id " & _
                        " from person order by newid()"
      Dim n As Integer
      n = cmd.ExecuteNonQuery()

Chris F
Tuesday, March 15, 2005

Just a guess -- 5 records affected in the select and 5 in the insert. 5+5=10?

Sgt.Sausage
Tuesday, March 15, 2005

It seems that's what's happening, but according to the docs it should be returning 5:

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

Only 5 rows are affected by the insert.

Chris F
Wednesday, March 16, 2005

Have you tried this without the "insert" section being in there? How many do you get back then?

Sheeshers http://spaces.msn.com/members/ashishs
Wednesday, March 16, 2005

Not much point using an ExecuteNonQuery with a select...

Chris F
Wednesday, March 16, 2005

Have you tried running the same query in Query Analyzer to see how many rows it thinks are affected?

Possibly a trigger is updating another table?

Joe
Wednesday, March 16, 2005

Query Analyzer reports 5 rows, which is why I'm thinking it's a bug somewhere in the SQLClient class.

Chris F
Thursday, March 17, 2005

OK, it's related to a trigger on the table, but the question is still why the f*ck it would be counting the rows updated by the trigger?

Chris F
Friday, March 18, 2005

Both excluding and including rows affected by triggers would seem to be a reasonable interpretation of "rows affected by the query".  But you would have hoped that SqlClient and Query Analyzer would be consistent in their interpretation.

You could try adding SET NOCOUNT ON to the start of your trigger(s).

Joe
Sunday, March 20, 2005

"But you would have hoped that SqlClient and Query Analyzer would be consistent in their interpretation."

They don't even share an underlying API. I expect them to act significantly different.

Brad Wilson
Monday, March 21, 2005

Well, by my definition, a SELECT doesn't "affect" a row, so I still say it's a bug.

Chris F
Monday, March 21, 2005

er...ignore that last comment...brain freeze.  Of course the trigger is doing an insert.

Chris F
Monday, March 21, 2005

*  Recent Topics

*  Fog Creek Home