代码之家  ›  专栏  ›  技术社区  ›  chobo2

从nvarchar值到数据类型int的转换失败?

  •  1
  • chobo2  · 技术社区  · 7 年前

    CREATE TYPE ItemTableType AS TABLE   
    ( 
      BrandId INT, 
      BranchId INT,
      InventoryCategoryId INT,
      Attributes nvarchar(Max),
      Price Decimal(18,2),
      [Description] nvarchar(Max)
    ); 
    

    我在C代码中创建了一个数据表,如下所示

     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

    当我尝试将dt传递到SP时,我得到

    {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}
    

    1 回复  |  直到 7 年前
        1
  •  3
  •   Hadi    7 年前

    很明显,您创建的类型中的第三列 InventoryCategory 是 Int 而datatable的第三列是 varchar

    添加列时,请使用:

    dt.Columns.Add("InventoryCategoryId");
    dt.Columns.Add("attributes");