-1

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:

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)

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.

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)

None of them allows null except Dose_Mult and amt_shipped. - 

   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 (" & webOrderID & ",'" & obj.lineItem1.ToString & "'," & obj.NoOfParticipants1.ToString & "," & obj.AmtInInventory1.ToString & "," & obj.NSCNumber1.ToString & ",'" & obj.DoseStrength1.ToString & "','" & obj.DoseUnit1.ToString & "', '" & obj.DoseForm1.ToString.Substring(0, 1) & "', '" & obj.DoseMult1.ToString & "', " & obj.AmtRequested1.ToString & ")"

Below is the entire method:

        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 (" & webOrderID & ",'" & obj.lineItem1.ToString & "'," & obj.NoOfParticipants1.ToString & "," & obj.AmtInInventory1.ToString & "," & obj.NSCNumber1.ToString & ",'" & obj.DoseStrength1.ToString & "','" & obj.DoseUnit1.ToString & "', '" & obj.DoseForm1.ToString.Substring(0, 1) & "', '" & obj.DoseMult1.ToString & "', " & obj.AmtRequested1.ToString & ")"
            myCommand.ExecuteNonQuery()
        Next de

Edit 1 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 "Dose Strength is 200"

   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()

Edit 2 I have added the size of the data type below, but I am a different error now

    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()

Edit 3 Below is the new error I do get, Is there anything I need to change to make this code work?

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.

Below is a scenario off the insert statement

    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)
Improve this question
17
  • If you used SqlParameter(), that would avoid that problem. What you have is also prone to sql injection, a big no-no. Commented Jul 24, 2015 at 15:34
  • Thanks, for the reply, but where to use SqlParameter()? Commented Jul 24, 2015 at 15:52
  • You should use parameters for each and every value. When you build a string containing external data you are vulnerable to sql injection and you can't control the size of the data. If you use parameters you prevent injection AND can prevent this truncation error when the data is larger than the column. Commented Jul 24, 2015 at 15:56
  • Hi Sean, could you provide me with a line of code as en example for one value? Thanks Commented Jul 24, 2015 at 15:59
  • 1
    While waiting for Sean to reply, you should google "asp.net parameterized sql example" Commented Jul 24, 2015 at 16:07

1 Answer 1

Reset to default
2

Apart from SQL injection issue or any best practice kind of stuff, your error must be caused by truncated value on inserting one/more column. The error thrown from inserting "dose_unit" Column.

your query : 

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)

while your table definition :

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)

your query trying to insert "Strength" which has 8 chars into Dose_unit which defined as VARCHAR(2)

If you found your error came from, then the rest is about your logic on managing your parameter.

NOTE: if you could managed this error, i suggest you search more on SQL Parameterized as others recommend.

Good Luck.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks @OAN, I figured out that error is being thrown due to the explanation you provided. I am now looking into how to make the query not to insert data as split, but as one value alphanumeric value "Dose Strength is 200" into Dose_str column.
I have added an attempt of answering using the asp.net parameterized sql, but I am getting the same error. Do you have any suggestion on making the code work?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.