SQL：附近有语法错误（'

问题描述：

我想数据绑定到一个GridView控件，但始终未能SQL：附近有语法错误（'

考虑下面的代码（SQL扩展到多行的可读性）：

protected void Page_Load(object sender, EventArgs e) 
{ 

string dsn = "foo"; 
string sql = "SELECT * FROM (SELECT F.Project AS 'Project Number', F.Account AS 
      'Account', F.Pd AS Period, F.Incurred AS Totals, C.Project AS 
      'Project Name' FROM Ultron.Final F INNER JOIN Ultron.Custom 
       C ON F.Project = C.Project WHERE F.Project LIKE '61000.003%' AND 
       F.Account NOT LIKE '%-01' AND F.Fy = YEAR(GetDate())) Budget PIVOT 
       (SUM(Totals) FOR Period in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10], 
       [11],[12]) AS PivotTable"; 
using (SqlConnection conn = new SqlConnection(dsn)) 
using (SqlCommand cmd = new SqlCommand(sql, conn)) 
{ 
    conn.Open(); 
    SqlDataReader reader = cmd.ExecuteReader(); 
    testGrid.DataSource = reader; 
    testGrid.DataBind(); 
} 
}

我确定这个错误与查询中的圆括号有关，但这些都是查询工作所必需的。在SSMS中测试时，查询通过时没有问题。

我该如何重构这个代码不会失败？

编辑

我错过As PivotTable到SQL查询的末尾。它现在有效。对不起，这是一个无辜的错字。感谢所有回复的人。

“BudgetPIVOT”之间缺少空格。如果您从代码中删除SQL，则应该尝试在SSMS中执行它，这将有助于您调试问题。 – Taryn

你可以把你的硬编码查询转换成一个存储过程，然后调用那个SP？ –

'BudgetPIVOT'应该是'Budget PIVOT'吗？或者这只是一个复制过去的错误？ – outcoldman

答

缺少空间和关闭)

protected void Page_Load(object sender, EventArgs e) 
{ 

    string dsn = "foo"; 
    string sql = @"SELECT * FROM 
        (
        SELECT F.Project AS 'Project Number', F.Account AS 
          'Account', F.Pd AS Period, F.Incurred AS Totals, C.Project AS 
          'Project Name' FROM Ultron.Final F INNER JOIN Ultron.Custom 
          C ON F.Project = C.Project 
        WHERE 
          F.Project LIKE '61000.003%' AND 
          F.Account NOT LIKE '%-01' AND F.Fy = YEAR(GetDate()) 
        ) Budget PIVOT 
        (
        SUM(Totals) FOR Period in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10], 
        [11],[12]) 
        )"; 
    using (SqlConnection conn = new SqlConnection(dsn)) 
    using (SqlCommand cmd = new SqlCommand(sql, conn)) 
    { 
     conn.Open(); 
     SqlDataReader reader = cmd.ExecuteReader(); 
     testGrid.DataSource = reader; 
     testGrid.DataBind(); 
    } 
}

不过说真的，你应该创建一个存储过程是这样的：

CREATE PROCEDURE [GetProjectBudgetInfo] 
AS 
BEGIN 
    SET NOCOUNT ON; 

    SELECT * FROM 
         (
         SELECT F.Project AS 'Project Number', F.Account AS 
           'Account', F.Pd AS Period, F.Incurred AS Totals, C.Project AS 
           'Project Name' FROM Ultron.Final F INNER JOIN Ultron.Custom 
           C ON F.Project = C.Project 
         WHERE 
           F.Project LIKE '61000.003%' AND 
           F.Account NOT LIKE '%-01' AND F.Fy = YEAR(GetDate()) 
         ) Budget PIVOT 
         (
         SUM(Totals) FOR Period in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) 
         ) 

END

然后调用它的代码：

protected void Page_Load(object sender, EventArgs e) 
{ 

    string dsn = "foo"; 
    string sql = @"GetProjectBudgetInfo"; 
    using (SqlConnection conn = new SqlConnection(dsn)) 
    using (SqlCommand cmd = new SqlCommand(sql, conn)) 
    { 
     cmd.CommandType = CommandType.StoredProcedure; 
     conn.Open(); 
     SqlDataReader reader = cmd.ExecuteReader(); 
     testGrid.DataSource = reader; 
     testGrid.DataBind(); 
    } 
}

答

它在您的sql查询中丢失了最后一个)。试试这个：

protected void Page_Load(object sender, EventArgs e) 
{  
    string dsn = "foo"; 
    string sql = "SELECT * FROM (SELECT F.Project AS 'Project Number', F.Account AS 
       'Account', F.Pd AS Period, F.Incurred AS Totals, C.Project AS 
       'Project Name' FROM Ultron.Final F INNER JOIN Ultron.Custom 
        C ON F.Project = C.Project WHERE F.Project LIKE '61000.003%' AND 
        F.Account NOT LIKE '%-01' AND F.Fy = YEAR(GetDate())) BudgetPIVOT 
        (SUM(Totals) FOR Period in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10], 
        [11],[12]))"; 

    using (SqlConnection conn = new SqlConnection(dsn)) 
    using (SqlCommand cmd = new SqlCommand(sql, conn)) 
    { 
     conn.Open(); 
     SqlDataReader reader = cmd.ExecuteReader(); 
     testGrid.DataSource = reader; 
     testGrid.DataBind(); 
    } 
}

答

如果我计算正确，则在陈述结尾处缺少括号。

string sql = "SELECT * FROM (SELECT F.Project AS 'Project Number', F.Account AS 
      'Account', F.Pd AS Period, F.Incurred AS Totals, C.Project AS 
      'Project Name' FROM Ultron.Final F INNER JOIN Ultron.Custom 
       C ON F.Project = C.Project WHERE F.Project LIKE '61000.003%' AND 
       F.Account NOT LIKE '%-01' AND F.Fy = YEAR(GetDate())) Budget PIVOT 
       (SUM(Totals) FOR Period in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10], 
       [11],[12]))";

SQL：附近有语法错误（'

相关推荐