C# 多行SQL语句抛出错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8936665/
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
SQL statement in multiple lines throwing error
提问by mynameisneo
To prep my question defensively, I've utilized Google, Bing and StackOv prior to posting :-). Also, I'm new to MVC3 and still grappling with the syntactical intricacies of the framework.
为了防御性地准备我的问题，我在发布之前使用了 Google、Bing 和 StackOv :-)。另外，我是 MVC3 的新手，仍然在努力解决框架的复杂句法问题。
I have an error in my SQL statement in the code block below which is bugging me quite a bit. The syntax appears correct. I simplified the SQL statement with a Select * From.. and it returns data just fine.
我在下面的代码块中的 SQL 语句中有一个错误，这让我很烦恼。语法看起来是正确的。我用 Select * From.. 简化了 SQL 语句，它返回数据就好了。
Also, if there is a better way to do this (without using an EF object), definitely open to suggestions. I really like the flexibility and control of seeing the SQL Statement - either that, or just used to it as form of habit :-).
此外，如果有更好的方法来做到这一点（不使用 EF 对象），绝对可以接受建议。我真的很喜欢看到 SQL 语句的灵活性和控制力——要么是那样，要么只是习惯了它作为一种习惯:-)。
Thanks in advance!!
提前致谢！！
@using System.Data.SqlClient;
@using System.Configuration;
@{
Layout = null;
}
@{
SqlConnection cn = null;
cn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlConn"].ToString());
cn.Open();
SqlCommand cmd = new SqlCommand((@"SELECT DISTINCT" +
"tblSBT.sname," +
"tblSBDetails.sid," +
"tblSBDetails.assignedtrack," +
"tblSBDetails.maxtrack," +
"tblSBDetails.currentvals," +
"tblSBDetails.maxvals," +
"tblSBDetails.lastupdated" +
"FROM" +
"tblSBT (NOLOCK)" +
"LEFT OUTER JOIN" +
"tblSBDetails (NOLOCK)" +
"ON" +
"tblSBT.sid = tblSBDetails.sid" +
"WHERE" +
"tblSBDetails.lastupdated > DateADD(n, -5, GETDATE())"+
"ORDER BY" +
"tblSBT.sname" +), cn);
var myreader = cmd.ExecuteReader();
}
采纳答案by clyc
If you're using the @ symbol, you don't need to concatenate your strings like how you are doing it. It's also not the most efficient way of writing that piece of code when you're joining strings like that.
如果您正在使用 @ 符号，则不需要像您那样连接字符串。当您连接这样的字符串时，这也不是编写该段代码的最有效方式。
SqlConnection cn = null;
cn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlConn"].ToString());
cn.Open();
SqlCommand cmd = new SqlCommand((@"SELECT DISTINCT
tblSBT.sname,
tblSBDetails.sid,
tblSBT.sname,
tblSBDetails.sid,
tblSBDetails.assignedtrack,
tblSBDetails.maxtrack,
tblSBDetails.currentvals,
tblSBDetails.maxvals,
tblSBDetails.lastupdated
FROM tblSBT (NOLOCK)
LEFT OUTER JOIN tblSBDetails (NOLOCK)
ON .sid = tblSBDetails.sid
WHERE tblSBDetails.lastupdated > DateADD(n, -5, GETDATE())
ORDER BY tblSBT.sname"), cn);
var myreader = cmd.ExecuteReader();
回答by adatapost
You have to add a space between strings.
您必须在字符串之间添加一个空格。
SqlCommand cmd = new SqlCommand((@"SELECT DISTINCT " +
"tblSBT.sname," +
"tblSBDetails.sid," +
"tblSBDetails.assignedtrack," +
"tblSBDetails.maxtrack," +
"tblSBDetails.currentvals," +
"tblSBDetails.maxvals," +
"tblSBDetails.lastupdated" +
" FROM" +
" tblSBT (NOLOCK)" +
" LEFT OUTER JOIN" +
" tblSBDetails (NOLOCK)" +
" ON" +
" tblSBT.sid = tblSBDetails.sid" +
" WHERE" +
" tblSBDetails.lastupdated > DateADD(n, -5, GETDATE())"+
" ORDER BY" +
" tblSBT.sname"), cn);
Do not concatenate strings because you are using multiline string literal@.
不要连接字符串，因为您使用的是多行字符串文字@。
SqlCommand cmd = new SqlCommand(@"SELECT DISTINCT tblSBT.sname,tblSBDetails.sid,
tblSBDetails.assignedtrack,
tblSBDetails.maxtrack,
tblSBDetails.currentvals,
tblSBDetails.maxvals,
tblSBDetails.lastupdated
FROM tblSBT (NOLOCK)
LEFT OUTER JOIN
tblSBDetails (NOLOCK)
ON tblSBT.sid = tblSBDetails.sid
WHERE tblSBDetails.lastupdated > DateADD(n, -5, GETDATE())
ORDER BY tblSBT.sname",cn);
;
;
回答by Matt Dawdy
A better way to debug this would have been to set a string variable = to the entire SQL statement, then copy and paste that into SQL Server Mgmt Studio. You'd probably have seen the code running together and identified your issue.
调试此问题的更好方法是将字符串变量 = 设置为整个 SQL 语句，然后将其复制并粘贴到 SQL Server Mgmt Studio 中。您可能已经看到代码一起运行并确定了您的问题。
Also, I don't think that you really are understanding the @ symbol's use here. Only your first line of "SELECT DISTINCT" is treated as a literal, the rest of the strings you are concatenating are just strings, not string literals. Which, really, in this case doesn't even matter.
另外，我认为您并没有真正理解 @ 符号在这里的用法。只有“SELECT DISTINCT”的第一行被视为文字，您连接的其余字符串只是字符串，而不是字符串文字。实际上，在这种情况下，这甚至无关紧要。
What you probably meant is
你可能的意思是
SqlCommand cmd = new SqlCommand((@"SELECT DISTINCT
tblSBT.sname,
tblSBDetails.sid,
tblSBDetails.assignedtrack,
tblSBDetails.maxtrack,
tblSBDetails.currentvals,
tblSBDetails.maxvals,
tblSBDetails.lastupdated
FROM
tblSBT (NOLOCK)
LEFT OUTER JOIN
tblSBDetails (NOLOCK)
ON
tblSBT.sid = tblSBDetails.sid
WHERE
tblSBDetails.lastupdated > DateADD(n, -5, GETDATE())
ORDER BY
tblSBT.sname"), cn);
