使用的ExecuteNonQuery时（不正确的返回值） [英] Incorrect return value when using ExecuteNonQuery()

问题描述

我有一个C＃程序，我写与SQL Server交互。我愚蠢地将查询字符串硬编码到C＃程序中，并希望将它们转换为服务器上的存储过程。

由于某种原因，一个特定的INSERT语句（那工作正常！）不再工作，我不能弄清楚为什么。我甚至运行一个SQL Profiler Trace，它显示语句是完全形成的！也许有人可以告诉我我做错了什么。

存储过程：
这个SP只是需要一堆参数，并将它们插入表中。很简单。

  ALTER PROCEDURE [dbo]。[usp_InsertNewChangeRequest] 
 
 @Requester INT，
 @ChangeCreationDate DATETIME，
 @ChangeName VARCHAR（200），
 @ChangeDescription VARCHAR（1000），
 @LastModifiedDate DATETIME，
 @LastModifiedBy INT，
 @AffectedArea INT，
 @ImplementationPlan VARCHAR（MAX），
 @BackoutPlan VARCHAR（MAX），
 @RiskLevel TINYINT，
 @ApprovalRequired BIT，
 @IsApproved BIT 
 AS 
 BEGIN 
设置NOCOUNT ON; 
 
 INSERT INTO dbo.ChangeRequests（Requester，ChangeCreationDate，ChangeName，ChangeDescription，
 LastModifiedDate，LastModifiedBy，AffectedArea，ImplementationPlan，BackoutPlan，
 RiskLevel，ApprovalRequired，IsApproved）
 VALUES（@Requester，@ChangeCreationDate，@ChangeName，@ChangeDescription，
 @LastModifiedDate，@LastModifiedBy，@AffectedArea，@ImplementationPlan，@BackoutPlan，
 @RiskLevel，@ApprovalRequired，@IsApproved）
 END 
  

我在C＃中的代码只是预处理SP参数，然后调用非查询：

public int InsertNewChange（int RequesterID，DateTime CreationDate，string ChangeName，
string ChangeDescription，DateTime LastModifiedDate，
int AffectedAreaID，string ImplementationPlan ，string BackoutPlan，
int RiskLevel，int ApprovalRequired，int IsApproved）
{
int retval = 0;

//创建一个名为插入存储过程的命令
SqlCommand command = new SqlCommand（usp_InsertNewChangeRequest，scConnection）;
command.CommandType = CommandType.StoredProcedure;

//将参数添加到存储过程
command.Parameters.Add（new SqlParameter（@ Requester，RequesterID））;
command.Parameters.Add（new SqlParameter（@ ChangeCreationDate，CreationDate））;
command.Parameters.Add（new SqlParameter（@ ChangeName，ChangeName））;
command.Parameters.Add（new SqlParameter（@ ChangeDescription，ChangeDescription））;
command.Parameters.Add（new SqlParameter（@ LastModifiedDate，LastModifiedDate））;
command.Parameters.Add（new SqlParameter（@ LastModifiedBy，RequesterID））;
command.Parameters.Add（new SqlParameter（@ AffectedArea，AffectedAreaID））;
command.Parameters.Add（new SqlParameter（@ ImplementationPlan，ImplementationPlan））;
command.Parameters.Add（new SqlParameter（@ BackoutPlan，BackoutPlan））;
command.Parameters.Add（new SqlParameter（@ RiskLevel，RiskLevel））;
command.Parameters.Add（new SqlParameter（@ ApprovalRequired，ApprovalRequired））;
command.Parameters.Add（new SqlParameter（@ IsApproved，IsApproved））;

retval = command.ExecuteNonQuery（）;
return retval;
}


我不断得到-1的返回值，有一个SQL文本完全在C＃中拼写，我会得到一个1，插入1行。

令人担心的是，我没有看到任何错误在SQL Profiler做跟踪。它显示已通过的语句，它看起来完全正常！当我将它剪切并粘贴到Management Studio中并手动运行它时，它工作正常。

  exec usp_InsertNewChangeRequest @ Requester = 4 ，@ ChangeCreationDate ='2012-05-16 17:55:45'，@ ChangeName ='test name'，@ ChangeDescription ='test description'，@ LastModifiedDate ='2012-05-16 17：56：01.937' LastModifiedBy = 4，@ AffectedArea = 2，@ ImplementationPlan ='test implem'，@ BackoutPlan ='test backout'，@ RiskLevel = 1，@ ApprovalRequired = 0，@ IsApproved = 0 
  

