objectdatasource中的数据集不包含任何表 [英] the dataset in the objectdatasource does not contain any tables

问题描述

我面临错误objectdatasource中的数据集不包含任何表格，我不知道是什么导致了这个问题。



下面是一个用于从数据库表中检索第二行的代码提取：

I am facing the error " the dataset in the objectdatasource does not contain any tables" and i have no idea what is causing this problem.

Below is an extract of my codes used to retrieve the second row from the database table:

public DataSet GetConfirmedAdv2(int categoryID)
      {
          SqlConnection conn;
          StringBuilder sql;
          SqlDataAdapter da;
          DataSet confirmed;

          conn = dbConn.GetConnection();
          confirmed = new DataSet();
          sql = new StringBuilder();
          sql.AppendLine("SELECT a.imageID, a.categoryID ");
          sql.AppendLine("FROM ( SELECT *, ROW_NUMBER() OVER(ORDER BY PaymentID) AS RowNo FROM WP_advConfirmed) AS a ");
          sql.AppendLine("WHERE RowNo = 2");
          sql.AppendLine("INNER JOIN WP_advCategory AS b ");
          sql.AppendLine("ON a.categoryID = b.categoryID ");
          sql.AppendLine("WHERE a.categoryID = @categoryID ");

          //sql.AppendLine("AND a.companyID = @companyID");


          try
          {
              conn.Open();
              da = new SqlDataAdapter(sql.ToString(), conn);
              da.SelectCommand.Parameters.AddWithValue("@categoryID", categoryID);
              //da.SelectCommand.Parameters.AddWithValue("@companyID", companyID);
              da.Fill(confirmed);
          }
          catch (Exception ex)
          {
              errMsg = ex.Message;
          }
          finally
          {
              conn.Close();
          }

          return confirmed;
      }

将此方法绑定到objectdatasource并运行页面后，它会显示错误。我的SELECT语句是否有问题可能导致这种情况？



我尝试使用查询字符串构建器，并能够检索我想要的内容。但是当我复制到DAL时，它不起作用？



任何帮助将不胜感激。

After i bind this method to my objectdatasource and run the page, it shows me the error. Is there something wrong with my SELECT statement that might be causing this?

i tried using the Query string builder and is able to retrieve what i want. but when i copy over to the DAL, it does not work?

Any help will be greatly appreciated.

推荐答案

SELECT a.imageID, a.categoryID
            FROM (SELECT * FROM
            (SELECT *, ROW_NUMBER() OVER(ORDER BY PaymentID) AS RowNo FROM WP_advConfirmed) AS c WHERE c.ROWNo=2) AS a
            INNER JOIN WP_advCategory AS b
            ON a.categoryID = b.categoryID
            WHERE a.categoryID = @categoryID

试试此查询。实际上问题出在这行sql.AppendLine（WHERE RowNo = 2）;

你试图在同一个select语句的where子句中获取RowNo的值，它正在生成它。该值在给定点不可用。

Try this query. Actually the problem is in this line "sql.AppendLine("WHERE RowNo = 2");"
You are trying to get the value of "RowNo" in the same select statement's where clause, where it is being generated .The value is not available at the given point.

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