AutoComplete TextBox (Using AJAX AutoCompleteExtender) from Database

 

Recently I tried AutoComplete extender (AJAX toolkit) in one of my on going web application. What I suppose to do is, user shall be able to get the AutoSense list of available products from database on typing the name of the product in a TextBox. When 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. BTW, Ajax extension and Ajax Tool Kit should already be installed to implement any AJAX extenders. Here are the steps how to accomplish an AutoComplete TextBox from Database:

 

  • We start by creating new website. Create a new website by selecting “ASP.NET AJAX-Enabled Web Site” from installed templates in “New Web Site” window.

  • ScriptManager” would already be there in your webpage (Default.aspx), as we have selected AJAX Enabled Website.

  • Now drag and drop a Textbox from your Toolbox and AutoCompleteExtender to your webpage.

  • Then add a webservice to your project as WebService.asmx.

  • First of all, you need to Import “System.Web.Script.Services” namespace and add the “ScriptService” reference to the webserive.

  • Next, just need to write a simple webmethod ‘GetProducts’ to fetch the data from the Product table which will return the string array with product names.

  • We will pass the “PrefixText” to this webmethod; I mean the characters that are typed by the user in the textbox to get the exact AutoComplete Product list form Database that start with the characters typed by the user.

  • Here is the complete webmethod for that:

        using System;
        using System.Configuration;
        using System.Data;
        using System.Data.SqlClient;
        using System.Web;
        using System.Collections;
        using System.Web.Services;
        using System.Web.Services.Protocols;
        using System.Web.Script.Services;

        /// <summary>
        /// Summary description for WebService
        /// </summary>
        [ScriptService]
        [WebService(Namespace = "http://tempuri.org/")]
        [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
        public class WebService : System.Web.Services.WebService
        {
            public WebService()
            {
                //Uncomment the following line if using designed components
                //InitializeComponent();
            }        
            [WebMethod]
            public string[] GetProducts(string prefixText)
            {
            string sql = "Select * from product Where name like @prefixText";
            SqlDataAdapter da = new SqlDataAdapter(sql, ConfigurationManager.AppSettings["DBConn"]);
            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["name"].ToString(), i);
                i++;
            }
            return items;
           }
        }

 

  • you can see that we have passed prefixText as argument in above webmethod , which 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.

  • In the your webpage, set the AutoCompleteExtender’s TargetControlID property to the TextBox Id. You also need to set ServicePath property as WebService.asmx, ServiceMethod as GetProducts and MinimimPrefixLength as 1.

  • So, your web page design code will be something like:

    <form id="form1" runat="server">
        <asp:ScriptManager ID="ScriptManager1" runat="server" />
        <div>
            <asp:TextBox ID="TextBox1" runat="server" Width="785px"></asp:TextBox>
            <cc1:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server" MinimumPrefixLength="1" ServiceMethod="GetProducts" ServicePath="WebService.asmx" TargetControlID="TextBox1">
            </cc1:AutoCompleteExtender>
        </div>
    </form>

Thats it! Quite simple and a useful feature that user would like to have. Find the sample Code available to Download in attechment.

 

 

 

 

Comments

# re: AutoComplete TextBox (Using AJAX AutoCompleteExtender) from Database

Wednesday, June 25, 2008 2:29 PM by TheDirtyBird

Great post!  I'm still having trouble with my connection string.  Do you mind posting your web.config so I can see your <AppSettings>.  Or could you tell me what my connection string will be.  I have a database named tblNames.mdf in the App_Data folder.  So is this correct?

<appSettings>

<add key="DBConn" value="App_Data\tblNames.mdf" />

</appSettings>

Your attachment AJAXEnabledWebSite1.zip wont unzip.

# re: AutoComplete TextBox (Using AJAX AutoCompleteExtender) from Database

Wednesday, June 25, 2008 2:30 PM by TheDirtyBird

What does your <appSettings>  look like in the web.config?

# AutoComplete TextBox (Using AJAX AutoCompleteExtender) from Database &laquo; KaushaL.NET

