TVP does not conform to table type

Per the error, your data does not conform to the table type exactly. Note "exactly" -- if you do not specify types for the columns, they will be inferred, and they can easily be inferred incorrectly. The best approach here is to create a table that you know matches the table type definition:

var dt = new DataTable();
dt.Columns.Add("firstName", typeof(string)).MaxLength = 100;
dt.Columns.Add("lastName", typeof(string)).MaxLength = 100;
dt.Columns.Add("studentId", typeof(string)).MaxLength = 10;
dt.Columns.Add("email", typeof(string)).MaxLength = 100;
dt.Columns.Add("barcode", typeof(string)).MaxLength = 100;
dt.Columns.Add("dob", typeof(string)).MaxLength = 200;
dt.Columns.Add("major", typeof(string)).MaxLength = 200;
dt.Columns.Add("gender", typeof(string)).MaxLength = 200;
dt.Columns.Add("classCode", typeof(string)).MaxLength = 15;
dt.Columns.Add("currentclassCode", typeof(string)).MaxLength = 15;
dt.Columns.Add("entranceCode", typeof(string)).MaxLength = 15;
dt.Columns.Add("attendeeId", typeof(Guid));

And then use .Clone() to create a new table with the correct schema when you need to insert data. This way, if you have a type or length mismatch, it will be caught on the client end.

There is another approach you can take that does not rely on embedding the table definition into the application, which is fetching it from the database. There are pros and cons to this -- it requires an extra roundtrip to the database and it's not as easy to spot mistakes in the application logic if the types or columns don't match, but it does give you additional flexibility to change the type without having to change the application (adding a new, nullable column, for example).

var dt = new DataTable();
using (var connection = new SqlConnection(...)) {
    connection.Open();
    using (var command = new SqlCommand()) {
        command.Connection = connection;
        command.CommandText = "DECLARE @t dbo.AttendeeTableType; SELECT * FROM @t;"
        using (var reader = command.ExecuteReader()) {
            dt.Load(reader);
        }
    }
}

Obviously you probably want to cache the results of this and .Clone(), rather than doing it for every command involving the table type parameter.

I reached this page while searching for similar issue that I was experiencing, but none of the replies did help me. After some head beating I found that the error is being generated in case when the data table being passed from the code has some data that does not match the TVP type specification.

For example if you defined the following type:

CREATE TYPE EmployeeType AS TABLE 
(
    EmpID BigInt, EmpName VARCHAR(100)
)

and suppose the data table that you are passing has (say) one EmpName that has more than 100 characters then "*** does not conform to table type" error is generated.

This solved my issue. Hope it will help others as well.

TVP does not conform to table type

Related

Recent Posts