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

Question

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.

Accepted Answer

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`.

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

Solution

Problem