C# ASP.Net autocomplete with Javascript/Json post not working

Question

I am currently working with a c# asp.net usercontrol in which I need a functional autocomplete.

The script looks like it wants to run, as you can see; the progress bar spins, but it ALWAYS comes back 'Error'. I'm not sure what to do from here. I followed at least 5 different tutorials to get this working; the code mimics the code found here; but it doesn't seem to work when all is said and done. What am I missing? Any suggestions to get me where I need to be would be much appreciated.

If any more information is needed let me know, but the entire code can be found below.

HTML/Javascript

<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="Search Textbox.ascx.cs" Inherits="StagingApplication.Search.Search_Textbox" %>

<link href="../css/styleCustomPages.css" rel="stylesheet" />
<link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.1/themes/base/jquery-ui.css" rel="stylesheet" type="text/css"/>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.1/jquery-ui.min.js"></script> 
<script type="text/javascript">
    $(document).ready(function () {
        SearchText();
    });
    function SearchText() {
            $(".autosuggest").autocomplete({
                source: function (request, response) {
                    $.ajax({
                        type: "POST",
                        contentType: "application/json; charset=utf-8",
                        url: "Search_Textbox.aspx/GetAutoCompleteData",
                        data: "{'searchText':'" + document.getElementById('txtSearch').value + "'}",
                        dataType: "json",
                        success: function (data) {
                            response(data.d);
                        },
                        error: function (result) {
                            alert('Error' );
                        }
                    });
                }
            });
    }
</script>

<div class="ui-widget">
<label for="tbAuto">Enter UserName: </label>
<input type="text" id="txtSearch" class="autosuggest" />
</div>

C# Code Behind

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.Web.Services;
using System.Web.Script.Services;

namespace StagingApplication.Search
{
    public partial class Search_Textbox : System.Web.UI.UserControl
    {
        #region Declarations
        static string dbSearch = "db string";
        #endregion

        protected void Page_Load(object sender, EventArgs e)
        { }

        #region Methods and Functions
        [WebMethod, ScriptMethod]
        public static List<string> GetAutoCompleteData(string searchText)
        {
            List<string> result = new List<string>();
            using (SqlConnection con = new SqlConnection(dbSearch))
            {
                using (SqlCommand cmd = new SqlCommand("SELECT TOP 1000 [SearchTerm] FROM [Search].[dbo].[Cache] where AutoCompleteTerm = 0 and SearchTerm LIKE @SearchText + '%';", con))
                {
                    con.Open();
                    cmd.Parameters.AddWithValue("@SearchText", searchText);
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        result.Add(dr["SearchTerm"].ToString());
                    }
                    return result;
                }
            }
        }
    }
}

Answer 1

cmd.Parameters.AddWithValue("@SearchText", searchText);

Variable searchText doesn't exist, because:

Your function begins with this:

public static List<string> GetAutoCompleteData(string username)

It should be:

public static List<string> GetAutoCompleteData(string searchText)

You forgot to update username to searchText.

Answer 2

In the aspx page, you need to declare a WebMethod which will call the code in the user control. This is how I do it in one of our sites:

[WebMethod]
public static WebMethodReturn<IEnumerable> GetWatchboxes()
{
    return AddOns_WatchboxAdmin.GetWatchboxes();
}

...making sure in jQuery to call the page method url of the aspx page not the control.

My answer here may help a little bit too, particularly the recommendation to debug web traffic with Fiddler.

Its important to note that your "Error" string is in the jQuery error block. That means it's probably a transport error or some sort of error in jQuery. My guess is 404 because you don't have a page method in the aspx page only in the user control. This error block won't catch server side exceptions. I return a little wrapper class with a Data property and an Error property; I put any exception string into the Error property otherwise I leave it null. I check this string clientside. This is optional, just a little tip that jQuery knows nothing about .NET exceptions - not that I believe an exception is the problem here.

The ScriptMethod attribute is needed in ASMX web services in order to return JSON but for reasons unknown to me you don't need it in page methods.

You certainly do not need to be doing any manual serialisation to JSON as instructed in another answer. If called correctly by jQuery, the example I have given will return JSON with no further work needed.

So, to summarise, put this into your aspx.cs:

[WebMethod]
public static List<string> GetAutoCompleteData(string searchText)
{

and call and return the results of the same function in the instance of the user control on your aspx page (or make the function public static in the user control). Inspect the client call and server response in Fiddler in case of any problems.

Answer 3

Since you're expecting JSON to be returned, you may need to try something like this:

Add the following declaration:

using System.Web.Script.Serialization;

Then perform the following on your List<string> result variable:

JavaScriptSerializer serializer = new JavaScriptSerializer();
serializer.MaxJsonLength = Int32.MaxValue; // optional
return serializer.Serialize(result);

UPDATE - here's a more complete solution for outputting your data to JSON:

public static string GetDataTableToJSONString(DataTable table) {
    List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
    foreach (DataRow row in table.Rows) {
        Dictionary<string, object> dict = new Dictionary<string, object>();
        foreach (DataColumn col in table.Columns) {
            dict[col.ColumnName] = row[col];
        }
        list.Add(dict);
    }
    JavaScriptSerializer serializer = new JavaScriptSerializer();
    serializer.MaxJsonLength = Int32.MaxValue;
    return serializer.Serialize(list);
}

public static List<string> GetAutoCompleteData(string searchText) {
    string sql = "SELECT TOP 1000 [SearchTerm] FROM [Search].[dbo].[Cache] " +
        "where AutoCompleteTerm = 0 and SearchTerm LIKE @SearchText + '%';";
    using (SqlConnection con = new SqlConnection(dbSearch)) {
        con.Open();
        using (SqlCommand cmd = new SqlCommand(sql, con)) {
            cmd.Parameters.AddWithValue("@SearchText", searchText);
            using (SqlDataAdapter da = new SqlDataAdapter(cmd)) {
                using (DataSet ds = new DataSet()) {
                    da.Fill(ds);
                    return GetDataTableToJSONString(ds.Tables[0]);
                }
            }
        }
    }
}