ADO.Net Table valued parameter (TVP) - Operand type clash: datetime2 is incompatible with int
I am working with TVP and I am trying to pass a data table to the stored procedure as 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 find all 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.
It's hard to know exactly what's happening without seeing your code, but as a quick guess, have you set SqlParameter.SqlDbType = SqlDbType.Structured, and SqlParameter.TypeName = "YourTableType"?
If so, what does the generated T-SQL look like (you can see it with SQL Profiler)?
How is your table type declared? -- CREATE TYPE YourTableType as TABLE ( ... )
How is your stored procedure declared? -- CREATE PROC ... @arg YourTableType READONLY ...
How is your DataTable configured? It should include something like: