Getting an error converting data type nvarchar to int from application (C#) but not when running the SP on it's own

Question

My company had contracted a developer to build a new inventory program that would replace the old outdated one. Unfortunately for me the developer left before debugging the application and as a result I am stuck trying to figure out 2 issues with my limited knowledge of C# so enough of my sob story here is the issue I am having.

The majority of the application works fine but when trying to receive inventory from our technicians I get an error (SqlException was unhandled by user code- Error converting data tyoe nvarchar to int). I have been staring at the code, tables and stored procedure with no luck finding the problem I hope someone can point out what I am missing here. Below is the stack trace, C# coding, stored procedure and tables.

[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# code snippit

  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);
            }
        }

Stored Procedure

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

Tables

[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

P.S. If anyone can tell me how to create a prompt where if boxnum is not in boxhistory it prompts the user to add the boxtype and corp as it's only needed on boxnum's that were not all ready recorded.

Thanks in advance for any help you can provide me.

[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

My best guess would be that your problem is in this line:

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

in the procedure @boxtype is declared as an int but I believe this overload of AddParameters will make your parameter of type varchar. I don't know what your data is but one or other seems wrong. I suspect the above line probably should be

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

It depends on exactly what BoxType(lines.GetValue(I).ToString())) returns. If it is a string representation of an int then use the above. If it is an int then you don't need the convert at all:

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

Why I came to this conclusion

I thought I'd add a note on how I debugged this error so that you can understand for future use.

The error message "Error converting data type nvarchar to int" tells us that somewhere is expecting an int but receiving an nvarchar. Nvarchars are only a type in sql so we know that there must be either something in the sql or something in the call to the database.

If it was a problem in the procedure itself you would have noticed it when you were testing the procedure away from the rest of the app. This means it must have been down to the way the procedure is being called.

At this point we look at the parameter list and find that there is one parameter that is an int. We then check that against the code and hey presto, there's our suspicious looking line...