SQL nvarchar 值“3001822585”的转换溢出了 int 列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23191472/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Conversion of the nvarchar value '3001822585' overflowed an int column
提问by user2525244
I use following to import Excel file to SQL Server.
我使用以下将 Excel 文件导入 SQL Server。
The Excel file has all the values as string. I am able to import the file except Barcode, SalePriceand Price2. I get error
Excel 文件将所有值作为字符串。我可以导入文件，除了Barcode,SalePrice和Price2. 我得到错误
Conversion of the nvarchar value '3001822585'(barcode) overflowed an int column
nvarchar 值 '3001822585'(barcode) 的转换溢出了 int 列
Code:
代码：
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();
Database::
数据库：：
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.InventoryTypeis:
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.
如何在表值参数中转换数据类型？示例将不胜感激。
回答by Mudassir Hasan
Use unsignedInt datatype.
使用 unsignedInt 数据类型。
int range - 4 bytes, -2,147,483,648 to +2,147,483,647
unsignedInt range - 0 to 4,294,967,295
Your barcode value 3001822585is exceeding max int value 2,147,483,647and so throws error
您的条形码值3001822585超过了最大整数值2,147,483,647，因此引发错误
Edit
编辑
unigned intare not found in SQL Server , however MySQL supports it.
For SQL Server you will have to use bigINT
unigned int在 SQL Server 中找不到，但 MySQL 支持它。对于 SQL Server，您将不得不使用bigINT
bigInt range - 8 bytes -2^63 to 2^63-1
So change datattype of Barcodecolumn to bigInt . If you think other column values can exceed int range , then change their datatype also.
因此将Barcode列的datattype 更改为 bigInt 。如果您认为其他列值可以超过 int range ，那么也更改它们的数据类型。
回答by Samiran Nath
SQl Statement:
SQl 声明：
SELECT max( Mother_ID)+1 FROM Mother
http://i.stack.imgur.com/ACKAW.jpgMsg 248, Level 16, State 1, Line 1 The conversion of the nvarchar value '182512900911500120' overflowed an int column.
http://i.stack.imgur.com/ACKAW.jpgMsg 248, Level 16, State 1, Line 1 nvarchar 值 '182512900911500120' 的转换溢出了一个 int 列。
The problem is caused due to the number 182512900911500120 exceeds int range. If we convert in to bigint than it works.
问题是由于数字 182512900911500120 超出 int 范围引起的。如果我们转换为 bigint，它会起作用。
i.e.
IE
SELECT max(convert(bigint,Mother_ID))+1 as MID FROM Mother
http://i.stack.imgur.com/ZLSNR.jpgResult 182512900911500121
http://i.stack.imgur.com/ZLSNR.jpg结果 182512900911500121
