Friday, April 8, 2011

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.

9 comments:

  1. Thanks a lot!! you article explains autocomplete in the easiest way as compared to the other articles available on net

    ReplyDelete
  2. Can u tell me how to fetch data from database with Cascading Dropdown in AJAX using vb?

    ReplyDelete
    Replies
    1. Hi Ratika,give me your mail id i will send you the sample code for that here is my personal mailid( ganaparthi.srinu@gmail.com )

      Delete
  3. i did the same...but it is not working...

    ReplyDelete
  4. I am creating webservice for autocomplete text box. That service runs on my server but that service add to my Application my application run on my local machine then how can i give service path to AutoCompleteExtender control.
    canu help me Please. one more thing your code is not working

    Thanks Ramani

    ReplyDelete
  5. can u send me sample code in my mail ID ramniranjan.it@gmail.com
    Thanks
    Ramani

    ReplyDelete
  6. Thank you srinivas! well explained..

    ReplyDelete
  7. hi
    How to fetch the data from database using id value.
    Eg: i m using id value as ["Literial"] and i having a table "advance_amount" i want fetch cost column from tat.

    ReplyDelete
  8. Really Nice Information,Thank You Very Much For Sharing.
    Web Development Company

    ReplyDelete