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

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();
            }
       }
}