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

Solution

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...

Comments

[JohnD]

Is the @boxtype parameter of [bxReceive] expecting an int and the code is passing a string?

[Kprice84]

Thank you for your response I did not notice it being converted to a string and looking farther up in the code I do see where he is pulling it from the table as a string I am going to change the code and test it hopefully it will resolve the issue. I will let you know how it goes.

[Chris]

That's cool. As others indicated the error message pointed those of us more experienced at the right place to look. I'll actually add a note to my answer explaining how I found the error so you can understand a bit more how I came to the above answer. :)

[Chris]

Have you checked what is actually coming back from that? The error indicates that whatever string it was given that it couldn't be converted to an integer. It may be that it can return an empty string in which case it won't be able to parse it as an int. In this case you may be better off passing a varchar to the database and letting that worry about parsing it and putting in defaults if needed.

[Kprice84]

I tried changing it to Convert.ToInt32 and it is giving me another error I will add the stack trace to my orignal post please let me know if you need anymore of the coding

[Kprice84]

I appologize like I said I am very new to this I'm a systems admin who is taking classes on programming and Sql in college so my boss kind of just threw it on me I will check what it is returning and let you know what I find.

[Kprice84]

From looking at the tables and what should be returning is a intger between 1 and 17 and there should be no null values unless it is not in boxhistory in which case it should be creating a new record.

[Kprice84]

Thank you Chris for taking the time to help me with this issue after what you had brought to my attention I was able to locate the conversation issue within Sql and correct it by changing my data type to match the string and resolve the issue.