Conversion From nvarchar value to to data type int failed?

1

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);

            }

enter image description here

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?

c#
sql
json
sql-server
datatable
asked on Stack Overflow Feb 8, 2019 by chobo2 • edited Feb 8, 2019 by Hadi

1 Answer

3

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");
answered on Stack Overflow Feb 8, 2019 by Hadi

User contributions licensed under CC BY-SA 3.0