Ajax AutoCompleteExtender with MSSQL datasource

I’m building a form and wanted to use the autocompleteextender control of the Ajax Toolkit. Having limited skills with ASP.net and C#, I began by looking for examples online. However, nothing I found and tried seemed to be working. I finally found a simple example here that helped me get on my way.

I’ve reproduced my code below as a self-reference, and to hopefully help someone else avoid troubles with this control.

Default.aspx

<!-- Need to have the toolkitscript manager in your page -->
<asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server" EnablePageMethods="true" >
</asp:ToolkitScriptManager>
 
<!-- Textbox that the autocomplete will target -->
<asp:TextBox ID="CEMPNAMETextBox" runat="server" Text='<%# Bind("CEMPNAME") %>' />
 
<!-- AutocompleteControl -->
<asp:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server"
                            TargetControlID="CEMPNAMETextBox"
                            MinimumPrefixLength="2"
                            CompletionSetCount="5" 
                            ServiceMethod="GetCompletionList" 
                            UseContextKey="True" />

TargetControlID – the ID of the textbox that we’ve chosen to use
ServiceMethod – name of the method as defined in your codebehind file

Default.aspx.cs (C#)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
 
namespace ITIventory
{
    public partial class _Default : System.Web.UI.Page
    {
            // AUTO COMPLETE SECTION
            [System.Web.Services.WebMethodAttribute(), System.Web.Script.Services.ScriptMethodAttribute()]
            public static string[] GetCompletionList(string prefixText, int count, string contextKey)
            {
                List ListCempName = new List(); // List Object
                try
                {
                    // Open the connection (can re-use connection string as defined in web.config)
                    SqlConnection SqlCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ITInventoryConnectionString"].ConnectionString);
                    SqlCon.Open();
 
                    // Create a Command
                    SqlCommand SqlComm = new SqlCommand();
                    SqlComm.Connection = SqlCon;
 
                    // Add a employee name SQl Parameter
                    SqlComm.Parameters.Add("@cempname", SqlDbType.VarChar).Value = prefixText; // retrievable throught prefixText parameter
 
                    // Query for get country name from database
                    SqlComm.CommandType = CommandType.Text;
                    SqlComm.CommandText = "SELECT cempname FROM BMEMP WHERE (BMEMP.LISTERM = 'false') AND (BMEMP.cempname LIKE ''+@cempname+'%') ";
 
                    // Read the data and add in List object.
                    SqlDataReader SqlDr = SqlComm.ExecuteReader();
 
                    if (SqlDr != null)
                    {
                        while (SqlDr.Read())
                        {
                            ListCempName.Add(SqlDr["cempname"].ToString());
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                return ListCempName.ToArray();
            }
       }
}

using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data; using System.Data.SqlClient; using System.Configuration; namespace ITIventory { public partial class _Default : System.Web.UI.Page { // AUTO COMPLETE SECTION [System.Web.Services.WebMethodAttribute(), System.Web.Script.Services.ScriptMethodAttribute()] public static string[] GetCompletionList(string prefixText, int count, string contextKey) { List ListCempName = new List(); // List Object try { // Open the connection (can re-use connection string as defined in web.config) SqlConnection SqlCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ITInventoryConnectionString"].ConnectionString); SqlCon.Open(); // Create a Command SqlCommand SqlComm = new SqlCommand(); SqlComm.Connection = SqlCon; // Add a employee name SQl Parameter SqlComm.Parameters.Add("@cempname", SqlDbType.VarChar).Value = prefixText; // retrievable throught prefixText parameter // Query for get country name from database SqlComm.CommandType = CommandType.Text; SqlComm.CommandText = "SELECT cempname FROM BMEMP WHERE (BMEMP.LISTERM = 'false') AND (BMEMP.cempname LIKE ''+@cempname+'%') "; // Read the data and add in List object. SqlDataReader SqlDr = SqlComm.ExecuteReader(); if (SqlDr != null) { while (SqlDr.Read()) { ListCempName.Add(SqlDr["cempname"].ToString()); } } } catch (Exception ex) { throw new Exception(ex.Message); } return ListCempName.ToArray(); } } }

Ajax AutoCompleteExtender with MSSQL datasource

Jeff Miles

Leave a Reply Cancel reply