c# - 如何解决sql查询中的最大长度错误（太长）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9805948/
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
How to solve the maximum lenght error( it is too long ) in sql query c#
提问by leventkalay92
I have a method which includes sql statement . it is
我有一个包含 sql 语句的方法。这是
public Boolean addRSS(string RSS_title, string Description, DateTime datetime, string RSS_Link, string user_name, float rate)
{
// Console.WriteLine(MyString.Remove(5, 10));
// string a = date.ToString().Replace('.', '-');
Boolean res = false;
string sql = "INSERT INTO My_RSS ( RSS_Title,RSS_Description,RSS_Date,RSS_Link,RSS_Rate,UserName) values('"
+
RSS_title + "','" +
"FFFFFFFFFFFFFFFFFFFFFFFFFAAASDASDASDASD" +
"', SYSUTCDATETIME(),'" +
RSS_Link + "'," +
rate + ",'"+
user_name +
"')";
try
{
// hasan = hasan.Insert(c, hasan);
SqlCommand cmd = new SqlCommand(sql, Connect());
cmd.ExecuteNonQuery();
res = true;
}
catch (Exception)
{
res = false;
}
return res;
}
It gives the error when I try to enter this input http://rss.feedsportal.com/c/32727/f/510887/s/1da50441/l/0Lekonomi0Bmilliyet0N0Btr0Cenflasyon0Eyuzde0E50Ee0Einene0Ekadar0Esikacak0E0Cekonomi0Cekonomidetay0C210B0A30B20A120C15181930Cdefault0Bhtm/story01.htmto "link column" and it gives error which is Incorrect syntax near 'e'. The identifier that starts with 'Lekonomi0Bmilliyet0N0Btr0Cenflasyon0Eyuzde0E50Ee0Einene0Ekadar0Esikacak0E0Cekonomi0Cekonomidetay0C210B0A30B20A120C15181930Cdefau' is too long. Maximum length is 128. Unclosed quotation mark after the character string ')'.
它给人的错误，当我试图进入该输入http://rss.feedsportal.com/c/32727/f/510887/s/1da50441/l/0Lekonomi0Bmilliyet0N0Btr0Cenflasyon0Eyuzde0E50Ee0Einene0Ekadar0Esikacak0E0Cekonomi0Cekonomidetay0C210B0A30B20A120C15181930Cdefault0Bhtm/story01.htm到“链接栏”，它给错误， 'e' 附近的语法不正确。以“Lekonomi0Bmilliyet0N0Btr0Cenflasyon0Eyuzde0E50Ee0Einene0Ekadar0Esikacak0E0Cekonomi0Cekonomidetay0C210B0A30B20A120C15181930Cdefau”开头的标识符太长了。最大长度为 128。字符串 ')' 后的非封闭引号。
Also,In the sql side this colum is varchar(455)
此外，在 sql 方面，此列是 varchar(455)
采纳答案by Michael Edenfield
The error is saying that the identifiername is too long; this combined with the unclosed quotation mark error means you probably missed an opening quote. That is, you have this:
错误是说标识符名称太长；这与未关闭的引号错误相结合意味着您可能错过了一个开场白。也就是说，你有这个：
INSERT INTO Foo ( A ) VALUES ( AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA')
instead of
代替
INSERT INTO Foo ( A ) VALUES ( 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA')
You shouldn't be building your queries via string concatentation; this is one of the reasons. Parameterized queries will get the quoting right for you. (Note: you don't need to be using stored procs to use parameterized queries.)
您不应该通过字符串连接来构建查询；这是原因之一。参数化查询将为您提供合适的报价。（注意：您不需要使用存储过程来使用参数化查询。）
var sql = "INSERT INTO My_RSS ( Title, Description, Date, Link, Rate, Name )
VALUES ( @Title, @Desc, @PostDate, @Link, @Rate, @Name )";
SqlCommand cmd = new SqlCommand(sql, Connect());
cmd.Parameters.Add("@Title", SqlDbType.VarChar, 100).Value = RSS_title;
cmd.Parameters.Add("@Desc", SqlDbType.VarChar, 8192).Value = RSS_description;
cmd.Parameters.Add("@PostDate", SqlDbType.SmallDateTime).Value = DateTime.Now;
cmd.Parameters.Add("@Rate", SqlDbType.Int).Value = rate;
etc.
等等。
回答by Shiblee Shahriar
You Can also add SET QUOTED_IDENTIFIER OFF before 'sql' string and SET QUOTED_IDENTIFIER On after 'sql'
您还可以在 'sql' 字符串之前添加 SET QUOTED_IDENTIFIER OFF 并在 'sql' 之后添加 SET QUOTED_IDENTIFIER On
QUOTED IDENTIFIER ON/OFF: This specifies the setting for usage of double quotation. IF this is on, double quotation mark is used as part of the SQL Server identifier (object name). This can be useful in situations in which identifiers are also SQL Server reserved words.
QUOTED IDENTIFIER ON/OFF：指定双引号使用的设置。如果启用，双引号将用作 SQL Server 标识符（对象名称）的一部分。这在标识符也是 SQL Server 保留字的情况下很有用。
sql = "SET QUOTED_IDENTIFIER OFF " + sql + " SET QUOTED_IDENTIFIER OFF ";
SqlCommand cmd = new SqlCommand(sql, Connect());
cmd.ExecuteNonQuery();
res = true;
You should use this in this case.
在这种情况下，您应该使用它。
