String or binary data would be truncated in Varbinary(MAX)

Question

I created a table with one of the column having datatype varbinary(MAX) in sql server 2008. Using C# code I tried to insert a pdf file(18000 bytes) but it giving error "String or binary data would be truncated"

I gone through many thread, answer is because of column size is lesser than data size. But the maximum size of Varbinary(MAX) is 2GB. I need a help

CREATE TABLE [dbo].[Purchase_Order] (
[PO_Number]           VARCHAR (25)    NOT NULL,
[Organization_Name]   VARCHAR (10)    NOT NULL,
[PO_Date]             DATE            NOT NULL,
[Value_Amount]        NUMERIC(8, 2)  NOT NULL,
[Currency_Type]       NVARCHAR (10)   NOT NULL,
[Customer_Name]       VARCHAR (50)    NOT NULL,
[Contact_Person]      VARCHAR (50)    NOT NULL,
[Customer_Location]   VARCHAR (20)    NOT NULL,
[End_Date]            DATE            NOT NULL,
[Emug_Contact_Person] VARCHAR (50)    NOT NULL,
[Payment_Terms]       NUMERIC (3)     NOT NULL,
[Project_Type]        NCHAR (10)      NOT NULL,
[File_Name]                VARBINARY (MAX) NOT NULL,
CHECK ([PO_Number]<>''),
CHECK ([Organization_Name]<>''),
CHECK ([PO_Date]<>''),
CHECK ([Value_Amount]<>(0)),
CHECK ([Currency_Type]<>''),
CHECK ([Customer_Name]<>''),
CHECK ([Contact_Person]<>''),
CHECK ([Customer_Location]<>''),
CHECK ([End_Date]<>''),
CHECK ([Emug_Contact_Person]<>''),
CHECK ([Payment_Terms]<>''),
CHECK ([Project_Type]<>''),

);

Here is my insert code

public int Insertcommand(string tablename, string[] columlist, object[] valuelist)
    {
        int x = 0;
        string commandstring = createcommand(tablename, columlist);
        MyCommand = new SqlCommand(commandstring, GetConnection());

        for (int i = 0; i < valuelist.Count(); i++)
        {
            MyCommand.Parameters.AddWithValue("@" + columlist[i].ToLower(), valuelist[i]);
        }

        x = MyCommand.ExecuteNonQuery();

        return x;
    }

variable names

Mycommand string

qry = "INSERT into Purchase_Order (PO_Number, Organization_Name, PO_Date, Value_Amount, Currency_Type, Customer_Name, Contact_Person, Customer_Location, End_Date, Emug_Contact_Person, Payment_Terms, Project_Type, File_Name) VALUES (@po_number, @organization_name, @po_date, @value_amount, @currency_type, @customer_name, @contact_person, @customer_location, @end_date, @emug_contact_person, @payment_terms, @project_type, @file_name)"

Why would a file name be more, and shouldn't it be `VARCHAR(MAX)` if it's just a name? — Lloyd, May 24 '16 at 11:58
How do you insert the data from C#? I would be particularly interested in the way you set up the parameter for the varbinary column, as there is a potential problem when using implicit conversion. — Dirk, May 24 '16 at 11:58
Are you sure it's the `File_Name` column that has the truncation problem? Pleas show us your code that produces the error. — juharr, May 24 '16 at 11:59
Hi, what is the type u are using for File_Name property in C# Model? Here is some good explanation is provided http://stackoverflow.com/questions/5824620/what-sqldbtype-maps-to-varbinarymax — error_handler, May 24 '16 at 12:05
It you are trying to INSERT data into SQL Server from C# use parameters in command.text. Make sure you define the parameters to equivalent types as in database. — jdweng, May 24 '16 at 12:23
If File_Name is for the binary content of your PDF, probably the error is thrown for other string fields like Organization_Name, etc. Please check them as well — Eralper, May 24 '16 at 12:29
Is `Onsite-Domestic` is Project type?? if yes then this error occurred because of its length `NCHAR(10)` — Jaydip Jadhav, May 24 '16 at 12:37

score 6 · Accepted Answer · answered May 24 '16 at 12:38

6

The problem here is the Project_Type field. Here a comparison on the data type length and the actual values:

INSERT into dbo.Purchase_Order
( PO_Number -- 25
, Organization_Name -- 10
, PO_Date
, Value_Amount
, Currency_Type -- 10
, Customer_Name -- 50
, Contact_Person  -- 50
, Customer_Location  -- 20
, End_Date
, Emug_Contact_Person -- 50
, Payment_Terms
, Project_Type -- 10 <--- BAD STUFF
, File_Name -- MAX
)
VALUES
( @po_number -- 10
, @organization_name -- 3
, @po_date
, @value_amount
, @currency_type -- 5
, @customer_name -- 2
, @contact_person -- 3
, @customer_location -- 2
, @end_date
, @emug_contact_person -- 3
, @payment_terms
, @project_type -- 15 <--- BAD STUFF
, @file_name
)

You will see that Project_Type only allows 10 characters, but the actual value (ONSITE-DOMESTIC) is 15 characters long.

answered May 24 '16 at 12:38

Patrick Hofman

153,850
22
249
325

@patrick....Thank you very much....I increased the size of column.. But i am facing new problem " Error converting data type varchar to numeric". Do u feel anything wrong with other column... – Sethu Raman May 24 '16 at 12:42
Not a clear problem. Try to eliminate fields yourself. Create a table that allows null values and try each column one by one. – Patrick Hofman May 24 '16 at 12:53
Is there a reason to use NUMERIC over INT when not specifying a decimal? – Anthony Mason May 24 '16 at 17:55
@PatrickHofman that error because of check contraint...i gave string value for numeric field.. – Sethu Raman May 26 '16 at 07:04

score 0 · Answer 2 · answered Aug 03 '23 at 19:24

0

Just want to throw this out there in case someone else has the same issue. I had a trigger logging an audit trail and that trigger was causing the issue. It had nothing to do the the stored procedure.

answered Aug 03 '23 at 19:24

Jeremy Hodge

612
3
14

String or binary data would be truncated in Varbinary(MAX)

2 Answers2