Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
winman
Starting Member
26 Posts |
Posted - 2013-02-11 : 05:49:54
|
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? |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-11 : 05:59:47
|
see if you've SET NOCOUNT ON; statement on top of SQL query which is assigned to Cmd------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
winman
Starting Member
26 Posts |
Posted - 2013-02-11 : 06:45:10
|
Hi.. visakh16I have already checked that. set nocount on is not set. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-11 : 07:17:56
|
whats the query you're trying to execute? is it a procedure or adhoc sql?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
winman
Starting Member
26 Posts |
Posted - 2013-02-12 : 01:49:29
|
It is just an insert statement |
|
|
winman
Starting Member
26 Posts |
Posted - 2013-02-12 : 02:03:20
|
The query i have used is insert into [customer]([Id]) values('531067')And another one is update statementupdate [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) |
|
|
winman
Starting Member
26 Posts |
Posted - 2013-02-12 : 02:53:58
|
Here is complete details on what problem i am facingProblem 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 = -1Dim AttemptCnt As Integer = 1Dim QueryString As String = Cmd.CommandTextDo While AttemptCnt <= 3 Try ExecuteUpdtCmdWithRetry = Cmd.ExecuteNonQuery() Exit Do Catch ErrorObj As Exception MsgBox(Err.description) End Try Loop End FunctionSample 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.CommandTextWhen ‘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. |
|
|
|
|
|