ExecuteNonQuery returns -1 even if rows are successfully inserted

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » ExecuteNonQuery returns -1 even if rows are...

ExecuteNonQuery returns -1 even if rows are successfully inserted

Rate Topic

Display Mode

Topic Options

Author

Message

IT researcher

Posted Monday, February 11, 2013 2:19 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 186, Visits: 956

I have am using ExecuteNonQuery to get number of rows inserted But i found that it returns -1 even though rows are successfully inserted.
I am using simple insert statement.

ExecuteUpdtCmdWithRetry = Cmd.ExecuteNonQuery()

here Cmd is a parameter which contains the sql insert statement.

The ExecuteNonquery return proper value.But sometime returns -1 and then it keeps showing -1 if any number of insert is done. but all values successfully inserted.Only problem with ExecuteNonQuery returning -1.

This error comes only to a single database. For all other database in that server ExecuteNonquery returns proper value.So what may be the problem that causes this?

Post #1418260

Sean Lange

Posted Monday, February 11, 2013 7:37 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 1:17 PM
Points: 8,641, Visits: 8,273

winmansoft (2/11/2013)

It is impossible to determine based on what you posted. -1 is returned if the statement is not an insert, update or delete. It can also return -1 if there is a rolled back transaction.

You said this happens on an insert. Can you start by posting the CommandText? There are a number of reasons this could happen.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1418429

IT researcher

Posted Tuesday, February 12, 2013 12:02 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 186, Visits: 956

The query i have used is insert into [customer]([Id]) values('531067')

And another one is update statement

update [customer] set [Call attnd]='ses' where Id = 531067 and (Ltrim(Rtrim(cast([Call attnd] as varchar(max)))) = '' or Ltrim(Rtrim(cast([Call attnd] as varchar(max)))) is null)

Post #1418765

IT researcher

Posted Tuesday, February 12, 2013 12:53 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 186, Visits: 956

Here is complete details on what problem i am facing

Problem with “ExecuteNonQuery’”:

We are using VB.Net & in the below code sample ‘ExecuteNonQuery’ method returns -1 while executing Insert & update statements even though actually rows have been affected.

Public Function ExecuteUpdtCmdWithRetry(ByVal Msfgmain As AxMSFlexGridLib.AxMSFlexGrid, ByVal Cmd As DbCommand) As Integer
ExecuteUpdtCmdWithRetry = -1
Dim AttemptCnt As Integer = 1
Dim QueryString As String = Cmd.CommandText
Do While AttemptCnt <= 3
Try
ExecuteUpdtCmdWithRetry = Cmd.ExecuteNonQuery()
Exit Do
Catch ErrorObj As Exception
MsgBox(Err.description)
End Try
Loop
End Function
Sample query that are set to the command:
Insert into [customer]([Id]) values('531067')
Update [customer] set [Phone no]='09966949119' where Id='531067'

For first few updates/insert ‘ExecuteNonQuery’ return proper value, later on it returns -1 & it repeats continuously. Once the program is closed & opened again it works properly for few updates/inserts.

Below change done in the code before the command ‘ExecuteNonQuery’, now the return value of ‘ExecuteNonQuery’ is proper.

Cmd.CommandText = "set nocount off; " & Cmd.CommandText

When ‘set Nocount off’ is done then the result is proper. Why is this happening? Whether ‘Nocount’ is set ‘on’ in-between?
We have 2 SQL Server installed in different PC. Error comes only for the database kept in one server (other database in server have very few inserts. We haven’t checked them) & in the other server Insert statement never returned -1. (Update statements we have not checked)
We have tried shifting of data to new database & new table in the same server but still the error persists. Initially when this error started we found some errors while running ‘DBcheck’ command which have been repaired immediately, but still the error persists.

Post #1418777

Sean Lange

Posted Tuesday, February 12, 2013 8:30 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 1:17 PM
Points: 8,641, Visits: 8,273

Do you have any triggers on the customer table?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1419003

IT researcher

Posted Tuesday, February 12, 2013 10:28 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 186, Visits: 956

No. I don't have any triggers in customer table.

Post #1419291

« Prev Topic | Next Topic »

Permissions