Database insert error: "string or binary data would be truncated"

Question

When I log in, I am storing my username in the session. My requirement is that I want to store my username in my database. Here I am storing it in username1. When the username is entered, I can print it using response.write() and it is printing perfectly. However, when I am storing it in the database it is producing this error:

**sqlException was unhandled by user code
and exception at       cmd.ExecuteScalar();
String or binary data would be truncated.
The statement has been terminated.**

Following is my ado.net code:

using (SqlConnection con = 
    new SqlConnection("Data Source=.;database=testdb1;Integrated Security=SSPI")) {

    con.Open();
    //  SqlCommand cmd = new SqlCommand("delete from fileinfo where ID=" + Convert.ToInt32(Request.Params["one"]), con);                            

    string uname = (string) Session["fname"].ToString() + " " + Session["lname"].ToString(); //Session["fname"].ToString()+" "+Session["lname"].ToString();

    // Response.Write(uname);
    // uname = "sri hari";
    uname = uname + " ";
    string uname1 = uname;
    uname = uname.Trim();
    SqlCommand cmd = new SqlCommand("insert into qry_details values('" + txt_query_name.Text + "','pending for approval','" + txt_query_description.Text + "','" + DateTime.Now.ToString("yyyy-MM-dd") + "','" + qry + "','" + uname1 + "')", con);
    cmd.ExecuteScalar();
}

What is the data type of the column into which you are trying to insert the username? The value stored in uname is longer than the length of this column. — Simon MᶜKenzie
– Simon MᶜKenzie, Commented Jun 26, 2013 at 6:23
@srihari: you need to check your table schemas, datatypes and query like INSERT INTO table_name (column1,column2,column3,...) VALUES (@value1,@value2,@value3,...); you might be facing column mismatch — Satinder singh
– Satinder singh, Commented Jun 26, 2013 at 6:24

Aneef · Accepted Answer · 2013-06-26 06:04:07Z

26

check the length of qry_details table and see if its smaller than the string you send to the db?

basically the exception says you are trying to something bigger than the column length.

answered Jun 26, 2013 at 6:04

Aneef

3,73910 gold badges49 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

srihari Over a year ago

but if asigning string value directly it is inserting like uname="srihari" if iam inserting with session it is giving exception

srihari Over a year ago

i gave uname value 50 length

Simon MᶜKenzie Over a year ago

@srihari, then what is the value of uname.Length?

Darin Dimitrov · Accepted Answer · 2013-06-26 06:04:04Z

10

I would recommend you using a parametrized query. Your code is now vulnerable to SQL injection. Also you should use the ExecuteNonQuery method on the SQL command instead of ExecuteScalar when inserting values to the database:

var connectionString = "Data Source=.;database=testdb1;Integrated Security=SSPI";
using (SqlConnection con = new SqlConnection(connectionString))
using (SqlCommand cmd = con.CreateCommand())
{
    con.Open();
    cmd.CommandText = "INSERT INTO qry_details VALUES (@query_name, 'pending for approval', @query_description, @date, @qry, @username)";
    cmd.Parameters.AddWithValue("@query_name", txt_query_name.Text);
    cmd.Parameters.AddWithValue("@query_description", txt_query_description.Text);
    cmd.Parameters.AddWithValue("@date", DateTime.Now);
    cmd.Parameters.AddWithValue("@qry", qry);
    cmd.Parameters.AddWithValue("@username", uname1);
    cmd.ExecuteNonQuery();
}

answered Jun 26, 2013 at 6:04

Darin Dimitrov

1.0m275 gold badges3.3k silver badges3k bronze badges

4 Comments

srihari Over a year ago

string uname 1= (string)Session["fname"].ToString() + " " + Session["lname"].ToString();

Simon MᶜKenzie Over a year ago

But what about the "string or binary data would be truncated" error?

srihari Over a year ago

then also same exception is coming.exception is String or binary data would be truncated. The statement has been terminated.

srihari Over a year ago

it is giving at cmd.ExecuteNonQuery();

Akhtar ali · Accepted Answer · 2016-09-17 09:58:30Z

1

This error mostly happen when the inserting value is larger than the field width defined in table on SQL Server.

Check if you are inserting date and time using DateTime.Now c# fuction, your Table must be of type DateTime. not Date or Time only.

answered Sep 17, 2016 at 9:58

Akhtar ali

111 bronze badge

Collectives™ on Stack Overflow

Database insert error: "string or binary data would be truncated"

3 Answers 3

3 Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related