String or binary data would be truncated. SQL Statements in C#

Question

SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Server=; Database=; User id=; password=";
conn.Open();

string Query = "Insert into [Capstone0480].[dbo].[NAME] (NameID, FirstName, MI, LastName, UserID) Values('" + this.txtNameID.Text + "','" + txtFirst.Text + "','" + txtMI.Text + "','" + txtLast.Text + "', '" + txtUserID.Text + "')";

SqlCommand createCommand = new SqlCommand(Query, conn);
createCommand.ExecuteNonQuery();


MessageBox.Show("Updated");
conn.Close();

I have been getting this error on the ExecuteNonQuery() statement. I am not sure what is wrong here. I feel as if my sql statements are correct. I am just trying to add what is typed into textboxes into my database.

If there is a better way of doing this or if there is something wrong, please let me know!

One of your textboxes contains a string that's too long for its column in the database. — Blorgbeard, Nov 24 '14 at 04:16
What's the type of `NameID`, `FirstName`, `MI`, `LastName`, and `UserID` columns? And what's the value of `txtNameID.Text`, `txtFirst.Text`, `txtMI.Text`, `txtLast.Text`, and `txtUserID.Text`? — ekad, Nov 24 '14 at 04:17
Not related to your question, but the way you are adding in parameters is ill advised. [See this](http://stackoverflow.com/questions/4624811/different-ways-of-passing-sqlcommand-parameters). I can explain more if you need help. — techvice, Nov 24 '14 at 04:32
@techvice Oh i know. Its susceptible to sql injection. If this were actually important, i wouldnt be using parameters the way I am. Thank you though! — Travis, Nov 24 '14 at 04:36
this has been asked and answered many times i.e. http://stackoverflow.com/questions/5591473/error-string-or-binary-data-would-be-truncated-when-trying-to-insert — Guy Lowe, Nov 24 '14 at 04:49

Scott Ferguson · Answer 1 · 2014-11-24T04:22:37.817

One of the columns in your NAME table is shorter than the values you are trying to insert into it. Try trimming the length of your name textboxes before you insert them into the database, or alternatively increase the length of your database columns.

For example:

If your FirstName Column is varchar(20) then:

var firstName = txtFirst.Text.Length > 20 ? txtFirst.Text.Substring(0,20) : txtFirst.Text;

then insert the value of firstName into your SQL statement.

In addition, you should set the max length of your Textbox to be no more than the size of your columns.

score 0 · Answer 2 · answered Nov 24 '14 at 05:25

The error suggest, that your input data length is more than the column length you define in database table.

suppose the column is like

TableName1
ColumnName1 varchar(50)

and now when you want to insert 51 or more character length input string, at that time the Sqlserver gives error. check this sample example.

declare @t table (name varchar(5))

insert into @t values('abc')

select * from @t

insert into @t values('abcdefg') --this gives error as you insert more than define length.

Please check at run-time what is the input string.

Good if you restrict your textbox to insert only the database column length like

<input type="textbox" maxlength="50" />

String or binary data would be truncated. SQL Statements in C#

2 Answers2