Nested Repeater Query

Hello Everyone,

I am trying to create a query for the purpose of a nested repeater relation. The information needs to be pulled from one table. I have shortened the columns to the ones that are required.

table - Pages

ID
PageName
ParentPageID

So, take the following example:

ID 14, PageName - Service A, ParentPage ID = 6
ID 15, PageName - Service B, ParentPage ID = 6
ID 36 PageName - Client 1, ParentPage ID = 14
ID 37 PageName - Client 2, ParentPage ID = 14
ID 38 PageName - Client 3, ParentPage ID = 15
ID 39 PageName - Client 4, ParentPage ID = 15

So, I want to create a query that will get my nested repeater to display as follows:

Service A
Client 1
Client 2
Service B
Client 3
Client 4

What I have come up with so far is:

SELECT * from tbl_Pages WHERE ParentPageID IN (Select ID From tbl_Pages)

SELECT p.ParentPageID, p.PageName, p.ID FROM tbl_Pages p

The relation would be based off ParentPageID. I keep getting errors that either there is no unique value or the relation is null. What am I am missing here?

Replace

SELECT * from tbl_Pages WHERE ParentPageID IN (Select ID From tbl_Pages)

SELECT p.ParentPageID, p.PageName, p.ID FROM tbl_Pages p

with

SELECT * from tbl_Pages Parent inner join tbl_Pages Child on Parent.Id = Child.ParentPageID

Inform me if this works for u

|||

Thank you, the query worked great! I am now trying to make it work with a nested repeater however, and I am getting the following error:

System.IndexOutOfRangeException: Cannot find table 1.

I am assuming this is because this query places everything in 1 table, right? Here is my codebehind (I haven't placed the query in a stored procedure yet)

public partial class Controls_RightSideBarNavTree : BaseControl
{
protected void Page_Load(object sender, EventArgs e)
{
string connString = System.Configuration.ConfigurationManager.ConnectionStrings["dsn1"].ConnectionString;
SqlConnection myConnection = new SqlConnection(connString);
SqlCommand MyCommand = new SqlCommand("SELECT * from tbl_Pages Parent inner join tbl_Pages Child on Parent.Id = Child.ParentPageID", myConnection);
SqlDataAdapter ad = new SqlDataAdapter(MyCommand);
DataSet ds = new DataSet();
ad.Fill(ds);

ds.Relations.Add(new DataRelation("NavigationRelation", ds.Tables[0].Columns["ParentPageID"], ds.Tables[1].Columns["ParentPageID"]));
Repeater1.DataSource = ds.Tables[0];
Repeater1.DataBind();
}
}

protected void Repeater1_ItemCreated(object sender, RepeaterItemEventArgs e)
{
if ((e.Item.ItemType == ListItemType.Item) || (e.Item.ItemType == ListItemType.AlternatingItem))
{
DataRowView drv = e.Item.DataItem as DataRowView;
Repeater childRepeater = e.Item.FindControl("childRepeater") as Repeater;
childRepeater.DataSource = drv.CreateChildView("NavigationRelation");
childRepeater.DataBind();
}
}

|||

try this

u have to add next to the repeater in an item template a label with the Eval("Id"), and lets give it an id="label1"

protected void Page_Load(object sender, EventArgs e)
{
string connString = System.Configuration.ConfigurationManager.ConnectionStrings["dsn1"].ConnectionString;
SqlConnection myConnection = new SqlConnection(connString);
SqlCommand MyCommand = new SqlCommand("SELECT * from tbl_Pages Parent inner join tbl_Pages Child on Parent.Id = Child.ParentPageID", myConnection);
SqlDataAdapter ad = new SqlDataAdapter(MyCommand);
DataSet ds = new DataSet();
ad.Fill(ds);

Repeater1.DataSource = ds.Tables[0];
Repeater1.DataBind();
}
}

