SQL statement in multiple lines throwing error

问题:

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

回答1:

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

回答2:

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

;

回答3:

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.

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.

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