错误：字符串或二进制数据将被截断。该语句已终止 [英] Error: String or binary data would be truncated. The statement has been terminated

问题描述

我试图创建一个简单的登记表，其中来自文本框等数据输入到一个名为用户表：

  USER_ID INT
用户名的nchar（20）
密码的nchar（20）
...更多列
 

code：

 昏暗的用户名作为字符串= txtUsername.ToString
...更多变量声明lblDOB.Text = DOB.ToString
lblDOB.Visible = TRUE昏暗ProjectManager作为字符串=测试
......更多的常量声明昏暗康恩作为新的SqlConnection（...康恩串......）sqlComm =INSERT INTO用户（用户名，密码，...等栏目...）+
    VALUES（@ P1，P2 @ ...等参数...）conn.Open（）
registerSQL =新的SqlCommand（sqlComm，康涅狄格州）
registerSQL.Parameters.AddWithValue（@ P1，用户名）
...其他AddWithValuesregisterSQL.ExecuteNonQuery（）
 

我收到此错误消息：

  

字符串或二进制数据将被截断。该语句已终止。

解决方案

是的，你让任何旧的字符串，人们在打字，你需要将每个参数的限制，以允许在该列的最大长度。例如，如果你不想要的错误，您可以截断用户名到20个字符时参数值是超过20个字符，你可以提高自己的错误。要截断，你可以简单地说：

 昏暗的用户名作为字符串= txtUsername.ToString.Substring（0,20）
 

或者更好的是，当你建立你的形式，指定最大长度为列在表中相匹配的表单域。为什么超过20个字符让用户类型，如果你的表仅设计容纳20？

顺便说一句， NCHAR 是大多数这类数据的一个可怕的数据的选择，除非用户名，密码等，将永远是整整20个字符。你应该认真考虑为nvarchar 。

I'm trying to create a simple registration form, where data from textboxes etc. are input into a table called users:

user_id     int
username    nchar(20)
password    nchar(20)
... more columns

Code:

Dim Username As String = txtUsername.ToString
... more variable declarations

lblDOB.Text = DOB.ToString
lblDOB.Visible = True

Dim ProjectManager As String = "test"
... more constant declarations

Dim conn As New SqlConnection("...conn string...")

sqlComm = "INSERT INTO users(Username, Password, ...other columns...)" +
    "VALUES(@p1, @p2, ...other params...)"

conn.Open()
registerSQL = New SqlCommand(sqlComm, conn)
registerSQL.Parameters.AddWithValue("@p1", Username)
... other AddWithValues

registerSQL.ExecuteNonQuery()

I'm getting this error message:

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

解决方案

Yes, you are allowing any old string that people are typing in. You need to constrain each parameter to the maximum length allowed in that column. For example, if you don't want the error, you can truncate username to 20 characters or you can raise your own error when that parameter value is more than 20 characters. To truncate you could simply say:

Dim Username As String = txtUsername.ToString.Substring(0,20)

Or better yet, when you build your form, specify a MaxLength for the form field that matches the column in your table. Why let a user type in more than 20 characters if your table is only designed to hold 20?

As an aside, nchar is a terrible data choice for most of this data, unless usernames, passwords, etc. will always be exactly 20 characters. You should strongly consider nvarchar.

这篇关于错误：字符串或二进制数据将被截断。该语句已终止的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！