多行抛出错误SQL语句 [英] SQL statement in multiple lines throwing error

问题描述

要我的问题在防守端，我使用谷歌，Bing和StackOv张贴:-)之前$ P $页。另外，我是新来MVC3和框架的语法复杂性依然拼杀。

我在code座以下是缠着我颇有几分我的SQL语句错误。语法正确显示。我简单的SQL语句用SELECT * FROM ..它返回的数据就好了。

此外，如果有更好的方法来做到这一点（不使用EF对象），绝对开放的建议。我真的很喜欢的灵活性，看到SQL语句的控制 - 要么，或者只是用它作为习惯的形式： - ）

在此先感谢！

  @using System.Data.SqlClient的;
@using System.Configuration;@ {
    布局= NULL;
}
@ {
    SqlConnection的CN = NULL;
    CN =新的SqlConnection（ConfigurationManager.ConnectionStrings [sqlConn]的ToString（））;
    cn.Open（）;
   CMD的SqlCommand =新的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）+
                                    开+
                                        tblSBT.sid = tblSBDetails.sid+
                                    WHE​​RE+
                                    tblSBDetails.lastupdated＆GT; DATEADD（N，-5，GETDATE（））+
                                    ORDER BY+
                                    tblSBT.sname+），CN）;    变种myreader = cmd.ExecuteReader（）;
}
 

解决方案

如果您使用的@符号，你并不需要连接你的字符串像你是如何做的。这也是不写那件code，当你喜欢，加入字符串的最有效的方式。

  SqlConnection的CN = NULL;
CN =新的SqlConnection（ConfigurationManager.ConnectionStrings [sqlConn]的ToString（））;
cn.Open（）;
CMD的SqlCommand =新的SqlCommand（（@SELECT DISTINCT
        tblSBT.sname，
        tblSBDetails.sid，
        tblSBT.sname，
        tblSBDetails.sid，
        tblSBDetails.assignedtrack，
        tblSBDetails.maxtrack，
        tblSBDetails.currentvals，
        tblSBDetails.maxvals，
        tblSBDetails.lastupdated
        从tblSBT（NOLOCK）
                    LEFT OUTER JOIN tblSBDetails（NOLOCK）
                        ON .sid = tblSBDetails.sid
        WHERE tblSBDetails.lastupdated＆GT; DATEADD（N，-5，GETDATE（））
        ORDER BY tblSBT.sname），CN）;变种myreader = cmd.ExecuteReader（）;
 

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.

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.

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 :-).

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

解决方案

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

这篇关于多行抛出错误SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！