ExecuteNonQuery problem

  • Thread starter Thread starter Chris Stone
  • Start date Start date
C

Chris Stone

In the following function, I always get rows affected = 2 although I know
I'm only updating one record. Can anyone please give me an idea why rows
affected is not = 1?

Thank You for any assistance!!!


Private Function DeleteRecord(ByVal strRecID As String) As Boolean

Dim Conn As New
SqlConnection(ConfigurationSettings.AppSettings("ConnectString"))
Dim Command As New SqlCommand("UPDATE tblDepartments SET ActiveRecord =0
WHERE DeptID=" & strRecID, Conn)
Dim intRowsAffected As Integer

Conn.Open()
intRowsAffected = Command.ExecuteNonQuery()

'Response.Write(intRowsAffected)

If (intRowsAffected = 1) Then
Return True
Else
Return False
End If

End Function
 
Chris,

It sounds like there's a trigger involved. If that's the
case, SQL server will report how many rows were affected by the
actual DELETE query, as well as how many are affected by the
trigger. If you try the same query from Query Analyzer, you'd
see two messages saying "1 row(s) affected". Setting NOCOUNT ON
at the beginning of the trigger, and re-setting it to OFF at the
end of the trigger will suppress this information from the
trigger.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.
 
Thank You!!! Thank You!!! You are exactly right. I ran the query in Query
Analyzer but only got (1 row(s) affected) so it never occurred to me that
the trigger was returning 1 row affected as well. After your response, I
tried it again and found that it does show (1 row(s) affected) twice if I
set the Show Execution Plan option. That was an oversight on my part!

I consider it a great honor to have you answer my question. I really
appreciate the books you have written. They have been a valuable asset to
me and have taught me so much!

Thanks and God Bless You,
Chris Stone
 
Chris,

Glad to hear that you have the scenario working correctly.
Thank you for the kind words.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.
 
Back
Top