protected void Repeater1_ItemCreated(object sender, RepeaterItemEventArgs e)
{
if ((e.Item.ItemType == ListItemType.Item) || (e.Item.ItemType == ListItemType.AlternatingItem))
{

string connString = System.Configuration.ConfigurationManager.ConnectionStrings["dsn1"].ConnectionString;
SqlConnection myConnection = new SqlConnection(connString);
SqlCommand MyCommand = new SqlCommand("SELECT * from tbl_Pages Parent inner join tbl_Pages Child on Parent.Id = Child.ParentPageID where Parent.Id = @.ID",myConnection);
Label lbl = (Label)e.Item.FindControl("Label1");

MyCommand.Parameters.Add("@.Id",lbl.Text);
SqlDataAdapter ad = new SqlDataAdapter(MyCommand);
DataSet ds = new DataSet();
ad.Fill(ds);
Repeater childRepeater = e.Item.FindControl("childRepeater") as Repeater;
childRepeater.DataSource = ds;
childRepeater.DataBind();
}
}

Inform me if this works for u.

|||

Thank you very much for your help. I see that you are from Lebanon. I am originally from Damascus :)

Anyway, I had to make a change to the first query in order to get the correct results for the first query...
SELECT * from tbl_Pages WHERE ParentPageID = @.ID (@.ID is taken from a class BasicPageInfo.ID)

However, when using the 2nd query for the childrepeater, I get the following error:
System.NullReferenceException: Object reference not set to an instance of an object. on Line 47 childRepeater.DataSource = ds;

When I run the query in SQL Server Management Studio I get the correct result when I pass, for example, 32 for the @.ID.

This error was happening when i used the first query the way you had indicated as well. Seems to me, that for some reason, the dataset is not being populated correctly.

This is what I intend on doing. Before trying to use a nested repeater, these were my functions to create a navigation menu. This worked just fine, but I couldn't figure out how to insert a nested repeater into the below code (I took this over from another developer).

public partial class Controls_RightSideBarNavTree : BaseControl
{
protected void Page_Load(object sender, EventArgs e)
{
PagesDB sdb1 = new PagesDB();
BasicPageData[] dataArray1 = null;
int num1 = -1;

if (base.BasicPageInfo != null)
{
dataArray1 = sdb1.Page_GetAllWithParentID(base.BasicPageInfo.ID);
num1 = base.BasicPageInfo.ID;
if (dataArray1.Length == 0)
{
dataArray1 = sdb1.Page_GetAllWithParentID(base.BasicPageInfo.ParentPageID);
num1 = base.BasicPageInfo.ParentPageID;
}

/* Checks to see if page is a content page. If so, applied Header Text */
if (num1 > 0)
{
BasicPageData data1 = sdb1.Page_GetItemByID(num1);
this.lblHeader.Text = data1.PageName;
this.Repeater1.DataSource = dataArray1;
this.Repeater1.DataBind();
}
else
{
this.lblHeader.Text = "";
}
}

}

protected void Repeater1_ItemCreated(object sender, RepeaterItemEventArgs e)
{
if ((e.Item.ItemType == ListItemType.Item) || (e.Item.ItemType == ListItemType.AlternatingItem))
{
BasicPageData data1 = (BasicPageData)e.Item.DataItem;
HyperLink link1 = (HyperLink)e.Item.FindControl("hypItem");
HtmlGenericControl control1 = (HtmlGenericControl)e.Item.FindControl("liItem");
if (null == data1) return;
link1.Text = data1.PageName;
link1.NavigateUrl = GlobalData.TRCBaseURL + "content/default.aspx?" + BaseControl.PAGE_GUID + "=" + data1.GUID.ToString() + "&" + BaseControl.TOOLBARPAGE_GUID + "=" + base.ToolbarPageGUID.ToString();
if (base.PageGUID.Equals(data1.GUID))
{
control1.Attributes.Add("class", "subOn");
}
}
}

}

|||

Hi adarwich,

From the context, I didn't see the control childRepeater has been initialized. Please check if childRepeater is a valid control on the page and it has a valid reference to an object.