如何在数据集数据表中显示数据列 [英] How to show DataColumns in Dataset DataTable

问题描述

我有一个查询，我将它作为数据表放入数据集中.查询运行良好，但是在数据表中它没有在数据列中列出任何内容，因为我无法报告它......任何想法为什么这不起作用?我的查询如下.

I have a query that I put into a dataset as a datatable. The query runs great, however in the datatable it does not list anything in the Data Column, there for I cannot report it... Any ideas why this is not working? My Query is below.

SELECT * FROM (
    SELECT timeclock.dtTimeIn, timeclock.dtTimeOut, employees.sfirstname,
       RANK() OVER ( ORDER BY dtTimeIn) rk1, --earliest record gets 1
       RANK() OVER (ORDER BY dtTimeOut DESC) rk2 --latest record gets 1

    FROM   TimeClock INNER JOIN
                         Employees ON TimeClock.lEmployeeID = Employees.lEmployeeID
    WHERE (dtTimeIn > dateadd(day, datediff(day, 0, getdate())-1, 0)) AND (dtTimeOut < dateadd(day, datediff(day, 0, getdate()), 0)) AND 
      (sDept IN ('1', '2', '3'))
) A
WHERE rk2=1 

运行查询时的当前输出:

Current output when I run the query:

  dtTimeIn                    dtTimeOut       sfirstname    rk1      rk2
2/7/2013 2:36:00 PM 2/7/2013 7:52:33 PM        Brian        10        1

在向导中创建表适配器时...我收到一条错误消息:

When Creating the Table adapter in the wizzard... I get an error message:

The Wizard detected the following problems when configuring the TableAdapter:
"OpenTime":

Details:
Generated SELECT statement.
The OVER SQL construct or statement is not supported
To add these components to your dataset, click Finish.

我猜它不喜欢这个查询……但我不知道除了使用OVER"函数之外，还有什么方法可以完成这个查询……

I'm guessing it doesn't like the query... But i'm not sure how else to accomplish the query other than using the "OVER" function...

推荐答案

由于TableAdapter 向导的限制，您必须使用代码创建DataTable.

Due to the limitations of the TableAdapter wizard, you have to create the DataTable using code.

DataTable dataTable;

using (SqlConnection sqlConn = new SqlConnection())
{
    sqlConn.Open();

    using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter())
    using (sqlDataAdapter.SelectCommand = sqlConn.CreateCommand())
    {

        sqlDataAdapter.SelectCommand.CommandType = CommandType.Text;
        sqlDataAdapter.SelectCommand.CommandText = '
            SELECT * FROM (
                SELECT timeclock.dtTimeIn, timeclock.dtTimeOut, employees.sfirstname,
                    RANK() OVER ( ORDER BY dtTimeIn) rk1, --earliest record gets 1
                    RANK() OVER (ORDER BY dtTimeOut DESC) rk2 --latest record gets 1

                FROM TimeClock INNER JOIN
                    Employees ON TimeClock.lEmployeeID = Employees.lEmployeeID
                WHERE (dtTimeIn > dateadd(day, datediff(day, 0, getdate())-1, 0)) AND (dtTimeOut < dateadd(day, datediff(day, 0, getdate()), 0)) AND (sDept IN ('1', '2', '3'))
            ) A
            WHERE rk2=1';

        sqlDataAdapter.Fill(dataTable);
    }
}

在填满 dataTable 后，只需将其分配给 ReportViewer 的 DataSource

After having your dataTable filled, simply assign it to the ReportViewer's DataSource

ReportDataSource rds = new ReportDataSource(dataTable.TableName, dataTable);
ReportViewer1.LocalReport.DataSources.Clear();
ReportViewer1.LocalReport.DataSources.Add(rds);

请注意，这些代码片段中有很大一部分未经测试.

Please note that a large part of these code snippets are untested.

这篇关于如何在数据集数据表中显示数据列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！