c# - 从应用程序 (C#) 将数据类型 nvarchar 转换为 int 时出错，但在单独运行 SP 时却没有

Question

我的公司已与开发人员签约，以构建一个新的库存程序来取代旧的过时的程序。对我来说不幸的是，开发人员在调试应用程序之前就离开了，结果我一直试图用我对 C# 的有限知识来解决 2 个问题，所以我的悲哀故事已经够多了，这就是我遇到的问题。

应用程序的大部分工作正常，但在尝试从我们的技术人员那里接收库存时，我收到一个错误(SqlException 未被用户代码处理 - 将数据类型 nvarchar 转换为 int 时出错)。我一直盯着代码、表格和存储过程，但没有找到问题我希望有人能指出我在这里遗漏了什么。下面是堆栈跟踪、C# 编码、存储过程和表格。

[SqlException (0x80131904): Error converting data type nvarchar to int.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)   +2062078
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +5050204
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +234
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler,   SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)  +2275
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +215
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +987
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +178
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
DAL.DBManager.ExecuteNonQuery(CommandType commandType, String commandText) in C:\Users\kevin.price\Documents\Visual Studio 2010\Projects\Equip\DAL\DAL.cs:221
Equip.EquipmentMainPage.btnReceiveProcess_Click(Object sender, EventArgs e) in C:\Users\kevin.price\Documents\Visual Studio 2010\Projects\Equip\EquipmentMainPage.aspx.cs:358
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +118
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +112
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563

