VS：DataGridView 的交叉表查询语法错误

Question

我想在 Visual Studio DataGridView 中显示 Access 交叉表查询（如果我不使用交叉表查询，只需一个表查询，它就可以工作了，所以代码的其他部分就可以了）。

下面的交叉表查询代码只是在我设置交叉表查询（通过 Access 交叉表查询向导）后从 Access SQL View 复制的，我不太理解 SQL 内部联接语句。但它返回我想要的内容（使用日期作为列标题名称）。

下面是代码。显然，为了编译，需要对其进行修改。

这是Google驱动器上的Access文件（中间有空格以防被阻止）：https://drive。谷歌。com/ 驱动器/文件夹/1ihZ6OW0l5oJhlPgxWxJCRkJI5P4DEMt0？usp=共享 1ihZ6OW0l5oJhlPgxWxJCRkJI5P4DEMt0

如何修改下面的SQL语句？

谢谢。

             private void btnCrosstabQuery_Click(object sender, EventArgs e)  
             {  
          
                 OdbcConnection Cn = new OdbcConnection(GlobalVariables.DatabaseConnectionString);  
                 OdbcDataAdapter adapt;  
                 DataTable dt;  
                 Cn.Open();  
                 //The code to query table Employee works fine  
                 //adapt = new OdbcDataAdapter("SELECT * from Employee", Cn);  
  
               adapt = new OdbcDataAdapter("TRANSFORM First(WorkNote.Note) AS FirstOfNote  
SELECT Teams.Username, Employee.FirstName, Employee.LastName  
FROM Teams INNER JOIN (Employee INNER JOIN WorkNote ON Employee.Username = WorkNote.Username) ON Teams.Username = WorkNote.Username  
WHERE (((Teams.TeamName)="team1") AND ((WorkNote.WorkDate)<Now() And (WorkNote.WorkDate)>=Now()-30))  
GROUP BY Teams.Username, Employee.FirstName, Employee.LastName  
ORDER BY Teams.Username  
PIVOT Format([WorkNote.WorkDate],"yyyy-mm-dd ddd");", Cn);  
          
  
          
          
                 dt = new DataTable();  
                 adapt.Fill(dt);  
                 dgvUsers.DataSource = dt;  
                 Cn.Close();  
             }  

Note：此问题总结整理于: VS: Crosstab query syntax error for DataGridView

Answer 1

连接字符串时，您是否忘记在行的开头或结尾添加空格？ 这将导致某些单词连接在一起，如下所示：

TRANSFORM First(WorkNote.Note) AS FirstOfNoteSELECT Teams.Username, Employee.FirstName, Employee.LastNameFROM Teams INNER JOIN(Employee INNER JOIN WorkNote ON Employee.Username = WorkNote.Username) ON Teams.Username = WorkNote.UsernameWHERE(((Teams.TeamName) = 'team1') AND((WorkNote.WorkDate) < Now() And(WorkNote.WorkDate) >= Now() - 30))GROUP BY Teams.Username, Employee.FirstName, Employee.LastNameORDER BY Teams.UsernamePIVOT Format([WorkNote.WorkDate],'yyyy-mm-dd ddd')'  

FirstOfNoteSELECT、UsernameWHERE 等连接在一起。 添加空格应该可以解决问题：

                string sqlString = "TRANSFORM First(WorkNote.Note) AS FirstOfNote"+  
                                    " SELECT Teams.Username, Employee.FirstName, Employee.LastName"+  
                                    " FROM Teams INNER JOIN(Employee INNER JOIN WorkNote ON Employee.Username = WorkNote.Username) ON   
                                         Teams.Username = WorkNote.Username"+  
                                    " WHERE(((Teams.TeamName) = 'team1') AND((WorkNote.WorkDate) < Now() And(WorkNote.WorkDate) >= Now() -   
                                         30))"+  
                                    " GROUP BY Teams.Username, Employee.FirstName, Employee.LastName"+  
                                    " ORDER BY Teams.Username"+  
                                    " PIVOT Format([WorkNote.WorkDate],'yyyy-mm-dd ddd')";  
                adapt = new OdbcDataAdapter(sqlString, Cn);  

---

如果回复有帮助，请点击“接受答案”并点赞。

注意：如果您想接收此线程的相关电子邮件通知，请按照我们文档中的步骤启用电子邮件通知。