Joggee

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