Windows 窗体
一组用于开发图形用户界面的 .NET Framework 托管库。
93 个问题
我想在 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
连接字符串时,您是否忘记在行的开头或结尾添加空格? 这将导致某些单词连接在一起,如下所示:
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);
如果回复有帮助,请点击“接受答案”并点赞。
注意:如果您想接收此线程的相关电子邮件通知,请按照我们文档中的步骤启用电子邮件通知。