C# 数据库插入错误：“字符串或二进制数据将被截断”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17312558/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Database insert error: "string or binary data would be truncated"
提问by srihari
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();
}
采纳答案by Aneef
check the length of qry_details table and see if its smaller than the string you send to the db?
检查 qry_details 表的长度，看看它是否小于您发送到数据库的字符串？
basically the exception says you are trying to something bigger than the column length.
基本上例外是说你正在尝试比列长度更大的东西。
回答by Darin Dimitrov
I would recommend you using a parametrized query. Your code is now vulnerable to SQL injection. Also you should use the ExecuteNonQuerymethod on the SQL command instead of ExecuteScalarwhen inserting values to the database:
我建议您使用参数化查询。您的代码现在容易受到 SQL 注入的影响。此外，您应该ExecuteNonQuery在 SQL 命令上使用该方法，而不是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();
}
回答by Akhtar ali
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# 功能插入日期和时间，您的表必须是 DateTime 类型。不仅仅是日期或时间。