Pingback from  AutoComplete TextBox (Using AJAX AutoCompleteExtender) from Database « KaushaL.NET

# re: AutoComplete TextBox (Using AJAX AutoCompleteExtender) from Database

Thursday, September 25, 2008 2:32 PM by enthusiast

I did exactly same thing but my website is not working although the xml file is getting generated.

I am using Microsoft Visual Studio 2005.

Is the problem due to the fact that the web site in which I ma using the code is not ASP/AJAX enabled website ?

Any suggestions please

# re: AutoComplete TextBox (Using AJAX AutoCompleteExtender) from Database

Monday, September 14, 2009 1:55 AM by pdyfen

id2Cpp  <a href="dnamisitjjog.com/.../a>, [url=http://uwczgiycoiqw.com/]uwczgiycoiqw[/url], [link=http://ooobtucbmauc.com/]ooobtucbmauc[/link], http://nybfopxnfxjq.com/

# re: AutoComplete TextBox (Using AJAX AutoCompleteExtender) from Database

Wednesday, September 23, 2009 4:22 PM by Josue

Thanks friend, my code were wrong cause my visibility modificator was static and can't access the method in the web services, thanks again!!!!

# re: AutoComplete TextBox (Using AJAX AutoCompleteExtender) from Database

Wednesday, September 30, 2009 12:23 PM by Geofrey

Great Post.

# re: AutoComplete TextBox (Using AJAX AutoCompleteExtender) from Database

Thursday, December 03, 2009 2:11 PM by Basma

You can make WebMethod like that

[WebMethod]

   public string[] GetProducts(string prefixText)

   {

       string sql = "Select Name from Contacts Where Name like @prefixText";

       SqlDataAdapter da = new SqlDataAdapter(sql, ConfigurationManager.AppSettings["DBConn"]);

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

       DataTable dt = new DataTable();

       da.Fill(dt);

       if (dt.Rows.Count != 0)

       {

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

           int i = 0;

           foreach (DataRow dr in dt.Rows)

           {

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

               i++;

           }

           return items;

       }

       else

       {

           string[] items = new string[1];

           items.SetValue("No Name Match", 0);

           return items;

       }

   }

To show if the name you write dosen't match any name inside DB

# re: AutoComplete TextBox (Using AJAX AutoCompleteExtender) from Database

Monday, December 28, 2009 2:08 PM by Prasenjit Basu

I have done the auto complete extender as give above but from the text box no output is coming. Please suggest on the same. Here is my code:

APSX CODE:

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

                 <Services>

               <asp:ServiceReference Path="~/AutoCompleateWebService.asmx" />

           </Services>

               </asp:ScriptManager>

<asp:TextBox ID="txtClientName" runat="server" Width="268px"></asp:TextBox>

               <cc1:AutoCompleteExtender ID="AutoCompleteExtenderClientScearch" runat="server" TargetControlID="txtClientName"

                ServicePath="AutoCompleateWebService.asmx" ServiceMethod="GetClientScearchDetails" MinimumPrefixLength="3"

                 CompletionInterval="1000" EnableCaching="true" CompletionSetCount="10">

               </cc1:AutoCompleteExtender>

WEB SERVICE CODE:

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

