nvarchar 值“3001822585"的转换溢出了 int 列 [英] Conversion of the nvarchar value '3001822585' overflowed an int column

问题描述

我使用以下将 Excel 文件导入 SQL Server.

I use following to import Excel file to SQL Server.

Excel 文件的所有值都是字符串.我可以导入除 Barcode、SalePrice 和 Price2 之外的文件.我收到错误

The Excel file has all the values as string. I am able to import the file except Barcode, SalePrice and Price2. I get error

转换 nvarchar 值 '3001822585'(barcode) 溢出了 int 列

Conversion of the nvarchar value '3001822585'(barcode) overflowed an int column

代码:

         SqlCommand sqlcmd = new SqlCommand
         (@"MERGE Inventory AS target
         USING (SELECT
            LocalSKU, ItemName, QOH, Price, Discontinued,Barcode, Integer2
            ,Integer3, SalePrice, SaleOn, Price2 FROM @source)
            AS Source ON (Source.LocalSKU = target.LocalSKU)
           WHEN MATCHED THEN
           UPDATE 
           SET ItemName = source.ItemName,
           Price = source.Price,
           Discontinued = source.Discontinued,
           Barcode = source.Barcode,
           Integer2 = source.Integer2,
           Integer3 = source.QOH,
           SalePrice = source.SalePrice,
           SaleOn = source.SaleOn,
           Price2 = source.Price2;", sqlconn);

           SqlParameter param;
           param = sqlcmd.Parameters.AddWithValue("@source", dr);
           param.SqlDbType = SqlDbType.Structured;
           param.TypeName = "dbo.InventoryType";

           sqlconn.Open();
           sqlcmd.ExecuteNonQuery();
           sqlconn.Close();

数据库::

 LocalSKU varchar(200),
 ItemName varchar(200),
 QOH int,
 Price decimal(19,4),
 Discontinued bit,
 Barcode int,
 Integer2 int,
 Integer3 int,
 SalePrice decimal(19,4),
 SaleOn bit,
 Price2 decimal(19,4)

dbo.InventoryType 是:

   CREATE TYPE [dbo].[InventoryType] AS TABLE
   (
      [LocalSKU] [varchar](200) NOT NULL,
      [ItemName] [varchar](200) NULL,
      [QOH] [int] NULL,
      [Price] [decimal](19, 4) NULL,
      [Discontinued] [bit] NULL,
      [Barcode] [varchar](25) NULL,
      [Integer2] [int] NULL,
      [Integer3] [int] NULL,
      [SalePrice] [decimal](19, 4) NULL,
      [SaleOn] [bit] NULL,
      [Price2] [decimal](19, 4) NULL
   )
   GO

如何转换表值参数中的数据类型?示例将不胜感激.

How to cast the datatype in table valued parameter? Example will be appreciated.

推荐答案

使用 unsignedInt 数据类型.

Use unsignedInt datatype.

int range - 4 bytes, -2,147,483,648 to +2,147,483,647 
unsignedInt range - 0 to 4,294,967,295 

您的条码值 3001822585 超过最大整数值 2,147,483,647，因此引发错误

Your barcode value 3001822585 is exceeding max int value 2,147,483,647 and so throws error

编辑

unigned int 在 SQL Server 中找不到，但 MySQL 支持它.对于 SQL Server，您必须使用 bigINT

unigned int are not found in SQL Server , however MySQL supports it. For SQL Server you will have to use bigINT

bigInt range - 8 bytes -2^63 to 2^63-1 

因此将 Barcode 列的数据类型更改为 bigInt .如果您认为其他列值可以超过 int range ，那么也更改它们的数据类型.

So change data type of Barcode column to bigInt . If you think other column values can exceed int range , then change their datatype also.

这篇关于nvarchar 值“3001822585"的转换溢出了 int 列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！