ADO.Net Table-Valued Parameter (TVP): Operand type clash: datetime2 is incompatible with int

Question

I am working with a TVP, and I am trying to pass a data table to the stored procedure as a TVP. When the command tries to ExecuteNonQuery(), it throws an error:

Operand type clash: datetime2 is incompatible with int. The data for table-valued parameter "@tvpPermitWork" doesn't conform to the table type of the parameter.

I checked the data table using the visualizer, and I've found all of the data to be correct. I am now stuck and I don't have the time to change it to stored procedures with individual parameters.

Any suggestions on how to fix this is greatly appreciated.

Answer 1

I've found the solution for this issue.

You need to check the matching columns' order in your TVP and C# code.

For example, if you have the TVP like this:

CREATE TYPE [dbo].[tvp_FinDocContract] AS TABLE(
    [column_1_Id] [uniqueidentifier] NOT NULL,
    [column_2_Id] [uniqueidentifier] NULL,
    [column_3_Id] [datetime] NULL
)

Then the C# code for creating the TVP must be like this:

DataTable tvp = new DataTable();

// The column order is very important
tvp.Columns.Add(new DataColumn("column_1_Id", typeof (Guid)){AllowDBNull = false};
tvp.Columns.Add("column_2_Id", typeof (Guid));
tvp.Columns.Add("column_3_Id", typeof (DateTime));

foreach (var item in ...)
{
    //just populating
}