The Accordion is a web control that allows you to provide multiple panes and display them one at a time. It is like having several CollapsiblePanels where only one can be expanded at a time.
How to populate data from the SQL SERVER database and integrate with Accordion Control
I tried from the internet but hardly found something good which is so friendly.
I decided to make a code for this.
First create tables in the database
CREATE TABLE [dbo].[Category](
[CategoryId] [int] IDENTITY(1,1) NOT NULL,
[CatName] [nvarchar](50) NULL,
[CatDesc] [nvarchar](255) NULL
)
CREATE TABLE [dbo].[SubCategory](
[SubCategoryID] [int] IDENTITY(1,1) NOT NULL,
[CategoryID] [int] NOT NULL,
[SubCategoryName] [varchar](30) NULL
)
Inserting values for Main Category Table
Insert Into Category (CatName,CatDesc) Values (‘Hospital’,‘Hospital’)
Go
Insert Into Category (CatName,CatDesc) Values (‘School’,‘School’)
Go
Insert Into Category (CatName,CatDesc) Values (‘Hotel’,‘Hotel’)
Select * from Category
Result:
Some values for Sub Category Table
Insert Into SubCategory (CategoryID,SubCategoryName) Values (1,‘Hospital 1′)
Go
Insert Into SubCategory (CategoryID,SubCategoryName) Values (1,‘Hospital 2′)
Go
Insert Into SubCategory (CategoryID,SubCategoryName) Values (1,‘Hospital 3′)
Go
Insert Into SubCategory (CategoryID,SubCategoryName) Values (2,‘School 1′)
Go
Insert Into SubCategory (CategoryID,SubCategoryName) Values (2,‘School 2′)
Go
Insert Into SubCategory (CategoryID,SubCategoryName) Values (3,‘Hotel 1′)
Go
Insert Into SubCategory (CategoryID,SubCategoryName) Values (3,‘Hotel 2′)
Go
Insert Into SubCategory (CategoryID,SubCategoryName) Values (3,‘Hotel 3′)
Go
Insert Into SubCategory (CategoryID,SubCategoryName) Values (3,‘Hotel 4′)
Go
Select * from SubCategory
Result:
Now we have two tables, let’s create a stored procedure.
CREATE PROCEDURE [dbo].[PROC_CATEGORY_GETALL]
AS
BEGIN
SELECT
DISTINCT CATEGORY.CATEGORYID,
ISNULL(CATEGORY.CATNAME,”) AS CATNAME
FROM
CATEGORY
SELECT
CATEGORY.CATEGORYID,
ISNULL(CATEGORY.CATNAME,”) AS CATNAME,
ISNULL(SUBCATEGORY.SUBCATEGORYNAME,”) AS SUBNAME
FROM
CATEGORY INNER JOIN SUBCATEGORY
ON CATEGORY.CategoryID=SubCategory.CategoryID
ORDER BY CATEGORY.CATNAME ASC
END
GO
In the above stored procedure I am returning two tables, Main and Sub Category both, So I don’t need to go and touch database for each Category.
I don’t want for each Category it will go back and forth toward server.
Create any Webpage in your AJAX Enabled web application
In a Webpage ASPX write below code
<table border=”0″ cellpadding=”0″ cellspacing=”0″ width=”98%”>
<tr>
<td>
<cc1:Accordion ID=”MyAccordion” runat=”Server” SelectedIndex=”0″ HeaderCssClass=”accordionHeader”HeaderSelectedCssClass=”accordionHeaderSelected” ContentCssClass=”accordionContent”AutoSize=”None” FadeTransitions=”true” TransitionDuration=”250″ FramesPerSecond=”40″
RequireOpenedPane=”false” SuppressHeaderPostbacks=”true”>
</cc1:Accordion>
</td>
</tr>
</table>
Now CODE Behind.
Private Sub PopulateGrid()
Dim sqlConn As New SqlConnection
Dim sqlCmd As New SqlCommand(“PROC_CATEGORY_GETALL”, sqlConn)
Dim DA As New SqlDataAdapter
Dim ds As New DataSet
Dim intRow As Integer
Dim intRowPenal As Integer
Dim acpPane As AjaxControlToolkit.AccordionPane
Dim lblHeader As Label
sqlConn = “Opened Connection”e.g.”openConnection()returns me a connection object”
sqlCmd.Connection = sqlConn
sqlCmd.CommandType = CommandType.StoredProcedure
DA.SelectCommand = sqlCmd
DA.Fill(ds)
For intRow = 0 To ds.Tables(0).Rows.Count – 1
lblHeader = New Label
lblHeader.Text = ds.Tables(0).Rows(intRow)(“CatName”)
acpPane = New AjaxControlToolkit.AccordionPane
acpPane.HeaderContainer.Controls.Add(lblHeader)
�
Dim dv As DataView = ds.Tables(1).DefaultView
dv.RowFilter = “CATEGORYID=’” + ds.Tables(0).Rows(intRow)(“CATEGORYID”).ToString + “‘”
Dim lblContent As New Label
For intRowPenal = 0 To dv.Count – 1
lblContent.Text = lblContent.Text & “<div style=’padding-top: 5px; padding-left: 10px;’><a href=’” & ResolveUrl(dv.Item(intRowPenal)(“SUBCATEGORYNAME”)) & “‘>” + dv.Item(intRowPenal)(“SUBNAME”) + “</a>” & “</div>”
Next
acpPane.ContentContainer.Controls.Add(lblContent)
MyAccordion.Panes.Add(acpPane)
Next
DA.Dispose()
DA = Nothing
sqlCmd.Dispose()
sqlCmd.Connection.Close()
sqlCmd = Nothing
sqlConn.Close()
sqlConn = Nothing
�
End Sub
Note : Change Sub Category Hyperlink as per your wish.
Leave your message if it solve your problem.
Joggee
