AJAX AutoComplete from SQL DB using ASP.NET C# on 4.5 Framework

See more:

C#

ASP.NET

SQL

.NET

Ajax

.NET4.5

I've been looking for several days for a solution that fit, but the majority of content out there seems to be really old or does not provide a complete tutorial. I've never worked with AJAX before, but I need to try and use it to implement a autocomplete feature on a textbox in a form in a way so that a dynamic query can be made against our sql database without having to reload the page, load all data, etc.

The way asp.net works sometimes I wonder if it's just a simple syntax thing or a missing reference, but I don't know what to look for.

Here's what I'm working with:

hub.aspx
AutoComplete.asmx

Both, of course have their cs pages.

The web.config reads these important bits (other fluff omitted)

<sectionGroup name="system.web">
  <section name="sanitizer" requirePermission="false"
           type="AjaxControlToolkit.Sanitizer.ProviderSanitizerSection, AjaxControlToolkit" />
</sectionGroup>


<sanitizer defaultProvider="HtmlAgilityPackSanitizerProvider">
  <providers>
    <add name="HtmlAgilityPackSanitizerProvider" type="AjaxControlToolkit.Sanitizer.HtmlAgilityPackSanitizerProvider"></add>
  </providers>
</sanitizer>

  <controls>
    <add assembly="Microsoft.AspNet.Web.Optimization.WebForms" namespace="Microsoft.AspNet.Web.Optimization.WebForms" tagPrefix="webopt" />
    <add tagPrefix="ajaxToolkit" assembly="AjaxControlToolkit" namespace="AjaxControlToolkit" />
  </controls>

This is the chunk out of the aspx page with the control:

<div class="large-4 columns">
    <label>Company:</label>
    <asp:TextBox ID="CompanyName" runat="server"></asp:TextBox>
    <asp:AutoCompleteExtender
        ID="AutoCompleteExtender2"
        TargetControlID="CompanyName"
        runat="server"
        UseContextKey="True"
        ServiceMethod="GetCompletionList"
        ServicePath="~/AutoComplete.asmx" />
</div>

This is the entirety of the asmx service:

using System;
using System.Collections;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Linq;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using AjaxControlToolkit;

namespace Omitted_For_Reason
{
    /// <summary>
    /// Summary description for AutoComplete
    /// </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 AutoComplete : System.Web.Services.WebService
    {

        public AutoComplete()
        {

            //Uncomment the following line if using designed components
            //InitializeComponent();
        }

        [WebMethod]
        [System.Web.Services.WebMethod]
        [System.Web.Script.Services.ScriptMethod]
        public static string[] GetCompletionList(string prefixText, int count, string contextKey)
        {
            //ADO.Net
            SqlConnection cn = new SqlConnection();
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            String strCn = "data source=data012;Initial Catalog=Archive;Integrated Security=True";
            cn.ConnectionString = strCn;
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            //Compare String From Textbox(prefixText) AND String From 
            //Column in DataBase(CompanyName)
            //If String from DataBase is equal to String from TextBox(prefixText) 
            //then add it to return ItemList
            //-----I defined a parameter instead of passing value directly to 
            //prevent SQL injection--------//
            cmd.CommandText = "SELECT * FROM [Archive].[dbo].[SH_Customers] WHERE CardName LIKE @myParameter";
            cmd.Parameters.AddWithValue("@myParameter", "%" + prefixText + "%");

            try
            {
                cn.Open();
                cmd.ExecuteNonQuery();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);
            }
            catch
            {
            }
            finally
            {
                cn.Close();
            }
            dt = ds.Tables[0];

            //Then return List of string(txtItems) as result
            List<string> txtItems = new List<string>();
            String dbValues;

            foreach (DataRow row in dt.Rows)
            {
                //String From DataBase(dbValues)
                dbValues = row["CardName"].ToString();
                dbValues = dbValues.ToLower();
                txtItems.Add(dbValues);
            }

            return txtItems.ToArray();
        }
    }
}

What happens when I use this code is that nothing happens as I start to type anything in the textbox which should match known data. There are no errors or exceptions thrown, it simply does not work. Again, because I have yet to come across a thorough tutorial, this is a hodgepodge of various solutions that worked for other people, but since no one source represented a complete solution, I feel like there was something I missed.

The

using AjaxControlKit;

tag was also placed into the aspx.cs for good measure.

I am using Visual Studio 2012. I cannot use the toolkit dialog to create a page method for the extender, because it tells me there is no codebehind for the getcompletionlist although I don't know what codebehind it's looking for and the one solution I found online was so vague I had no idea where to code.

Please help, and once this is solved and understood I'll write an article that is, for the first time in years, up to date, and for the first time ever, COMPLETE.


UPDATE #1:

Here is the toolscriptmanager stuff - it's in the Site.Master

<ajaxToolkit:ToolkitScriptManager  runat="server" ID="ScriptManager1" EnablePageMethods="true">
        <Scripts>
            <%--To learn more about bundling scripts in ScriptManager see http://go.microsoft.com/fwlink/?LinkID=272931&clcid=0x409 --%>
            <%--Framework Scripts--%>
            
            <asp:ScriptReference Name="MsAjaxBundle" />
            <asp:ScriptReference Name="jquery" />
            <asp:ScriptReference Name="jquery.ui.combined" />
            <asp:ScriptReference Name="WebForms.js" Assembly="System.Web" Path="~/Scripts/WebForms/WebForms.js" />
            <asp:ScriptReference Name="WebUIValidation.js" Assembly="System.Web" Path="~/Scripts/WebForms/WebUIValidation.js" />
            <asp:ScriptReference Name="MenuStandards.js" Assembly="System.Web" Path="~/Scripts/WebForms/MenuStandards.js" />
            <asp:ScriptReference Name="GridView.js" Assembly="System.Web" Path="~/Scripts/WebForms/GridView.js" />
            <asp:ScriptReference Name="DetailsView.js" Assembly="System.Web" Path="~/Scripts/WebForms/DetailsView.js" />
            <asp:ScriptReference Name="TreeView.js" Assembly="System.Web" Path="~/Scripts/WebForms/TreeView.js" />
            <asp:ScriptReference Name="WebParts.js" Assembly="System.Web" Path="~/Scripts/WebForms/WebParts.js" />
            <asp:ScriptReference Name="Focus.js" Assembly="System.Web" Path="~/Scripts/WebForms/Focus.js" />
            <asp:ScriptReference Name="WebFormsBundle" />
            <%--Site Scripts--%>
            <asp:ScriptReference Path="~/js/foundation.min.js" />
            <asp:ScriptReference Path="~/js/vendor/custom.modernizr.js" />
            <asp:ScriptReference Path="~/js/gauge.coffee" />
            
        </Scripts>
    </ajaxToolkit:ToolkitScriptManager>