字符串或二进制数据将被截断.该语句已终止.在数据库表中插入记录时 [英] String or binary data would be truncated. The statement has been terminated. while inserting records in DB table

问题描述

我的用户定义数据类型如下

My user-defined data type is as below

CREATE TYPE [dbo].[CdeSecureList] AS TABLE
(
    [CardRefID] [int] NOT NULL,
    [KEY] [varchar](250) NOT NULL,
    [MIS_Field1] [varchar](60) NULL,
    [MIS_Field2] [varchar](60) NULL,
    [ValidatingCarrier] [varchar](2) NOT NULL,
    [OtherCarriers] [bit] NOT NULL,
    [Card Vendor] [varchar](2) NOT NULL,
    [Card] [varchar](266) NOT NULL,
    [Expiration] [date] NOT NULL,
    [Precedence] [int] NULL,
    [AlertEmail] [varchar](80) NULL,
    [maxTktAmt] [decimal](18, 2) NOT NULL,
    [IRP_remark] [bit] NOT NULL
)

在C#代码中，我创建了一个datable并确保数据类型与我的SQL表匹配

and in my C# code, I created a datable and made sure the data type matches with my SQL table

DataTable dtCloned = dtExcel.Clone();

try
{            
    dtCloned.Columns[0].DataType = typeof(Int32);
    dtCloned.Columns[1].DataType = typeof(string);
    dtCloned.Columns[2].DataType = typeof(string);
    dtCloned.Columns[3].DataType = typeof(string);
    dtCloned.Columns[4].DataType = typeof(string);
    dtCloned.Columns[5].DataType = typeof(Boolean);
    dtCloned.Columns[6].DataType = typeof(string);
    dtCloned.Columns[7].DataType = typeof(string);
    //dtCloned.Columns[8].DataType = typeof(DateTime);
    dtCloned.Columns[9].DataType = typeof(Int32);
    dtCloned.Columns[10].DataType = typeof(string);
    dtCloned.Columns[11].DataType = typeof(decimal);
    dtCloned.Columns[12].DataType = typeof(Boolean);

    foreach (DataRow row in dtExcel.Rows)
    {
        dtCloned.ImportRow(row);
    }
}

但是在插入记录时，我遇到了错误

Yet while inserting the record, I am getting an error

字符串或二进制数据将被截断.该声明已终止

String or binary data would be truncated. The statement has been terminated

我了解此错误的意思

原因:在表中的某列为VARCHAR或CHAR数据类型且要插入的值的长度大于该列的长度的表中插入记录时，通常会遇到此错误.

Causes: This error is usually encountered when inserting a record in a table where one of the columns is a VARCHAR or CHAR data type and the length of the value being inserted is longer than the length of the column.

但是我确保数据类型相同，所以为什么会出现错误.

But I made sure the data type is same, so why am I getting the error.

我的假设是代码中的第5行和第12行具有布尔值(两种情况下的内容均为True)，但是我的SQL数据类型是bit，这有问题吗?我该如何解决?

My assumption is row 5 and row 12 in my code is having boolean value (the content is True in both the case), but my SQL data type is bit, is it a problem? How can I fix this?

推荐答案

问题出在字符串，而不是布尔值.有多种可能的原因:

The problem lies with the strings, not the booleans. There are a number of possible causes:

1. 您可能正在阅读Unicode文本-在这种情况下，应使用 NVarChar 而不是 VarChar .
2. 其中一个字符串比您为其分配的字段长.您需要检查数据以确保可以将其存储在可用空间中.

您需要检查数据以查看适用的数据.

You'll need to check the data to see which one applies.

这篇关于字符串或二进制数据将被截断.该语句已终止.在数据库表中插入记录时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！