SQL Server Table Column Column is not supported. Object type

I have to ask this because it worries me and I cannot find the specific source of this problem. I know how to use TVP (Table Parameter) in Sql Server and I am using it for batch inserts in my stored procedure. So basically I create table types in Sql with corresponding columns / fields for the table I am going to insert records into and it works fine for me from code (C #) to sql stored procedure, now here is my problem: recently when users try inserting multiple records, there are certain random fields in the table parameter that give such an error message when trying to execute a stored procedure (mostly in C # because it throws some error).

Column type {field} 'is not supported. Object type

In each case, the field in the error message changes, so I know the problem is in the data, but I don't know what it is. By the way, I am passing data from my source to a datatable to be used as a parameter for the insert:

dr["{field_name}"] = {value}

      

Update: I want to go into more detail on how to wrap values โ€‹โ€‹into data. Basically, I will try to loop through all rows in the datatable through

table.Rows.Cast<DataRow>().ToList().ForEach( action => {statements})

      

then inside foreach I assign

action["{field_name}"] = action["{field_name}"].{formatting_function}

      

I need this to format the data before inserting it into the database for example. to convert to uppercase or lowercase, or to convert to specific types, for example. for boolean.

+3


source to share


2 answers


The table you pass as an argument is not "column-compatible" with the expected table value parameter.

The type ('Object') that you pass for the column ('{field}') in the table value parameter is incompatible with the type of the corresponding column.



When you create your table value, make sure all column types are compatible with the expected parameter.

More details here: Table Parameters

+1


source


It seems that the error is to use the formatting features, such as ToUpper()

, ToLower()

on {value}

.

These functions expect the type to be significant string

, but the type {value}

is Object

.



So use ToString()

as below:

{value}.ToString().ToLower().

      

+1


source







All Articles