I am trying to display an index with categories and subcategories and then the actual information.
It is a listing of recipes. I have for instance:
Drinks > Cocktails > Margaritas
I have them in a database and I am having trouble displaying them properly. I am trying to use a repeater to display the complete index. I am further using nesting another repeater to display the categories that they go under. Some reason I can get the
following to work:
Drinks > Margaritas
But I am having difficulty getting the subcategory to show up properly. I believe it is because every bit of info has a category but not every bit has a subcategory the same. I may have a Margarita which is in the Drinks category and also the cocktails
subcategory but the next entry might be Wine from the Other subcategory but still the Drinks category.
using System;
using System.Data;
using System.Text;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Configuration;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page {
public SqlConnection Conn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["strConn"]);
public SqlConnection Conn2 = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["strConn"]);
public SqlDataAdapter RS = new SqlDataAdapter();
public SqlDataAdapter RS2 = new SqlDataAdapter();
public DataTable dsRecordSet = new DataTable();
public DataTable dsRecordSet2 = new DataTable();
public DataTable dsRecordSet3 = new DataTable();
public BaseFunctions BaseFunc = new BaseFunctions();
public void Page_Load(object sender, EventArgs e) {
Those don't mention how to display the information with a category and then a subcategory. I may be mistaken about the links but I have gotten a category and another item under it to work but I'm confused as to how to add that subcategory. Can you tell
me where it tells me how to display them? I did ask how to display database information but ultimately I am looking for how to display it with a category and a further subcategory. Any help?
This would be perfect for the TreeView Control. You can have as many subcategories as you want and display them heirarchly. If you are interested, I can paste some code and explain the table layout needed to load the tree.
The table structure is really simple. You use NULL values for root nodes as the ID column, could be an autoincrement field. Then you have a parentid field where you reference what belongs to what nodes. The example explains it well. Here is my C# code
that implements it:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
public partial class Site : System.Web.UI.MasterPage
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
PopulateRootLevel();
}
}
private void PopulateRootLevel()
{
SqlConnection aConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PaulsPlaceDBConnectionString"].ConnectionString);
SqlCommand aCmd = new SqlCommand("SELECT menuID, title, navigateURL, tooltip, (SELECT COUNT(*) AS Expr1 FROM Menu WHERE (parentID = m.menuID)) AS childnodecount FROM Menu AS m WHERE (parentID IS NULL)", aConn);
SqlDataAdapter aAdapter = new SqlDataAdapter(aCmd);
DataTable aTable = new DataTable();
aAdapter.Fill(aTable);
aConn.Close();
PopulateNodes(aTable, tvMenu.Nodes);
aConn.Close();
}
private void PopulateNodes(DataTable aTable, TreeNodeCollection nodes)
{
foreach(DataRow dr in aTable.Rows)
{
TreeNode tn = new TreeNode();
tn.Text = dr["title"].ToString();
tn.Value = dr["MenuID"].ToString();
nodes.Add(tn);
//If node has child nodes, then enable on-demand populating
tn.PopulateOnDemand = ((Int32)(dr["childnodecount"]) > 0);
//Attach the URL to the node
tn.NavigateUrl = dr["navigateURL"].ToString();
tn.ToolTip = dr["tooltip"].ToString();
}
}
private void PopulateSubLevel(Int32 parentid, TreeNode parentNode)
{
SqlConnection aConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PaulsPlaceDBConnectionString"].ConnectionString);
SqlCommand aCmd = new SqlCommand("SELECT menuID, title, navigateURL, tooltip, (SELECT COUNT(*) AS childnodecount FROM Menu WHERE (parentID = m.menuID)) AS childnodecount FROM Menu AS m WHERE (parentID = @parentID) ORDER BY title", aConn);
aCmd.Parameters.Add("@parentID", SqlDbType.Int).Value = parentid;
SqlDataAdapter aAdapter = new SqlDataAdapter(aCmd);
DataTable aTable = new DataTable();
aAdapter.Fill(aTable);
aConn.Close();
PopulateNodes(aTable, parentNode.ChildNodes);
aConn.Close();
}
protected void tvMenu_TreeNodePopulate(object sender, TreeNodeEventArgs e)
{
PopulateSubLevel(Convert.ToInt32(e.Node.Value), e.Node);
}
}
DigitalAce7
0 Points
14 Posts
Wondering the best way to display database information
May 16, 2012 09:07 PM|LINK
Hi guys,
I am trying to display an index with categories and subcategories and then the actual information.
It is a listing of recipes. I have for instance:
Drinks > Cocktails > Margaritas
I have them in a database and I am having trouble displaying them properly. I am trying to use a repeater to display the complete index. I am further using nesting another repeater to display the categories that they go under. Some reason I can get the following to work:
Drinks > Margaritas
But I am having difficulty getting the subcategory to show up properly. I believe it is because every bit of info has a category but not every bit has a subcategory the same. I may have a Margarita which is in the Drinks category and also the cocktails subcategory but the next entry might be Wine from the Other subcategory but still the Drinks category.
HTML Code:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="test.aspx.cs" Inherits="_Default" Debug="true" %>
<!doctype html>
<html>
<head>
<title>Test</title>
</head>
<body>
<form runat="server">
<asp:Repeater id="rptCatList" runat="server" OnItemDataBound="CatRepeater_ItemDataBound">
<ItemTemplate>
<span style="font-weight: bold;">
<%# Eval("CatType") %>
</span>
<br />
<asp:HiddenField id="hdnSubCatID" value='<%# Eval("SubCatTypeID")%>' runat="server" />
<asp:Repeater id="rptSubCatList" runat="server">
<ItemTemplate>
<span style="font-style: italic"><%# Eval("SubCatTypeID") %></span><br />
<asp:HiddenField id="hdnSubCatID" value='<%# Eval("SubCatTypeID")%>' runat="server" />
<asp:Repeater id="rptIndexList" runat="server">
<ItemTemplate>
<%# Eval("Title") %>, <%# Eval("Page") %> <br />
</ItemTemplate>
</asp:Repeater>
</ItemTemplate>
</asp:Repeater>
<br />
</ItemTemplate>
</asp:Repeater>
</form>
</body>
</html>
ASPX Code:
using System;
using System.Data;
using System.Text;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Configuration;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page {
public SqlConnection Conn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["strConn"]);
public SqlConnection Conn2 = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["strConn"]);
public SqlDataAdapter RS = new SqlDataAdapter();
public SqlDataAdapter RS2 = new SqlDataAdapter();
public DataTable dsRecordSet = new DataTable();
public DataTable dsRecordSet2 = new DataTable();
public DataTable dsRecordSet3 = new DataTable();
public BaseFunctions BaseFunc = new BaseFunctions();
public void Page_Load(object sender, EventArgs e) {
if(!IsPostBack) {
try {
string SQL = "SELECT * FROM [vw_Food_Types]";
RS = new SqlDataAdapter(SQL,Conn);
dsRecordSet = new DataTable();
RS.Fill(dsRecordSet);
if (dsRecordSet.Rows.Count != 0) {
rptCatList.DataSource = dsRecordSet;
rptCatList.DataBind();
}
dsRecordSet.Clear();
dsRecordSet.Dispose();
}
catch {
}
}
}
protected void CatRepeater_ItemDataBound(object sender, RepeaterItemEventArgs e) {
RepeaterItem item = e.Item;
if (item.ItemType == ListItemType.Item || item.ItemType == ListItemType.AlternatingItem) {
Repeater rptSubCatList = (Repeater)item.FindControl("rptSubCatList");
string SubCatID = ((HiddenField) e.Item.FindControl("hdnSubCatID")).Value;
string SQL2 = "SELECT * FROM [vw_Food_Types]";
SQL2 += " WHERE [SubCatTypeID] = " + "'" + SubCatID + "'";
RS = new SqlDataAdapter(SQL2, Conn);
dsRecordSet2 = new DataTable();
RS.Fill(dsRecordSet2);
if (dsRecordSet2.Rows.Count != 0) {
rptSubFoodList.DataSource = dsRecordSet2;
rptSubFoodList.DataBind();
}
dsRecordSet2.Clear();
dsRecordSet2.Dispose();
}
}
protected void SubCatRepeater_ItemDataBound(object sender, RepeaterItemEventArgs e) {
RepeaterItem item = e.Item;
if (item.ItemType == ListItemType.Item || item.ItemType == ListItemType.AlternatingItem) {
Repeater rptSubCatList = (Repeater)item.FindControl("rptSubCatList");
string SubCatID = ((HiddenField) e.Item.FindControl("hdnSubCatID")).Value;
string SQL2 = "SELECT [Title],[Page] FROM [vw_SubFood_Types]";
SQL2 += " WHERE [SubCatTypeID] = " + "'" + SubCatID + "'";
RS = new SqlDataAdapter(SQL2, Conn);
dsRecordSet3 = new DataTable();
RS.Fill(dsRecordSet3);
if (dsRecordSet3.Rows.Count != 0) {
rptSubFoodList.DataSource = dsRecordSet3;
rptSubFoodList.DataBind();
}
dsRecordSet3.Clear();
dsRecordSet3.Dispose();
}
}
}
Database sql nested Repeater
sriramabi
Contributor
4351 Points
1277 Posts
Re: Wondering the best way to display database information
May 16, 2012 09:11 PM|LINK
pls ref this
http://www.aspnet101.com/2007/05/displaying-database-information/
http://forums.asp.net/t/1313444.aspx/1
http://www.asp.net/web-forms/tutorials/data-access
thank u
DigitalAce7
0 Points
14 Posts
Re: Wondering the best way to display database information
May 16, 2012 09:19 PM|LINK
Those don't mention how to display the information with a category and then a subcategory. I may be mistaken about the links but I have gotten a category and another item under it to work but I'm confused as to how to add that subcategory. Can you tell me where it tells me how to display them? I did ask how to display database information but ultimately I am looking for how to display it with a category and a further subcategory. Any help?
DigitalAce7
0 Points
14 Posts
Re: Wondering the best way to display database information
May 17, 2012 01:34 PM|LINK
Anyone have any ideas? Everywhere I look I see how to do show with just a couple categories and not subcategories.
pnoneal
Member
353 Points
166 Posts
Re: Wondering the best way to display database information
May 17, 2012 02:49 PM|LINK
This would be perfect for the TreeView Control. You can have as many subcategories as you want and display them heirarchly. If you are interested, I can paste some code and explain the table layout needed to load the tree.
Paul
DigitalAce7
0 Points
14 Posts
Re: Wondering the best way to display database information
May 17, 2012 02:58 PM|LINK
Can you try? I think I know how it works but making it dynamic and drawing from a database makes it trickier to grasp.
pnoneal
Member
353 Points
166 Posts
Re: Wondering the best way to display database information
May 17, 2012 11:15 PM|LINK
Ah, found the link that I used to set mine up. He use's VB, I converted to C#. Here's the link to the example:
http://aspalliance.com/732_Display_Hierarchical_Data_with_TreeView_in_ASPNET_20
The table structure is really simple. You use NULL values for root nodes as the ID column, could be an autoincrement field. Then you have a parentid field where you reference what belongs to what nodes. The example explains it well. Here is my C# code that implements it:
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data.SqlClient; using System.Data; public partial class Site : System.Web.UI.MasterPage { protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { PopulateRootLevel(); } } private void PopulateRootLevel() { SqlConnection aConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PaulsPlaceDBConnectionString"].ConnectionString); SqlCommand aCmd = new SqlCommand("SELECT menuID, title, navigateURL, tooltip, (SELECT COUNT(*) AS Expr1 FROM Menu WHERE (parentID = m.menuID)) AS childnodecount FROM Menu AS m WHERE (parentID IS NULL)", aConn); SqlDataAdapter aAdapter = new SqlDataAdapter(aCmd); DataTable aTable = new DataTable(); aAdapter.Fill(aTable); aConn.Close(); PopulateNodes(aTable, tvMenu.Nodes); aConn.Close(); } private void PopulateNodes(DataTable aTable, TreeNodeCollection nodes) { foreach(DataRow dr in aTable.Rows) { TreeNode tn = new TreeNode(); tn.Text = dr["title"].ToString(); tn.Value = dr["MenuID"].ToString(); nodes.Add(tn); //If node has child nodes, then enable on-demand populating tn.PopulateOnDemand = ((Int32)(dr["childnodecount"]) > 0); //Attach the URL to the node tn.NavigateUrl = dr["navigateURL"].ToString(); tn.ToolTip = dr["tooltip"].ToString(); } } private void PopulateSubLevel(Int32 parentid, TreeNode parentNode) { SqlConnection aConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PaulsPlaceDBConnectionString"].ConnectionString); SqlCommand aCmd = new SqlCommand("SELECT menuID, title, navigateURL, tooltip, (SELECT COUNT(*) AS childnodecount FROM Menu WHERE (parentID = m.menuID)) AS childnodecount FROM Menu AS m WHERE (parentID = @parentID) ORDER BY title", aConn); aCmd.Parameters.Add("@parentID", SqlDbType.Int).Value = parentid; SqlDataAdapter aAdapter = new SqlDataAdapter(aCmd); DataTable aTable = new DataTable(); aAdapter.Fill(aTable); aConn.Close(); PopulateNodes(aTable, parentNode.ChildNodes); aConn.Close(); } protected void tvMenu_TreeNodePopulate(object sender, TreeNodeEventArgs e) { PopulateSubLevel(Convert.ToInt32(e.Node.Value), e.Node); } }