I have to ask this because it bothers me and I can't find the specific source of this problem. I know how to use TVP (table valued parameter) in Sql Server and I'm using it for batch inserts in my stored procedure. So basically I create table types in Sql with the corresponding columns/fields for the table I'm going to insert records in and it works fine for me from the code (C#) to sql stored procedure, now here's my problem: recently when users try to insert multiple records, there are certain random fields in the table parameter that give this kind of error message when it tries to execute the stored procedure (basically in C# because it throws some error).
The type of column '{field}' is not supported. The type is 'Object'
On every occurrence the field in the error message changes so I know that the problem is in the data but I don't know what it is. BTW this is how I transfer data from my source to the datatable that will be used as a parameter for the insert:
dr["{field_name}"] = {value}
Update: I want to elaborate more on how I tranfer values to the datatable. Basically I will try to loop through all the rows in the datatable via
table.Rows.Cast<DataRow>().ToList().ForEach( action => {statements})
then inside the foreach, I assign
action["{field_name}"] = action["{field_name}"].{formatting_function}
I need to to this to format the data before it is inserted into the database, eg. to transfer to upper case or lower case or convert to specific types eg. to boolean.
The table you are passing as argument is not "column-compatible" with the expected table value parameter.
The type ('Object') you are passing for the column ('{field}') in your table value parameter is not compatible with the type of the corresponding column.
When you construct your table value, make sure all column types are compatible with the expected parameter.
See more here: Table-Valued Parameters