Brian Brian - 2 years ago 104 Question

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

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


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


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...)"

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


I'm getting this error message:

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

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download