CODEWITHSUNDEEP
vb.netsql-server-2008ado.nettable-valued-parameters
Raja
Raja

Reputation: 3618

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

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.

Upvotes: 4

Views: 5907

Answers (4)

isxaker
isxaker

Reputation: 9446

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
}

Upvotes: 8

RickNZ
RickNZ

Reputation: 18654

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 ... AS ...

How is your DataTable configured? It should include something like:

yourDataTable.Columns.Add("columnName", typeof(datetime2));

Upvotes: 1

Türk Yılmaz Özkan
Türk Yılmaz Özkan

Reputation: 41

It's SQL fault, table types insert without column in .NET. You should create your same order with your table type. As you can see on sample.

I spend two day for this.

declare @p1 dbo.typeSoftCopyDocument
insert into @p1 values(275,491196,N'000001.tif',1,100,5330900910,'2018-09-06 14:49:18',111111,N'xxx',N'xxxx')

Upvotes: 4

panky sharma
panky sharma

Reputation: 2159

Sometime it occurred if table valued parameter not passed as per SQL TVP column order.

It should have same ordering in .NET & SQL 

[id] [int] NULL,
[appStatus] [varchar](10) NULL,
[lodgedBy] [varchar](10) NULL,

.NET 

objSearch = new AppSearchEN();
objSearch.id= context.Request["AppCat"].ToNullableInteger();
objSearch.appStatus= context.Request["AppStatus"] == "0" ? null : context.Request["AppStatus"];
objSearch.lodgedBy= context.Request["lodgedBy"] == "0" ? null : context.Request["lodgedBy"];

Then While passing parameter in .NET it must have same order of the parameter to table valued fn

Upvotes: 2

Related Questions