title: String or binary data would be truncated. ASP VB.NET
body: <p>The error is being thrown at the insert statement below, where the dose_str field or column in the database table has, for example, the value “Dose Strength is 200.” But this is getting split into columns, translated to the following 
  SQL statement that I got from the SQL Profiler:</p>
  
  <pre><code>insert into web_ship_detail (web_order_id, line_id, no_of_participants, Amt_inventory, NSC_num, dose_str, dose_unit, dose_form, dose_mult, amt_req) values (123,'B',0,0,900096,'Dose','strength', 'i', '200', 1)
  </code></pre>
  
  <p>However, what I want is to insert the value "Dose strength is 200" as one value for Does_str column.
  I am not sure how to change the command in order to do that.</p>
  
  <p>The data type for each column is : 
  web_order_id    numeric(6, 0)
  line_id char(1)
  no_of_participants  numeric(6, 0)
  Amt_inventory   numeric(6, 0)
  NSC_num numeric(6, 0)
  Dose_str    varchar(20)
  Dose_unit   varchar(2)
  Dose_form   varchar(1)
  Dose_mult   varchar(10)
  amt_req numeric(6, 0)
  amt_shipped numeric(6, 0) </p>
  
  <p>None of them allows null except Dose_Mult and amt_shipped.
                                     - </p>
  
  <pre><code>   myCommand.CommandText = "insert into web_ship_detail (web_order_id, line_id, no_of_participants, Amt_inventory, NSC_num, dose_str, dose_unit, dose_form, dose_mult, amt_req) values (" &amp; webOrderID &amp; ",'" &amp; obj.lineItem1.ToString &amp; "'," &amp; obj.NoOfParticipants1.ToString &amp; "," &amp; obj.AmtInInventory1.ToString &amp; "," &amp; obj.NSCNumber1.ToString &amp; ",'" &amp; obj.DoseStrength1.ToString &amp; "','" &amp; obj.DoseUnit1.ToString &amp; "', '" &amp; obj.DoseForm1.ToString.Substring(0, 1) &amp; "', '" &amp; obj.DoseMult1.ToString &amp; "', " &amp; obj.AmtRequested1.ToString &amp; ")"
  </code></pre>
  
  <p>Below is the entire method:</p>
  
  <pre><code>        For Each de As DictionaryEntry In GetCart()
              Dim obj As OrderLineItem = CType(de.Value, OrderLineItem)
              myCommand.CommandText = "insert into web_ship_detail (web_order_id, line_id, no_of_participants, Amt_inventory, NSC_num, dose_str, dose_unit, dose_form, dose_mult, amt_req) values (" &amp; webOrderID &amp; ",'" &amp; obj.lineItem1.ToString &amp; "'," &amp; obj.NoOfParticipants1.ToString &amp; "," &amp; obj.AmtInInventory1.ToString &amp; "," &amp; obj.NSCNumber1.ToString &amp; ",'" &amp; obj.DoseStrength1.ToString &amp; "','" &amp; obj.DoseUnit1.ToString &amp; "', '" &amp; obj.DoseForm1.ToString.Substring(0, 1) &amp; "', '" &amp; obj.DoseMult1.ToString &amp; "', " &amp; obj.AmtRequested1.ToString &amp; ")"
              myCommand.ExecuteNonQuery()
          Next de
  </code></pre>
  
  <p><strong>Edit 1</strong>
  Below is the attempt to use SQL parameters, but I am still getting the same error, String or binary data would be truncated, with the same value <strong>"Dose Strength is 200"</strong></p>
  
  <pre><code>   For Each de As DictionaryEntry In GetCart()
          Dim obj As OrderLineItem = CType(de.Value, OrderLineItem)
              myCommand.CommandText = "insert into web_ship_detail (web_order_id, line_id, no_of_participants, Amt_inventory, NSC_num, dose_str, dose_unit, dose_form, dose_mult, amt_req) values(@web_order_id, @line_id, @no_of_participants, @Amt_inventory, @NSC_num, @dose_str,@dose_unit, @dose_form, @dose_mult,@amt_req);"
              myCommand.Parameters.AddWithValue("@web_order_id", webOrderID.ToString).SourceColumn = "web_order_id"
              myCommand.Parameters.AddWithValue("@line_id", obj.lineItem1.ToString).SourceColumn = "line_id"
              myCommand.Parameters.AddWithValue("@no_of_participants", obj.NoOfParticipants1.ToString).SourceColumn = "no_of_participants"
              myCommand.Parameters.AddWithValue("@Amt_inventory", obj.AmtInInventory1.ToString).SourceColumn = "Amt_inventory"
              myCommand.Parameters.AddWithValue("@NSC_num", obj.NSCNumber1.ToString).SourceColumn = "NSC_num"
              myCommand.Parameters.AddWithValue("@dose_str", obj.DoseStrength1.ToString).SourceColumn = "dose_str"
              myCommand.Parameters.AddWithValue("@dose_unit", obj.DoseUnit1.ToString).SourceColumn = "dose_unit"
              myCommand.Parameters.AddWithValue("@dose_form", obj.DoseForm1.ToString.Substring(0, 1)).SourceColumn = "dose_form"
              myCommand.Parameters.AddWithValue("@dose_mult", obj.DoseMult1.ToString).SourceColumn = "dose_mult"
              myCommand.Parameters.AddWithValue("@amt_req", obj.AmtRequested1.ToString).SourceColumn = "amt_req"
              myCommand.ExecuteNonQuery()
  </code></pre>
  
  <p><strong>Edit 2</strong>
  I have added the size of the data type below, but I am a different error now</p>
  
  <pre><code>    For Each de As DictionaryEntry In GetCart()
              Dim obj As OrderLineItem = CType(de.Value, OrderLineItem)
                     myCommand.CommandText = "insert into web_ship_detail (web_order_id, line_id, no_of_participants, Amt_inventory, NSC_num, dose_str, dose_unit, dose_form, dose_mult, amt_req) values(@web_order_id, @line_id, @no_of_participants, @Amt_inventory, @NSC_num, @dose_str,@dose_unit, @dose_form, @dose_mult,@amt_req)"
  
              myCommand.Parameters.Add("@web_order_id", SqlDbType.Int, 6).Value = "web_order_id"
              myCommand.Parameters.Add("@line_id", SqlDbType.Char, 1).Value = "line_id"
              myCommand.Parameters.Add("@no_of_participants", SqlDbType.Int).Value = "no_of_participants"
              myCommand.Parameters.Add("@Amt_inventory", SqlDbType.Int, 6).Value = "Amt_inventory"
              myCommand.Parameters.Add("@NSC_num", SqlDbType.Int, 6).Value = "NSC_num"
              myCommand.Parameters.Add("@dose_str", SqlDbType.VarChar, 20).Value = "dose_str"
              myCommand.Parameters.Add("@dose_unit", SqlDbType.VarChar, 2).Value = "dose_unit"
              myCommand.Parameters.Add("@dose_form", SqlDbType.VarChar, 1).Value = "dose_form"
              myCommand.Parameters.Add("@dose_mult", SqlDbType.VarChar, 10).Value = "dose_mult"
              myCommand.Parameters.Add("@amt_req", SqlDbType.Int, 6).Value = "amt_req"
              myCommand.ExecuteNonQuery()
  </code></pre>
  
  <p><strong>Edit 3</strong>
  Below is the new error I do get, Is there anything I need to change to make this code work?</p>
  
  <p>Error Message:System.FormatException: Failed to convert parameter value      from a String to a Int32. ---> System.FormatException: Input string was not in a correct format.</p>
  
  <p>Below is a scenario off the insert statement</p>
  
  <pre><code>    Declare @Web_Order_ID INT
      SET @Web_Order_ID = 123
  
      Declare     @line_id CHAR(1) 
      SET @line_id = 'A'
  
      Declare     @no_of_participants INT
      SET @no_of_participants = 0
  
      Declare     @Amt_inventory INT
      SET @Amt_Inventory = 0
  
      Declare     @NSC_num INT
      SET @NSC_num = 900096
  
      Declare     @dose_str VARCHAR(20)
      SET @Dose_Str = 'Dose Strength is 200'
  
      Declare     @dose_unit VARCHAR(2) 
      SET @dose_unit = 'mg'
  
      Declare     @dose_form VARCHAR(1) 
      SET @dose_form = 'C'
  
      Declare     @dose_mult VARCHAR(10)
      SET @dose_mult = '030'
  
      Declare     @amt_req INT
      SET @amt_req = 200
  
      insert into web_ship_detail (web_order_id, line_id, no_of_participants,  Amt_inventory, NSC_num, dose_str, dose_unit, dose_form, dose_mult, amt_req) 
      values(@web_order_id, @line_id, @no_of_participants, @Amt_inventory, @NSC_num, @dose_str,@dose_unit, @dose_form, @dose_mult,@amt_req)
  </code></pre>