C# SQLException : 字符串或二进制数据将被截断
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/779082/
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
SQLException : String or binary data would be truncated
提问by
I have a C# code which does lot of insert statements in a batch. While executing these statements, I got "String or binary data would be truncated" error and transaction roledback.
我有一个 C# 代码，它批量执行大量插入语句。在执行这些语句时，我收到了“字符串或二进制数据将被截断”错误和事务回退。
To find out the which insert statement caused this, I need to insert one by one in the SQLServer until I hit the error.
要找出是哪个插入语句导致了这个，我需要在 SQLServer 中一一插入，直到我遇到错误为止。
Is there clever way to findout which statement and which field caused this issue using exception handling? (SqlException)
有没有聪明的方法来使用异常处理找出哪个语句和哪个字段导致了这个问题？(SqlException)
回答by Adam Robinson
In general, there isn't a way to determine which particular statement caused the error. If you're running several, you could watch profiler and look at the last completed statement and see what the statement after that might be, though I have no idea if that approach is feasible for you.
通常，没有办法确定哪个特定语句导致了错误。如果您正在运行多个，您可以查看分析器并查看最后完成的语句，看看之后的语句可能是什么，尽管我不知道这种方法对您是否可行。
In any event, one of your parameter variables (and the data inside it) is too large for the field it's trying to store data in. Check your parameter sizes against column sizes and the field(s) in question should be evident pretty quickly.
无论如何，您的参数变量之一（以及其中的数据）对于它尝试存储数据的字段来说太大了。根据列大小检查您的参数大小，并且相关字段应该很快就显而易见了。
回答by CodeMonkey1313
It depends on how you are making the Insert Calls. All as one call, or as individual calls within a transaction? If individual calls, then yes (as you iterate through the calls, catch the one that fails). If one large call, then no. SQL is processing the whole statement, so it's out of the hands of the code.
这取决于您如何进行插入调用。全部作为一个调用，还是作为事务中的单个调用？如果单个调用，则是（当您遍历调用时，捕获失败的调用）。如果一个大电话，那么没有。SQL 正在处理整个语句，因此它不受代码控制。
回答by KM.
BEGIN TRY
INSERT INTO YourTable (col1, col2) VALUES (@val1, @val2)
END TRY
BEGIN CATCH
--print or insert into error log or return param or etc...
PRINT '@val1='+ISNULL(CONVERT(varchar,@val1),'')
PRINT '@val2='+ISNULL(CONVERT(varchar,@val2),'')
END CATCH
回答by Jasmin Chauhan
This type of error occurs when the datatype of the SQL Server column has a length which is less than the length of the data entered into the entry form.
当 SQL Server 列的数据类型的长度小于输入到条目表单中的数据长度时，就会发生这种类型的错误。
回答by profMamba
- Get the query that is causing the problems (you can also use SQL Profiler if you dont have the source)
- Remove all WHERE clauses and other unimportant parts until you are basically just left with the SELECT and FROM parts
- Add WHERE 0 = 1 (this will select only table structure)
- Add INTO [MyTempTable] just before the FROM clause
- 获取导致问题的查询（如果没有源，也可以使用 SQL Profiler）
- 删除所有 WHERE 子句和其他不重要的部分，直到您基本上只剩下 SELECT 和 FROM 部分
- 添加 WHERE 0 = 1（这将仅选择表结构）
- 在 FROM 子句之前添加 INTO [MyTempTable]
You should end up with something like
你应该最终得到类似的东西
SELECT
Col1, Col2, ..., [ColN]
INTO [MyTempTable]
FROM
[Tables etc.]
WHERE 0 = 1
This will create a table called MyTempTable in your DB that you can compare to your target table structure i.e. you can compare the columns on both tables to see where they differ. It is a bit of a workaround but it is the quickest method I have found.
这将在您的数据库中创建一个名为 MyTempTable 的表，您可以将它与目标表结构进行比较，即您可以比较两个表上的列以查看它们的不同之处。这是一种解决方法，但它是我找到的最快的方法。
回答by sdjfhueryeiur
With Linq To SQL I debugged by logging the context, eg. Context.Log = Console.OutThen scanned the SQL to check for any obvious errors, there were two:
使用 Linq To SQL，我通过记录上下文进行调试，例如。Context.Log = Console.Out然后扫描SQL检查是否有明显错误，有两个：
-- @p46: Input Char (Size = -1; Prec = 0; Scale = 0) [some long text value1]
-- @p8: Input Char (Size = -1; Prec = 0; Scale = 0) [some long text value2]
the last one I found by scanning the table schema against the values, the field was nvarchar(20) but the value was 22 chars
我通过根据值扫描表模式找到的最后一个，该字段是 nvarchar(20) 但该值是 22 个字符
-- @p41: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [1234567890123456789012]
-- @p41: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [1234567890123456789012]
回答by jaideep
this type of error generally occurs when you have to put characters or values more than that you have specified in Database table like in that case: you specify transaction_status varchar(10) but you actually trying to store _transaction_status which contain 19 characters. that's why you faced this type of error in this code
这种类型的错误通常发生在您必须输入的字符或值多于您在数据库表中指定的字符或值时，例如在这种情况下：您指定 transaction_status varchar(10) 但您实际上尝试存储包含 19 个字符的 _transaction_status。这就是您在此代码中遇到此类错误的原因
回答by Fandango68
It could also be because you're trying to put in a nullvalue back into the database. So one of your transactions could have nulls in them.
也可能是因为您试图将一个null值放回到数据库中。因此，您的一项交易中可能包含空值。
回答by ismail baig
Generally it is that you are inserting a value that is greater than the maximum allowed value. Ex, data column can only hold up to 200 characters, but you are inserting 201-character string
通常是您插入的值大于允许的最大值。例如，数据列最多只能容纳 200 个字符，但您插入的是 201 个字符的字符串
回答by Ibrahim Mohammed
In our own case I increase the sql table allowable character or field size which is less than the total characters posted from the?front end. Hence that resolve the issue.
在我们自己的例子中，我增加了 sql 表允许的字符或字段大小，它小于从前端发布的总字符数。因此，解决了这个问题。
