Error : String or binary data would be truncated. The data for table-valued parameter doesn't conform to the table type of the parameter

Question

I am getting error

String or binary data would be truncated. The data for table-valued parameter doesn't conform to the table type of the parameter.The statement has been terminated.

Stored procedure is:

CreatePROCEDURE [dbo].[addquestion] 
     @dt as MyDataTable readonly
AS
BEGIN
    insert into questiontbl(Question)
        select(Question) 
        from @dt;
END

The table is:

CREATE TABLE [dbo].[questiontbl]
( 
  [checkval] [varchar](max) NULL, 
  [Question] [varchar](max) NULL 
) 

C# code:

con.Close();
con.Open();

DataTable sqa = Session["questionlist"] as DataTable;

SqlParameter tvparam = cmd.Parameters.AddWithValue("@dt", sqa);                
tvparam.SqlDbType = SqlDbType.Structured;

cmd.ExecuteNonQuery();

Cmd.ExecuteNonQuery() returns the error mentioned. I have matched the datatype - it is varchar(max) in type and table as well.

Solution 1

I have referred many url but didn't get proper solution for this.

The main reason for this issue is, we are not passing the data in the specified length

But in our actual code we will be sent the valid data, but the value will not be passed and will through the mentioned issue.

Here the trick is,

While creating data table for the table valued parameter, we need to create the column in the order we created in the table valued parameter.

Please check the following code.

Solution (The following will work)

C#

DataTable users= new DataTable("Users");
users.Columns.Add("EmailAddress", typeof(string));
users.Columns.Add("Content", typeof(string));

DataTable data= users.NewRow();
data["EmailAddress"] = emailAddress;
data["Content"] = content;

Sql

CREATE TYPE [dbo].[ParamEulaEmailUser] AS TABLE(
    [EmailAddress] [nvarchar](50) NOT NULL,
    [Content] [nvarchar](max) NULL
)

The following will not work

c#

DataTable users= new DataTable("Users");
users.Columns.Add("Content", typeof(string));
users.Columns.Add("EmailAddress", typeof(string));

The reason is here while we sending data to the stored procedure, the table valued parameter takes the value in the given order and match with existing column in the order. So the content will be checked with the email address in the stored procedure and throw the following error

Error : String or binary data would be truncated. The data for table-valued parameter doesn't conform to the table type of the parameter

Solution 2

You have not posted the declaration for MyDataTable user-defined type, but you should increase the varchar size of the Question column in the MyDataTable definition.

DROP TYPE [dbo].[MyDataTable]
GO

CREATE TYPE [dbo].[MyDataTable] AS TABLE
(
    [Question] [varchar](200) NULL --INCREASE THIS VALUE
)

Script a Drop and Create for the addquestion procedure, as well as the MyDataTable type.

Drop the stored proc, drop the MyDataTable type.

Edit the MyDataTable Create script as I mentioned, and run it, then create part for the stored proc.

Solution 3

The maximal length of the target column is shorter than the value you try to insert.

Rightclick the table in SQL manager and go to 'Design' to visualize your table structure and column definitions. increase column Length

Comments

[Sahebgouda Patil]

The length of the colo\umn is Question varchar(MAX)

[Panagiotis Kanavos]

The error complains about the TVP and the parameter value, not the table. It doesn't matter what the table's schema is.

[Lee]

Spent ages trying to work out why it wasn't working. Thanks +1

[Rejwanul Reja]

I think this is the right answer about this issue. and it is being working on my end

[SGRao]

In my case nvarchar data type length issue.

[Pranesh Janarthanan]

i too faced this error, found a column length is less than value length being passed. +1