Status: Solved
Priority: Medium
Security: Public
Views: 171
Last Modified: 2012-12-31

breadcrumbs

Hi

I have to create a breadcrumbs trails for the Categories and their subcategories as u have seen it in many sites...

Maincetegory > Subcategory >subsubcategory > subsubsubcategory >items

This is the depth of the category..
category table i have Categoryid and categoryparentid where categoryid is linked to it... I have to link it with the below table

SELECT [Id], ,[Name], parentcategoryid
FROM [Category]

Id Name parentcategoryid
1 Books 0
2 Computers 0
3 Desktops 2
4 Notebooks 2
5 Accessories 3
6 Software 2
7 Games 2
8 Electronics 0
9 Camera, photo 8

like this

and I want to have a breadcumps which shows

Books
Computers
Computers->Desktops
Computers->Notebooks
Computers->Desktops->Accessories

This should be dynamic! thanks a lot!

any help will be appreciated

bsarahim

Asked: 2012-12-28

Advertise Here

ASP.NET

1 Solution

LVL 35

Robert SchuttSoftware EngineerCommented: 2012-12-28

Would this be helpful:

CREATE FUNCTION GetCategoryTrail 
(
	@p1 int
)
RETURNS nvarchar(1000)
AS
BEGIN
	DECLARE @Result nvarchar(1000)
    SELECT @Result = CASE [parentcategoryid] WHEN 0 THEN [Name] ELSE dbo.GetCategoryTrail([parentcategoryid]) + ' > ' + [Name] END
    FROM [Category]
	WHERE [Id] = @p1

	RETURN @Result
END
GO

Open in new window

Then call it like this:

SELECT [Id], [Name], dbo.GetCategoryTrail([Id]) AS [Trail]
FROM [Category]

Open in new window

bsarahimAuthor Commented: 2012-12-29

Thanks for that and it is working!

I'm little confused, how do I can show

Computers->Desktops->Accessories (display)

When I click Accessories from sitemap or breadcumps, that will show accessories items

and when I click on the desktop, it should show the desktop items.. whether I should write asp.net routing?

Im aware of how to show the items based on the category id via datalist/gridview...
delete2.png

LVL 35

Robert SchuttSoftware EngineerCommented: 2012-12-29

I'm not sure what you're asking, it sounds like you want to DataBind a label the same way you do with a grid? I'd have to see a bit more of your current page and/or code (showing the connection and category selection) to try to show you a way to do this in the same way, I would normally use a piece of code in the page load that reads the result of a SQL command using the current category id as parameter but maybe you're using 'asp:' tags to bind the data?

bsarahimAuthor Commented: 2012-12-29

let me simplify

Let say I have menu where categories are being displayed, which is hardcoded in jquery, div tags

Under computers Menu category I have Accessories link, when the user click the Accessories.

after above menu, the display should show Computers ->Desktops->Accessories
All the computer, desktop, accessories, are individual hyperlinks

Hope you understood, and im going to second

2nd requirement, based on the above requirement:

when I click any of above computer-> desktop->, accessories , I will fetch the data based on the category code in the datalist...

Pls let me know, if it is not clear...

LVL 35

Robert SchuttSoftware EngineerCommented: 2012-12-29

I think both requirements can be met by having the function return html links:

ALTER FUNCTION GetCategoryTrail 
(
	@p1 int
)
RETURNS nvarchar(1000)
AS
BEGIN
	DECLARE @Result nvarchar(1000)
    SELECT @Result = CASE [parentcategoryid]
			WHEN 0 THEN '<a href="yourpage.aspx?catid=' + convert(nvarchar, [Id]) + '">' + [Name] + '</a>'
			ELSE dbo.GetCategoryTrail([parentcategoryid]) + ' &gt; ' + '<a href="yourpage.aspx?catid=' + convert(nvarchar, [Id]) + '">' + [Name] + '</a>'
		END
    FROM [Category]
	WHERE [Id] = @p1

	RETURN @Result
END
GO

Open in new window

make sure the page and parameter name are changed (twice) to match your project.

LVL 35

Robert SchuttSoftware EngineerCommented: 2012-12-29

Here's one way to work with the output generated by the function as changed in my previous post. Note that I used the pagename and querystringparameter name to match the arbitrary examples I used.

yourpage.aspx:

<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="yourpage.aspx.vb" Inherits="WebAppQ_27979539.WebForm1" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=.\sqlexpress;User Id=ee;Password=ee;" SelectCommand="SELECT dbo.GetCategoryTrail([Id]) FROM [Category] WHERE [Id] = @Id">
        <SelectParameters><asp:QueryStringParameter Type="Int32" Name="Id" QueryStringField="catid" DefaultValue="5" /></SelectParameters>
    </asp:SqlDataSource>
    <form id="form1" runat="server">
    <div>
        <asp:Label ID="Label1" runat="server" Text="Label" ></asp:Label>
    </div>
    </form>
</body>
</html>

Open in new window

yourpage.aspx.vb:

Imports System.Data.SqlClient

Public Class WebForm1
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim dv As DataView = SqlDataSource1.Select(New DataSourceSelectArguments())
        Label1.Text = dv.Table.Rows(0).Item(0)
    End Sub

End Class

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

breadcrumbs

Join & Write a Comment Already a member? Login.

Featured Post