c＃＆amp; SQL Server Insert executenOnquery：哪个列会截断“字符串或二进制数据”？

发布于 2025-02-01 10:33:24 字数 1582 浏览 4 评论 0原文

我正在尝试将其插入C＃程序中的表格。我有此插入命令：

var insertSql = @"INSERT INTO dbo.[Case]
                  VALUES (@Id, @IsDeleted, @CaseNumber, @ContactId, @AccountId, @ParentId, @SuppliedName, @SuppliedEmail, @SuppliedPhone, @SuppliedCompany, @Type, @RecordTypeId, @Status, @Reason, @Origin...

然后我有很多行添加了类似的参数：

var command = new SqlCommand(insertSql, easySoftConn);

if (case2.Id != null)
    command.Parameters.AddWithValue("@Id", case2.Id);
else
    command.Parameters.AddWithValue("@Id", DBNull.Value);

if (case2.IsDeleted != null)
{
    if (case2.IsDeleted == "true")
        command.Parameters.AddWithValue("@IsDeleted", 1);
    else
        command.Parameters.AddWithValue("@IsDeleted", 0);
}
else
    command.Parameters.AddWithValue("@IsDeleted", DBNull.Value);

if (case2.CaseNumber != null)
    command.Parameters.AddWithValue("@CaseNumber", case2.CaseNumber);
else
    command.Parameters.AddWithValue("@CaseNumber", DBNull.Value);

if (case2.ContactId != null)
    command.Parameters.AddWithValue("@ContactId", case2.ContactId);
else
    command.Parameters.AddWithValue("@ContactId", DBNull.Value);       
...

当我最终执行插入物时：

try
{
    command.ExecuteNonQuery();
}
catch (System.Data.SqlClient.SqlException e)
{
    CLog.Write(e.Message.ToString(), CLog.ErrLvl.Error);...
}

我得到错误：

字符串或二进制数据将被截断

我的问题是，错误不会告诉我哪个列将被截断。我已经插入了80列，我宁愿一对一地浏览它们。有没有办法获取错误处理来确切告诉我哪个字段抛出了错误？

编辑：我在日志文件中有一个完整的堆栈跟踪，但它仍然没有告诉我哪个列，我只是将其缩短到这里的实际错误。

原文

I'm trying to insert into a table in a C# program. I have this insert command:

var insertSql = @"INSERT INTO dbo.[Case]
                  VALUES (@Id, @IsDeleted, @CaseNumber, @ContactId, @AccountId, @ParentId, @SuppliedName, @SuppliedEmail, @SuppliedPhone, @SuppliedCompany, @Type, @RecordTypeId, @Status, @Reason, @Origin...

And then I've got many lines adding in the parameters like so:

var command = new SqlCommand(insertSql, easySoftConn);

if (case2.Id != null)
    command.Parameters.AddWithValue("@Id", case2.Id);
else
    command.Parameters.AddWithValue("@Id", DBNull.Value);

if (case2.IsDeleted != null)
{
    if (case2.IsDeleted == "true")
        command.Parameters.AddWithValue("@IsDeleted", 1);
    else
        command.Parameters.AddWithValue("@IsDeleted", 0);
}
else
    command.Parameters.AddWithValue("@IsDeleted", DBNull.Value);

if (case2.CaseNumber != null)
    command.Parameters.AddWithValue("@CaseNumber", case2.CaseNumber);
else
    command.Parameters.AddWithValue("@CaseNumber", DBNull.Value);

if (case2.ContactId != null)
    command.Parameters.AddWithValue("@ContactId", case2.ContactId);
else
    command.Parameters.AddWithValue("@ContactId", DBNull.Value);       
...

When I finally execute the insert:

try
{
    command.ExecuteNonQuery();
}
catch (System.Data.SqlClient.SqlException e)
{
    CLog.Write(e.Message.ToString(), CLog.ErrLvl.Error);...
}

I get the error:

String or binary data would be truncated

My issue is, the error doesn't tell me which column would be truncated. I've got 80 columns I'm inserting into, and I'd rather not go through them one-by-one. Is there a way to get the error handling to tell me exactly which field is throwing the error?

EDIT: I have a full stack trace in my log file but it still doesn't tell me which column, I just shortened it to the actual error here.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

云朵有点甜 2025-02-08 10:33:24

切换到使用强键入的数据访问将更快地关注：

将数据集文件添加到您的项目中
，右键单击“表面”，添加tableadapter，设置连接参数，添加的查询> select * select *从[case] < /code>
完成向导，生成数据表和适配器。数据库用于驱动创建，因此所有字符串列在数据集中具有最大属性，该数据集来自DB
试图在此表中添加一行的数据集，现在会导致“无法设置XYZ列，值违反该值列的最大限制“

数据访问代码看起来像：

var dt = new YourDataSetNameHere.CaseDataTable();
dt.AddCaseRow(put, your, values, here, you , dont, need, to worry, about, null, this, or, data, type, that, because, VS, handles, it, all, for, you, in, the, DataSet.Designer.cs, file);

new YourDataSetNameHereTableAdapters.CaseTableAdapter().Update(dt); //save the new row;

它也可以节省您的时间，编写无聊的数据访问代码

Switching to using strongly typed data access would head this one off sooner:

Add a dataset file to your project
Open it, right click the surface, add tableadapter, set connection parameters, add a query of SELECT * FROM [Case]
FInish the wizard, a datatable and adapter are generated. The DB is used to drive the creation, so all the string columns have a MaxLength property in the dataset that comes from the DB
Attempting to add a row to this table will now cause an error like "unable to set column XYZ, the value violates the MaxLength limit for the column"

Data access code looks like:

var dt = new YourDataSetNameHere.CaseDataTable();
dt.AddCaseRow(put, your, values, here, you , dont, need, to worry, about, null, this, or, data, type, that, because, VS, handles, it, all, for, you, in, the, DataSet.Designer.cs, file);

new YourDataSetNameHereTableAdapters.CaseTableAdapter().Update(dt); //save the new row;

So it'll save you a boatload of time writing boring data access code too

回复收藏 0 原文