CODEWITHSUNDEEP
c#asp.netsql-serverado.net
srihari
srihari

Reputation: 917

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

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();
}

Upvotes: 13

Views: 71134

Answers (3)

Akhtar ali
Akhtar ali

Reputation: 11

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.

Upvotes: 1

Aneef
Aneef

Reputation: 3729

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.

Upvotes: 26

Darin Dimitrov
Darin Dimitrov

Reputation: 1038710

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();
}

Upvotes: 10

Related Questions