title: Getting an error converting data type nvarchar to int from application (C#) but not when running the SP on it's own
body: <p>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.</p>
  
  <p>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.</p>
  
  <pre><code>[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
  </code></pre>
  
  <p>C# code snippit</p>
  
  <pre><code>  protected void btnReceiveProcess_Click(object sender, EventArgs e)
      {
          string[] lines = txtReceive.Text.Split('\n');
  
          dm.Open();
          for (int I = 0; I &lt; 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 =
                 "&lt;script type='text/javascript'&gt;alert('Process Complete');&lt;/script&gt;";
              var page = HttpContext.Current.CurrentHandler as Page;
              if ((!page.ClientScript.IsClientScriptBlockRegistered("alert")))
              {
                  page.ClientScript.RegisterClientScriptBlock(GetType(), "alert", script);
              }
          }
  </code></pre>
  
  <p>Stored Procedure</p>
  
  <pre><code>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 &lt;&gt; 0),9)
      end 
  
  
  --Begin Try
  --begin transaction
  
  IF LEN(@boxnum) &gt; 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) &gt; 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
  </code></pre>
  
  <p>Tables</p>
  
  <pre><code>[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
  </code></pre>
  
  <p>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.</p>
  
  <p>Thanks in advance for any help you can provide me.</p>
  
  <pre><code>[FormatException: Input string was not in a correct format.]
  System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer&amp; 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
  </code></pre>