有人知道为什么会发生在我身上吗？

解决方案

因为您已关闭：

  SET NOCOUNT ON; 
  

I have a C# program I'm writing which interacts with SQL Server. I had foolishly hard-coded the query strings into the C# program, and wanted to instead turn those into Stored Procedures on the server.

For some reason, one particular INSERT statement (that was working fine!) no longer works and I can't figure out why. I've even run a SQL Profiler Trace, and it shows that the statement is perfectly formed! Maybe someone can tell me what I'm doing wrong.

STORED PROCEDURE: This SP just takes a bunch of parameters and inserts them into a table. Very simple.

ALTER PROCEDURE [dbo].[usp_InsertNewChangeRequest]

@Requester INT,
@ChangeCreationDate DATETIME,
@ChangeName VARCHAR(200),
@ChangeDescription VARCHAR(1000),
@LastModifiedDate DATETIME,
@LastModifiedBy INT,
@AffectedArea INT,
@ImplementationPlan VARCHAR(MAX),
@BackoutPlan VARCHAR(MAX),
@RiskLevel TINYINT,
@ApprovalRequired BIT,
@IsApproved BIT
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO dbo.ChangeRequests(Requester, ChangeCreationDate, ChangeName, ChangeDescription, 
            LastModifiedDate, LastModifiedBy, AffectedArea, ImplementationPlan, BackoutPlan, 
            RiskLevel, ApprovalRequired, IsApproved)
VALUES (@Requester, @ChangeCreationDate, @ChangeName, @ChangeDescription,
            @LastModifiedDate, @LastModifiedBy, @AffectedArea, @ImplementationPlan, @BackoutPlan,
            @RiskLevel, @ApprovalRequired, @IsApproved) 
END

My code in C# simply preps the SP parameters then calls a nonquery:

public int InsertNewChange(int RequesterID, DateTime CreationDate, string ChangeName,
        string ChangeDescription, DateTime LastModifiedDate, 
        int AffectedAreaID, string ImplementationPlan, string BackoutPlan,
        int RiskLevel, int ApprovalRequired, int IsApproved)
    {
        int retval = 0;

        // Create a command whose name is the stored procedure for inserts
        SqlCommand command = new SqlCommand("usp_InsertNewChangeRequest", scConnection);
        command.CommandType = CommandType.StoredProcedure;

        // add the parameters to the stored procedure
        command.Parameters.Add(new SqlParameter("@Requester", RequesterID));
        command.Parameters.Add(new SqlParameter("@ChangeCreationDate", CreationDate));
        command.Parameters.Add(new SqlParameter("@ChangeName", ChangeName));
        command.Parameters.Add(new SqlParameter("@ChangeDescription", ChangeDescription));
        command.Parameters.Add(new SqlParameter("@LastModifiedDate", LastModifiedDate));
        command.Parameters.Add(new SqlParameter("@LastModifiedBy", RequesterID));
        command.Parameters.Add(new SqlParameter("@AffectedArea", AffectedAreaID));
        command.Parameters.Add(new SqlParameter("@ImplementationPlan", ImplementationPlan));
        command.Parameters.Add(new SqlParameter("@BackoutPlan", BackoutPlan));
        command.Parameters.Add(new SqlParameter("@RiskLevel", RiskLevel));
        command.Parameters.Add(new SqlParameter("@ApprovalRequired", ApprovalRequired));
        command.Parameters.Add(new SqlParameter("@IsApproved", IsApproved));

        retval = command.ExecuteNonQuery();
        return retval;
    }

I'm constantly getting a -1 return value, whereas before, when I had the SQL text spelled out entirely in C#, I would get a 1, for the 1 row inserted.

Infuriatingly, I don't see any error in SQL Profiler when doing a trace. It shows the statement passed, and it looks completely fine! When I cut and paste it into Management Studio and run it myself by hand, it works fine.

    exec usp_InsertNewChangeRequest @Requester=4,@ChangeCreationDate='2012-05-16 17:55:45',@ChangeName='test name',@ChangeDescription='test description',@LastModifiedDate='2012-05-16 17:56:01.937',@LastModifiedBy=4,@AffectedArea=2,@ImplementationPlan='test implem',@BackoutPlan='test backout',@RiskLevel=1,@ApprovalRequired=0,@IsApproved=0

Does anyone know why this would be happening to me? MANY THANKS!

解决方案

Because you turned it off:

SET NOCOUNT ON;

这篇关于使用的ExecuteNonQuery时（不正确的返回值）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！