I have this Table Type
CREATE TYPE ItemTableType AS TABLE
(
BrandId INT,
BranchId INT,
InventoryCategoryId INT,
Attributes nvarchar(Max),
Price Decimal(18,2),
[Description] nvarchar(Max)
);
I create a datatable in my C# code like this
var dt = new DataTable();
dt.Columns.Add("BrandId");
dt.Columns.Add("BranchId");
dt.Columns.Add("attributes");
dt.Columns.Add("InventoryCategoryId");
dt.Columns.Add("Price");
dt.Columns.Add("Description");
foreach (var item in inventoryItems)
{
DataRow r = dt.NewRow();
r["BrandId"] = item.BrandId;
r["BranchId"] = item.BranchId;
r["InventoryCategoryId"] = item.InventoryCategoryId;
r["attributes"] = item.Attributes;
r["Price"] = item.Price;
r["Description"] = item.Description;
dt.Rows.Add(r);
}
when I try to pass my dt into my SP I get
{System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting the nvarchar value '{"PartNumber":"XA1167","Qty":999,"CleanedPartNumber":"1167"}' to data type int.
The data for table-valued parameter "@Source" doesn't conform to the table type of the parameter. SQL Server error is: 245, state: 1
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at line 202
ClientConnectionId:7a91a363-e5a0-4476-a92f-d02c30262728
Error Number:245,State:1,Class:16}
why is it thinking it is an Int when?
It is clear that the third column in the type you created InventoryCategory
is Int
while the datatable third column is varchar
. You must reorder the columns in the datatable.
While adding columns use:
dt.Columns.Add("InventoryCategoryId");
dt.Columns.Add("attributes");
User contributions licensed under CC BY-SA 3.0