问题是我怎样在产品类别页显示(根据类别表的id统计该类别下产品的总数),限在对应的产品类别中显示有多少个
0
悬赏园豆：20
[待解决问题]
两张关联表,一个是产品类别表,一个是产品表.
.net中,我用datalist控件,datalist控件里一个label控件,AspNetPager1,进行分页显示,问题是我怎样在产品类别页显示(根据类别表的id统计该类别下产品的总数),即在对应的产品类别中显示有多少个产品.
这个用什么方法实现,谢谢!下面代码实现不了
protected void BindData()
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString); //创建连接对象
conn.Open();
string sql = "select * from NM_Countdown_category order by px asc";
SqlCommand cmd = new SqlCommand(sql, conn); //执行查询语句
DataSet ds = new DataSet();
SqlDataAdapter oda = new SqlDataAdapter(cmd);
oda.Fill(ds, AspNetPager1.PageSize * (AspNetPager1.CurrentPageIndex - 1), AspNetPager1.PageSize, "NM_Countdown_category");
DataList1.DataSource = ds.Tables["NM_Countdown_category"].DefaultView;
DataList1.DataBind();
string sqlsearch;
for (int i = 0; i < DataList1.Items.Count; i++)
{
sqlsearch = "select count(*) from NM_Countdown_products where lb='" + DataList1.DataKeyField + "'";
SqlCommand cmdsearch = new SqlCommand(sqlsearch, conn); //执行查询语句
Label Tongji = (Label)DataList1.Items[i].FindControl("Label1");
Tongji.Text = cmdsearch.ExecuteScalar().ToString();
}
cmd.Dispose();
ds.Dispose();
oda.Dispose();
conn.Close();
}
所有回答(2)
0
能否给出你的表结构呢
类别表字段:lb,px,xianshi,tm,id
产品表字段:cpname,pic,content,stock,lbid,id
0
select categoryName,(select COUNT(-1) from product as a where categoryId=b.categoryId) as 产品数量 from category as b
以上Sql将表名和字段进行替换就行了。
另外提个不成熟的建议，查询的时候不要*。
count(-1)和count(1)有什么区别？
调试了 还是不行
我自己解决了,谢谢,代码如下
protected void BindData()
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString); //创建连接对象
conn.Open();
string sql = "select * from NM_Countdown_category order by px asc";
SqlCommand cmd = new SqlCommand(sql, conn); //执行查询语句
DataSet ds = new DataSet();
SqlDataAdapter oda = new SqlDataAdapter(cmd);
oda.Fill(ds, AspNetPager1.PageSize * (AspNetPager1.CurrentPageIndex - 1), AspNetPager1.PageSize, "NM_Countdown_category");
DataList1.DataSource = ds.Tables["NM_Countdown_category"].DefaultView;
DataList1.DataKeyField = "id";
DataList1.DataBind();
string sqlsearch;
for (int i = 0; i < DataList1.Items.Count; i++)
{
sqlsearch = "select count(*) from NM_Countdown_products where lb='" + this.DataList1.DataKeys[i].ToString() + "'";
SqlCommand cmdsearch = new SqlCommand(sqlsearch, conn); //执行查询语句
Label Tongji = (Label)DataList1.Items[i].FindControl("Label1");
Tongji.Text = cmdsearch.ExecuteScalar().ToString();
}
cmd.Dispose();
ds.Dispose();
oda.Dispose();
conn.Close();
}