[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

[System.Web.Script.Services.ScriptService]

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

   public AutoCompleateWebService () {

       //Uncomment the following line if using designed components

       //InitializeComponent();

   }

   [WebMethod]

   public string[] GetClientScearchDetails(string perfixText)

   {

       try

       {

           Utility objUtil = new Utility();

           DataSet dsresult = new DataSet();

           dsresult = objUtil.getClientDetailsForAutoCompleate(perfixText);

           int count = dsresult.Tables[0].Rows.Count;

           if (count == 0)

               count = 10;

           List<string> LsClientName = new List<string>(count);

           if (dsresult.Tables[0].Rows.Count > 0)

           {

               foreach (DataRow drrow in dsresult.Tables[0].Rows)

               {

                   string strClient = drrow[0].ToString();

                   LsClientName.Add(strClient);

               }

               return LsClientName.ToArray();

           }

           else

           {

               LsClientName.Add("No Such Client Exist");

               return LsClientName.ToArray();

           }

       }

       catch (Exception ex)

       {

           throw ex;

       }

   }

# re: AutoComplete TextBox (Using AJAX AutoCompleteExtender) from Database

Tuesday, December 29, 2009 3:48 AM by prasenjit basu

I have tried to implement the auto complete extender but it is not calling the perfectly working web service. I already implemented the same it was working now i don't know why it is not working please help me out.

ASP CODE:

<asp:ScriptManager id="ScriptManager1" runat="server">                  <Services>                <asp:ServiceReference Path="~/AutoCompleateWebService.asmx" />            </Services>                </asp:ScriptManager>

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

<Services>

               <asp:ServiceReference Path="~/AutoCompleateWebService.asmx" />

           </Services>

               </asp:ScriptManager>

<asp:TextBox ID="txtClientName" runat="server" Width="268px"></asp:TextBox>

               <cc1:AutoCompleteExtender ID="AutoCompleteExtenderClientScearch" runat="server" TargetControlID="txtClientName"

                ServicePath="AutoCompleateWebService.asmx" ServiceMethod="GetClientScearchDetails" MinimumPrefixLength="3"

                 CompletionInterval="1000" EnableCaching="true" CompletionSetCount="10">

               </cc1:AutoCompleteExtender>

The WEB SERVICE Code:

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

[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

[System.Web.Script.Services.ScriptService]

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

   public AutoCompleateWebService () {

       //Uncomment the following line if using designed components

       //InitializeComponent();

   }

   [WebMethod]

   public string[] GetClientScearchDetails(string perfixText)

   {

       try

       {

           Utility objUtil = new Utility();

           DataSet dsresult = new DataSet();

           dsresult = objUtil.getClientDetailsForAutoCompleate(perfixText);

           int count = dsresult.Tables[0].Rows.Count;

           if (count == 0)

               count = 10;

           List<string> LsClientName = new List<string>(count);

           if (dsresult.Tables[0].Rows.Count > 0)

           {

               foreach (DataRow drrow in dsresult.Tables[0].Rows)

               {

                   string strClient = drrow[0].ToString();

                   LsClientName.Add(strClient);

               }

               return LsClientName.ToArray();

           }

           else

           {

               LsClientName.Add("No Such Client Exist");

               return LsClientName.ToArray();

           }

       }

       catch (Exception ex)

       {

           throw ex;

       }

   }

# Autocomplete extender is not working | The Largest Forum Archive

Pingback from  Autocomplete extender is not working | The Largest Forum Archive

# re: AutoComplete TextBox (Using AJAX AutoCompleteExtender) from Database

Wednesday, November 03, 2010 2:40 AM by mittul1990

JAI MATADI ..

hello sir ..

this is great tutorial ..

and i did everything which u told in this but just made a slightly change in my code ..

i put a full path at

SqlDataAdapter da = new SqlDataAdapter(sql, ConfigurationManager.AppSettings["DBConn"]);

inplace of "ConfigurationManager etc..."

i put my full database path like this

@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\Chandrik Tools\My Documents\Visual Studio 2010\WebSites\ajax toolkit websites\App_Data\Database.mdf;Integrated Security=True;User Instance=True"

as i dont see any "APPSETTINGS" etc .. names in my WRB.CONFIG ..

but m not getting any result when i m running my page ..

my code generating very well without any errors .. but the name suggestion results are not coming ..

please tell me sir what wrong i m doing .. i have done everything .. yet not able to get the result .. :(

# re: AutoComplete TextBox (Using AJAX AutoCompleteExtender) from Database

Friday, December 03, 2010 12:13 PM by Stanja

I have the autocomplete extender working but would like to pass a different value from what I am displaying. I am displaying a lastname, firstname, date of birth.I would like to pass an id value. Can you give me some direction on how to accomplish this. Any help would be appreciated.