Fetch data from database with Autocomplete Exender Textbox in AJAX

 This article explains to fill an AutoComplete Textbox from the data fetched from database.

Everyone knows about the AutoComplete Textbox. It is an ASP.NET AJAX extender that can be attached to any TextBox control. When the user types some letters in the Textbox, a popup panel will come to action and displayed the related words.So that the user can choose exact word from the popup panel. Here I tried to explain how this AutoComplete fetches data from the database through a Webservice.

Open Microsoft Visual Studio, click on New Website. Give website name as AutocompleteExenderSample.

Now drag and drop a Textbox from your Toolbox. Then drag and drop a ScriptManager and AutoCompleteExtender to your Default.aspx page. Then add a webservice to your project as WebService.asmx. First thing you have to do is to add the ScriptService reference to the webserive as follows.

/// <summary>

/// Summary description for WebService

/// </summary>

[WebService(Namespace = "http://tempuri.org/")]

[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.

[System.Web.Script.Services.ScriptService]

public class WebService : System.Web.Services.WebService {

    SqlConnection cn = new SqlConnection("Data Source=Local;Database=Northwind;trusted_connection=true");

    public WebService () {

        //Uncomment the following line if using designed components

        //InitializeComponent();

    }

    [WebMethod]

    public string[] GetEmployeeName(string prefixText)

    {

        //int count = 10;

        string sql;

        sql = "Select FirstName from Employees Where FirstName like '" + prefixText + "' + '%'";

        SqlDataAdapter da = new SqlDataAdapter(sql, cn);

        //da.SelectCommand.Parameters.Add("@prefixText", SqlDbType.VarChar, 50).Value = prefixText + "%";

        DataTable dt = new DataTable();

        da.Fill(dt);

        string[] items = new string[dt.Rows.Count];

        int i = 0;

        foreach (DataRow dr in dt.Rows)

        {

            items.SetValue(dr[0].ToString(), i);

            i++;

        }

        return items;

    }

}

The above webmethod takes prefixText as argument, sends it to the query to fetch only the related words that starts with the prefixText values. Then it returns the result as an array of strings.

Next, in the Default.aspx page, set the AutoCompleteExtender’s TargetControlID property to the TextBox Id. Now you can see a new Extenders Tab is added in the Textbox’s Property window. Set ServicePath as WebService.asmx, ServiceMethod as GetEmployeeName and MinimimPrefixLength as 1.

<asp:ScriptManager ID="ScriptManager1" runat="server">

    </asp:ScriptManager>

    <asp:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server"

    MinimumPrefixLength="1" ServiceMethod="GetEmployeeName"

    ServicePath="~/WebService.asmx" TargetControlID="TextBox1"></asp:AutoCompleteExtender>

        <asp:TextBox ID="TextBox1" runat="server" Width="213px"></asp:TextBox>

Now, it’s time to run the project. Select the Default.aspx and click on View in Browser. You can see the excellent application starts to run. Type your Employee Name first letter. See all the Employee list starts with that letter will appear in the popup.

Dynamic Accordion control with Paging

jaIn this post we are going to learn how to bind the data to accordion control dynamically with paging option.

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. The Accordion is implemented as a web control that contains AccordionPane web controls. Each AccordionPane control has a template for its Header and its Content. We keep track of the selected pane so it stays visible across postbacks.

Create data table as follows and insert 10 records as you like

CREATE TABLE Projects(

    ProjectId int identity(1,1) NOT NULL,

    ProjectName nvarchar(500) ,

    [Description] nvarchar(max)

)

Create new Website named as DynamicAccordionPaging

Copy and paste below code in Default.aspx

<head runat="server">

    <title></title>

    <style>

    /* Accordion */

.accordionHeader

{

    border: 1px solid #2F4F4F;

    color: white;

    background-color: #2E4d7B;

       font-family: Arial, Sans-Serif;

       font-size: 12px;

       font-weight: bold;

    padding: 5px;

    margin-top: 5px;

    cursor: pointer;

}

#master_content .accordionHeader a

{

       color: #FFFFFF;

       background: none;

       text-decoration: none;

}

#master_content .accordionHeader a:hover

{

       background: none;

       text-decoration: underline;

}

.accordionHeaderSelected

{

    border: 1px solid #2F4F4F;

    color: white;

    background-color: #5078B3;

       font-family: Arial, Sans-Serif;

       font-size: 12px;

       font-weight: bold;

    padding: 5px;

    margin-top: 5px;

    cursor: pointer;

}

    </style>

</head>

<div>

        <asp:ScriptManager ID="ScriptManager1" runat="server">

        </asp:ScriptManager>

    <table>

<tr>

<asp:HiddenField ID="txtCount" runat="server" />

<td>

    <asp:UpdatePanel ID="UpdatePanel1" runat="server">

    <ContentTemplate >

  <asp:Panel ID="pnlShowProjects" runat="server">

        <asp:Accordion ID="acc" runat="server"

         SelectedIndex="0" EnableViewState="false"

         FadeTransitions="false" FramesPerSecond="40"

         TransitionDuration="250" AutoSize="None"

         RequireOpenedPane="false" SuppressHeaderPostbacks="true"

         HeaderCssClass="accordionHeader"

        HeaderSelectedCssClass="accordionHeaderSelected"

        ContentCssClass="accordionContent">

             <HeaderTemplate>

             <table width="100%">

             <tr>

             <td align="left">

             <asp:Label ID="lblProjectName" runat="server" Text='<%#Eval("ProjectName") %>'></asp:Label>

             </td>

             </tr>

             </table>

            </HeaderTemplate>

            <ContentTemplate>

            <table>

            <tr>

            <td align="left">

               <%# Eval("Description") %>

            </td>

            </tr>

            </table>

          </ContentTemplate>

        </asp:Accordion>

        </asp:Panel>

        <table align="right">

        <tr>

        <td >

        <asp:ImageButton ID="imgPrevious" runat="server"

                 ImageUrl="~/Images/previous.gif" onclick="imgPrevious_Click"

                  AlternateText="Previous" />

        </td>

        <td>

         <asp:ImageButton ID="imgNext" runat="server"

                 ImageUrl="~/Images/next.gif" onclick="imgNext_Click"

                AlternateText="Next" />

        </td>

        <td>

           <asp:Label ID="lblTotal" runat="server"></asp:Label>

        </td>

        </tr>

        </table>

        </ContentTemplate>

        </asp:UpdatePanel>

</td>

</tr>

</table>

    </div>

 Copy and paste below code in  Default.aspx.cs

PagedDataSource _pagedsource;

    SqlConnection conn = new SqlConnection("Data Source=.;Database=Sample;trusted_connection=true");

    protected override void OnInit(EventArgs e)

    {

        _pagedsource = new PagedDataSource();

       

        // creating handler for page unload event

        Unload += new EventHandler(Default_Unload);

        base.OnInit(e);

    }

    void Default_Unload(object sender, EventArgs e)

    {

        // we save the data source in a session to reload when posback

        Session["newslist"] = _pagedsource;

    }

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            getallprojects();

            lblTotal.Text = setpaging();

        }

        else

            if (Session["newslist"] != null)

            {

                _pagedsource = (PagedDataSource)Session["newslist"];

            }

    }

    void getallprojects()

    {

        conn.Open();

        SqlDataAdapter da = new SqlDataAdapter("select ProjectName,[Description] from Projects", conn);

        DataSet ds = new DataSet();

        da.Fill(ds);

        // you fill the PagedDataSource by set the DataSource property with the content of the data to display and page (e.g. DataTable, Collection...)

        _pagedsource.DataSource = ds.Tables[0].DefaultView;

        // CurrentPageIndex - what page to display

        _pagedsource.CurrentPageIndex = 0;

        txtCount.Value = ds.Tables[0].Rows.Count.ToString();

        initPagedDataSource();

        if (_pagedsource.PageSize == int.Parse(txtCount.Value))

        {

            imgPrevious.Visible = false;

            imgNext.Visible = false;

        }

        else

        {

            imgPrevious.Visible = true;

            imgNext.Visible = true;

        }

    }

    public string setpaging()

    {

        int from = _pagedsource.FirstIndexInPage + 1;

        int to = _pagedsource.FirstIndexInPage + _pagedsource.PageSize;

        if (to > int.Parse(txtCount.Value))

            to = int.Parse(txtCount.Value);

        return "Showing " + from.ToString() + " - " + to.ToString() + " of " + txtCount.Value;

    }

    private void initPagedDataSource()

    {

        // it is necessary to set this property true

        _pagedsource.AllowPaging = true;

        // how many items to show on one page

        _pagedsource.PageSize = 5;

        // now, bind the Accordion control to the PagedDataSource object

        acc.DataSource = _pagedsource;

        acc.DataBind();

        // disable Previous (Last) button when showing the first page of the PageDataSource

        imgPrevious.Enabled = !_pagedsource.IsFirstPage;

        // disable Next button when showing the last page of the PageDataSource

        imgNext.Enabled = !_pagedsource.IsLastPage;

    }

    protected void imgPrevious_Click(object sender, ImageClickEventArgs e)

    {

        // when clicking Last button, decrement the CurrentPageIndex

        _pagedsource.CurrentPageIndex--;

        initPagedDataSource();

        lblTotal.Text = setpaging();

    }

    protected void imgNext_Click(object sender, ImageClickEventArgs e)

    {

        // when clicking Next button, increment the CurrentPageIndex

        _pagedsource.CurrentPageIndex++;

        initPagedDataSource();

        lblTotal.Text = setpaging();

    }

Finally we are able to implement paging with Accordion control 

Foundations of ASP.NET AJAX (Expert's Voice in .NET)