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.

Answer 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