C# 代码片段

  protected void btnReceiveProcess_Click(object sender, EventArgs e)
    {
        string[] lines = txtReceive.Text.Split('\n');

        dm.Open();
        for (int I = 0; I < lines.GetLength(0); I++)
        {
            dm.CreateParameters(9);
            dm.AddParameters(0, "@OP", "RcvdTech");
            dm.AddParameters(1, "@BatchID", "{" + guid + "}");
            dm.AddParameters(2, "@BatchDate", DateTime.Now.Date);
            dm.AddParameters(3, "@boxnum", lines.GetValue(I));
            dm.AddParameters(4, "@boxtype", Convert.ToString(BoxType(lines.GetValue(I).ToString())));
            dm.AddParameters(5, "@status", "RcvdTech");
            dm.AddParameters(6, "@modby", user);
            dm.AddParameters(7, "@corp", null);
            dm.AddParameters(8, "@cominvoice", null);


            {

            }

            dm.ExecuteNonQuery(CommandType.StoredProcedure, "bxReceive");

            dm.Close();

            script =
               "<script type='text/javascript'>alert('Process Complete');</script>";
            var page = HttpContext.Current.CurrentHandler as Page;
            if ((!page.ClientScript.IsClientScriptBlockRegistered("alert")))
            {
                page.ClientScript.RegisterClientScriptBlock(GetType(), "alert", script);
            }
        }

存储过程

ALTER PROCEDURE [dbo].[bxReceive]
-- Add the parameters for the stored procedure here
@OP varchar(50), @BatchID varchar(50), @BatchDate varchar(50),
@boxnum varchar(50), @boxtype int, @status varchar(50), @modby varchar(50), @corp varchar(50), @cominvoice varchar(50) 
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
Declare @msg varchar(50)
set @boxnum = replace(@boxnum,char(13),'')
set @BatchID = left(@BatchID,CHARINDEX(char(125),@BatchID))
set @msg = 'White'

if ltrim(rtrim(@OP)) = 'RcvdTech'
    begin
        set @boxtype = isnull((select top 1 isnull(boxtype,9) from boxHistory where boxnum = rtrim(@boxnum) and boxtype <> 0),9)
    end 


--Begin Try
--begin transaction

IF LEN(@boxnum) > 0 and (SELECT count(*)FROM dbo.bxReceived where BatchID = rtrim(@BatchID)and [boxnum] = rtrim(@boxnum) and [corp] = rtrim(@corp) and [cominvoice] = rtrim(@cominvoice) ) = 0
Begin
INSERT INTO dbo.bxReceived
           ([BatchID]
           ,[BatchDate]
           ,[boxnum]
           ,[boxtype]
           ,[status]
           ,[modby]
           ,[corp]
           ,[cominvoice])

select rtrim(@BatchID), rtrim(@BatchDate),rtrim(@boxnum), @boxtype, rtrim(@status), rtrim(@modby), rtrim(@corp), rtrim(@cominvoice)

if ltrim(rtrim(@OP)) = 'newstock'
    begin
        if (select COUNT(*) from boxHistory where boxnum = rtrim(@boxnum) and enddate is null) = 0
            begin 
                INSERT INTO [boxHistory]
                ([boxnum],[boxtype],[tech],[status],[newstatus]
                ,[account],[startdate],[enddate],[modby],[ReqActn],[ActnReq],[corp],[cominvoice])

                select rtrim(@boxnum), @boxtype,0, rtrim(@status),null,null,GETDATE(), null, rtrim(@modby),null,null,@corp, @cominvoice
            end
        else
            begin
                update [boxHistory]
                    set [newstatus] = rtrim(@status), [enddate] = GETDATE()
                where boxnum = rtrim(@boxnum) and enddate is null 

                INSERT INTO [boxHistory]
                ([boxnum],[boxtype],[tech],[status],[newstatus]
                ,[account],[startdate],[enddate],[modby],[ReqActn],[ActnReq],[corp],[cominvoice])

                select rtrim(@boxnum), @boxtype,0, rtrim(@status),null,null,GETDATE(), null, rtrim(@modby),'Y','Was active in Inventory',@corp,@cominvoice
            end
    end


if ltrim(rtrim(@OP)) = 'RcvdTech'
    begin
        if (select COUNT(*) from boxHistory where boxnum = rtrim(@boxnum) and enddate is null) > 0
            begin
                update [boxHistory]
                    set [newstatus] = rtrim(@status), [enddate] = GETDATE()
                where boxnum = rtrim(@boxnum) and enddate is null 

                INSERT INTO [boxHistory]
                ([boxnum],[boxtype],[tech],[status],[newstatus]
                ,[account],[startdate],[enddate],[modby],[ReqActn],[ActnReq])

                select rtrim(@boxnum), @boxtype,0, rtrim(@status),null,null,GETDATE(), null, rtrim(@modby),null,null
            end
        else
            begin 
                INSERT INTO [boxHistory]
                ([boxnum],[boxtype],[tech],[status],[newstatus]
                ,[account],[startdate],[enddate],[modby],[ReqActn],[ActnReq])

                select rtrim(@boxnum), @boxtype,0, rtrim(@status),null,null,GETDATE(), GETDATE(), rtrim(@modby),'Y','Was not active in Inventory'
                set @msg = 'RED'
            end
    end

表格

[dbo].[boxHistory](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[boxnum] [varchar](50) NOT NULL,
[boxtype] [int] NULL,
[tech] [int] NULL,
[status] [varchar](50) NULL,
[newstatus] [varchar](50) NULL,
[account] [varchar](50) NULL,
[startdate] [datetime] NULL,
[enddate] [datetime] NULL,
[modby] [varchar](50) NULL,
[ReqActn] [char](1) NULL,
[ActnReq] [varchar](50) NULL,
[corp] [varchar](50) NULL,
[cominvoice] [varchar](50) NULL,


[dbo].[bxReceived](
[BatchID] [varchar](100) NULL,
[BatchDate] [varchar](50) NULL,
[boxnum] [varchar](50) NOT NULL,
[boxtype] [int] NOT NULL,
[status] [varchar](50) NULL,
[modby] [varchar](50) NULL,
[cominvoice] [varchar](50) NULL,
[corp] [varchar](50) NULL

附言如果有人能告诉我如何创建一个提示，如果 boxnum 不在 boxhistory 中，它会提示用户添加 boxtype 和 corp，因为只有在 boxnum 上还没有准备好记录时才需要它。

预先感谢您能为我提供的任何帮助。

[FormatException: Input string was not in a correct format.]
System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) +9586043
System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) +119
System.Convert.ToInt32(String value) +48
Equip.EquipmentMainPage.btnReceiveProcess_Click(Object sender, EventArgs e) in C:\Users\kevin.price\Documents\Visual Studio 2010\Projects\Equip\EquipmentMainPage.aspx.cs:347
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +118
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +112
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563

Answer 1

我最好的猜测是您的问题出在这一行:

dm.AddParameters(4, "@boxtype", Convert.ToString(BoxType(lines.GetValue(I).ToString())));

在程序中，@boxtype 被声明为 int，但我相信 AddParameters 的这种重载将使您的参数成为 varchar 类型。我不知道你的数据是什么，但一个或另一个似乎是错误的。我怀疑上面的行可能应该是

dm.AddParameters(4, "@boxtype", Convert.ToInt32(BoxType(lines.GetValue(I).ToString())));

这完全取决于 BoxType(lines.GetValue(I).ToString())) 返回的内容。如果它是 int 的字符串表示形式，则使用上面的方法。如果它是一个 int 那么你根本不需要转换:

dm.AddParameters(4, "@boxtype", BoxType(lines.GetValue(I).ToString()));

为什么我得出这个结论

我想我应该添加一条关于我如何调试此错误的注释，以便您可以理解以备将来使用。

错误信息“Error converting data type nvarchar to int”告诉我们某处需要一个 int 但接收到一个 nvarchar。 Nvarchars 只是 sql 中的一种类型，因此我们知道必须在 sql 中或在对数据库的调用中有一些东西。

如果这是程序本身的问题，您在远离应用程序其余部分测试程序时就会注意到它。这意味着它一定取决于调用过程的方式。

此时我们查看参数列表，发现有一个参数是int。然后，我们根据代码进行检查，嘿，转眼间，我们看到了可疑的一行...