Executenonquery（）返回-1 [英] Executenonquery() returns -1

问题描述

我在数据库中插入了一些值。我正在使用visual C＃和sql server.Here当我插入值时，我在执行nonquery（）时得到-1，并且数据库没有更新。我通过调试检查了每个值。但是找不到它来的哪一行的错误。所以我怎么能找到



我尝试了什么：



我用谷歌搜索但未能得到合适的解决方案。

I am inserting some values into databse. I am using visual C# and sql server.Here when I am inserting values I am getting -1 at execute nonquery() and database is not updated. I checked every value by debugging. but Failed to find error in which line it is coming.So how can I find that

What I have tried:

I googled but failed to get suitable solution.

推荐答案

你只能获得一个非-1的值删除，更新或插入提交值的语句。如果发生回滚，您将获得-1，因此很可能数据库正在回滚该值。由于你甚至没有提供最少的信息，我猜它会像回滚那样导致你看不到任何数据。一旦我们看到一些代码，我会修改我的意见。

You will only get a value other than -1 for a delete, update or insert statement that commits a value. If a rollback occurs, you'll get -1 so it could well be that the database is rolling back the value. As you haven't even supplied the most minimum of information, I'm going to guess it's something like a rollback that's causing you not to see any data. I will revise my opinion once we actually see some code.

要添加一些细节，如果你使用的是存储过程，那么设置 NOCOUNT 选项导致此效果。但是，它没有解释为什么没有找到数据，因此指出您要么不添加数据，要么回滚。



另请注意，回滚不一定在服务器端完成





要测试存储过程行为的不同变化，请考虑以下场景



测试表

To add some details, if you're using a stored procedure then setting the NOCOUNT option on causes this effect. However, it does not explain why no data is found so as pointed out you either add no data or it is rolled back.

Also note that the rollback is not necessarily done on the server side


To test different variations for stored procedure behaviour consider the following scenario

Test table

CREATE TABLE NoCountTable (
   col1 int
);
GO

测试程序

Test procedures

CREATE PROCEDURE NoCountProcedure AS 
BEGIN
  SET NOCOUNT ON;
  INSERT INTO NoCountTable VALUES (1);
END;
GO

CREATE PROCEDURE CountProcedure AS 
BEGIN
  SET NOCOUNT OFF;
  INSERT INTO NoCountTable VALUES (1);
END;
GO

CREATE PROCEDURE RollbackProcedure AS 
BEGIN
  SET NOCOUNT ON;
  BEGIN TRANSACTION
     INSERT INTO NoCountTable VALUES (1);
  ROLLBACK;
END;
GO

CREATE PROCEDURE ExceptionProcedure AS 
BEGIN
  SET NOCOUNT ON;
  BEGIN TRANSACTION
     INSERT INTO NoCountTable VALUES (1/0);
  ROLLBACK;
END;
GO

来电计划

Calling program

int result;

using (SqlConnection connection = new SqlConnection()) {
   connection.ConnectionString = "server=.\\<instance_name_here>;integrated security=true";
   using (SqlCommand command = new SqlCommand()) {
      command.Connection = connection;
      try {
         connection.Open();

         command.CommandText = "NoCountProcedure";
         result = command.ExecuteNonQuery();
         Debug.WriteLine(string.Format("{0} returned {1}", 
                         command.CommandText,  result));

         command.CommandText = "CountProcedure";
         result = command.ExecuteNonQuery();
         Debug.WriteLine(string.Format("{0} returned {1}", 
                         command.CommandText, result));

         command.CommandText = "RollbackProcedure";
         result = command.ExecuteNonQuery();
         Debug.WriteLine(string.Format("{0} returned {1}", 
                         command.CommandText, result));

         command.CommandText = "ExceptionProcedure";
         result = command.ExecuteNonQuery();
         Debug.WriteLine(string.Format("{0} returned {1}", 
                         command.CommandText, result));
      } catch (System.Exception exception) {
         Debug.WriteLine(string.Format("Exception occurred {0}: {1}", 
                         exception.Source, exception.Message));
      }
   }
}

程序的输出是

The output from the program is

NoCountProcedure returned -1
CountProcedure returned 1
RollbackProcedure returned -1
Exception thrown: 'System.Data.SqlClient.SqlException' in System.Data.dll
Exception occurred .Net SqlClient Data Provider: Divide by zero error encountered.
The statement has been terminated.

但正如@ Pete-OHanlon指出的那样，这只是解释了不同的可能性你还没有发布任何代码。

But as pointed out by @Pete-OHanlon, this is merely explaining different possibilities since you haven't posted any code.

如何找到它：没有代码提交的方法：



1 - 创建查询您认为您希望执行并查看它是否在SQL中直接运行（但是您这样做）。



如果成功插入记录：

2 - 如果可行，将其粘贴到您的代码中，以便通过Executenonquery（）执行已知的工作查询。如果它不起作用，问题在于外部部件（例如回滚或其他）。



如果成功插入记录：

3 - 陷阱（可能是屏幕打印？）您使用代码生成的查询，看看它有什么问题。也许在上面的（1）中使用它。



您现在已经调试了您的查询。确定是否因为SQL不好或者其余的处理都不好。



如果是后者，请检查您的条件（可能通过评论！）并查看出现问题的地方。或者更好的是，使用调试器。

How to Find It: No Code Submitted method:

1 - create the query you think you wish to execute and see if it works when run directly in SQL (however you do this).

If that successfully inserts the record:
2 - if that works, paste it into your code so that the know working query is executed via Executenonquery(). If it doesn't work, problem is in the external parts (such as a rollback or whatever).

If that successfully inserts the record:
3 - trap (possibly a print to screen?) the query you are generating with your code and see what's wrong with it. Perhaps use it in (1), above.

You have now debugged your query. Determined if it is because the SQL is no good or the rest of your handling is no good.

If the latter, check your conditionals (maybe by commenting out!) and see where things go wrong. Or better yet, use debugger.

这篇关于Executenonquery（）返回-1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！