Sql:'；附近的语法不正确；('；

本文关键字：语法 不正确 Sql | 更新日期: 2023-09-27 18:20:14

我正在尝试将DataBind绑定到GridView控件，但它始终失败。

给定以下代码（为了可读性，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中测试时，查询通过而没有问题。

我该如何重组它以使代码不会失败？

编辑

到SQL查询结束时，我缺少As PivotTable。它现在起作用了。对不起，这是一个无辜的打字错误。感谢所有的回复。

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

如果我计算正确，您的语句末尾缺少一个括号。

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查询中缺少最后一个)。试试这个：

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();
    }
}
相关文章：