数据库插入错误：“字符串或二进制数据将被截断”

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:

当我登录时，我将我的用户名存储在会话中。我的要求是我想将我的用户名存储在我的数据库中。这里我将它存储在username1中。输入用户名后，我可以使用response.write（）打印它，并且打印效果很好。但是，当我将其存储在数据库中时，它会产生以下错误：

**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:

以下是我的ado.net代码：

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

3 个解决方案

#1

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

检查qry_details表的长度，看看它是否小于你发送给db的字符串？

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

基本上例外，你说你正在尝试比列长更大的东西。

#2

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:

我建议你使用参数化查询。您的代码现在容易受到SQL注入攻击。在将值插入数据库时，您还应该在SQL命令上使用ExecuteNonQuery方法而不是ExecuteScalar：

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

#3

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

当插入值大于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.

检查是否使用DateTime.Now c＃fuction插入日期和时间，您的表必须是DateTime类型。不仅仅是日期或时间。

#1