jQuery Autocomplete from database when users starts to type

Question

I'm trying to get a autocomplete function to work. It works when I get my database values on Page_Load event but it takes forever loading the page because it's a large set of data that needs to be available to my autocomplete textfield. Better would be to do the db query when users start typing. I've tried different approaches but none works.

This is my code so far;

        <script type="text/javascript">
        $(document).ready(function () {

            SearchText();

            function SearchText() {
                $("#<%= artnr1.ClientID %>").autocomplete({
                    source: function (request, response) {
                        $.ajax({
                            url: "GetArticles.asmx/searcharticles",
                            type: "POST",
                            dataType: "json",
                            contentType: "application/json; charset=utf-8",
                            data: "{ 'term' : '" + $("#<%= artnr1.ClientID %>").val() + "'}",
                            dataFilter: function (data) { return data; },
                            success: function (data) {
                                response($.map(data.d, function (item) {
                                    return {
                                        label: item.label,
                                        value: item.value,
                                        desc: item.desc
                                    }
                                }))
                                //debugger;
                            },
                            error: function (result) {
                                alert("Error");
                            }
                        });
                    },
                    minLength: 1,
                    delay: 1000,
                    autoFocus: true,
                    change: function (event, ui) {
                        $("#<%= artben1.ClientID %>").val(ui.item.desc);//or ui.item.desc perhaps
                    }
                }).focus(function () {
                    $(this).data("uiAutocomplete").search($(this).val());
                });
            }
        });
    </script>


<asp:TextBox runat="server" id="artnr1" placeholder="Artnr" value=""/>

This is my asmx file:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data.Odbc;
using System.Web.Configuration;
using System.Data;
using Pervasive.Data.SqlClient;
using Pervasive.Data.Common;
using Newtonsoft.Json;

namespace cDealer
{
    /// <summary>
    /// Summary description for GetPyART
    /// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
// 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 GetPyART : System.Web.Services.WebService
{

    [WebMethod]
    public List<string> searcharticles(string term)
    {
        string strLagerSaldo = string.Empty;
        string strLagerReserv = string.Empty;
        string strLagerBest = string.Empty;
        string strLagerPris = string.Empty;
        string strArtKod = string.Empty;
        string strEANKod = string.Empty;
        string strBen = string.Empty;
        string strartnrvalue = string.Empty;
        string pyarticles = string.Empty;


        OdbcConnection dbPyConn = new OdbcConnection(WebConfigurationManager.ConnectionStrings["ConnPyString"].ConnectionString);

        string pysql = @"select D1001_Artikelkod, D1021_Benämning, D1132_Saldo, D1134_Reserverat, D1136_Beställt, D1154_Grundpris, " + (char)34 + "D1109_EAN-kod" + (char)34 + " from PULAGER WHERE D1055_Kalkyltyp <> '60' AND (D1001_Artikelkod Like '%" + term + "%' OR D1021_Benämning Like '%" + term + "%') ORDER By D1001_Artikelkod DESC";
        using (OdbcConnection connection = new OdbcConnection(WebConfigurationManager.ConnectionStrings["ConnPyString"].ConnectionString))
        {
            OdbcCommand command = new OdbcCommand(pysql, connection);

            List<string> articles = new List<string>();

            connection.Open();

            OdbcDataReader reader = command.ExecuteReader();

            // Call Read before accessing data.
            while (reader.Read())
            {
                if (reader.HasRows)
                {

                    if (reader["D1132_Saldo"] != DBNull.Value)
                    {
                        strLagerSaldo = reader.GetDecimal(reader.GetOrdinal("D1132_Saldo")).ToString("G29").TrimEnd();
                    }
                    else
                    {
                        strLagerSaldo = "0";
                    }

                    if (reader["D1134_Reserverat"] != DBNull.Value)
                    {
                        strLagerReserv = reader.GetDecimal(reader.GetOrdinal("D1134_Reserverat")).ToString("G29").TrimEnd();
                    }
                    else
                    {
                        strLagerReserv = "0";
                    }

                    if (reader["D1136_Beställt"] != DBNull.Value)
                    {
                        strLagerBest = reader.GetDecimal(reader.GetOrdinal("D1136_Beställt")).ToString("G29").TrimEnd();
                    }
                    else
                    {
                        strLagerBest = "0";
                    }


                    if (reader["D1154_Grundpris"] != DBNull.Value)
                    {
                        strLagerPris = reader.GetDecimal(reader.GetOrdinal("D1154_Grundpris")).ToString("G29").TrimEnd();
                        strLagerPris = String.Format("{0:C}", Convert.ToDouble(strLagerPris));
                    }
                    else
                    {
                        strLagerPris = "0";
                    }


                    if (reader["D1001_Artikelkod"] != DBNull.Value)
                    {
                        strArtKod = reader.GetString(reader.GetOrdinal("D1001_Artikelkod")).TrimEnd();
                    }
                    else
                    {
                        strArtKod = "";
                    }


                    if (reader["D1021_Benämning"] != DBNull.Value)
                    {
                        strBen = reader.GetString(reader.GetOrdinal("D1021_Benämning")).TrimEnd().Replace("" + (char)34 + "", "´´");
                    }
                    else
                    {
                        strBen = "";
                    }

                    if (reader["D1109_EAN-kod"] != DBNull.Value)
                    {
                        strEANKod = reader.GetString(reader.GetOrdinal("D1109_EAN-kod")).TrimEnd();
                    }
                    else
                    {
                        strEANKod = "";
                    }

                    articles.Add("{label:" + strArtKod + " - " + strEANKod + " - " + strBen + " - L.Saldo: " + strLagerSaldo + " - Reserv.: " + strLagerReserv + " - Beställt: " + strLagerBest + " - KPris: " + strLagerPris + ", value: " + strArtKod + ", desc:" + strBen + "}");

                    //Response.Write(strartsearch);
                }
                else
                {
                    //Response.Redirect("https://www.google.se");

                }
            }

            // Call Close when done reading.
            reader.Close();
            return articles;

        }
    }
}

}

When I start typing and then pauses the event is fired. I can in firebug see that I get a json response like this;

"[{"label":"771678 - Fuel injector/10/kw","value":"771678","desc":"Fuel injector for Ford"}]"

But no autocomplete shows up. I can in the debugger see this errors TypeError: invalid 'in' operand a TypeError: ui.item is null

I have no idéa where that is and whats wrong. Can anyone please assist or point me to a really easy guide with all bits explained for C# webforms. Thanks.

Answer 1

The problem is probally that the JSON is incorrect. After what you have posted in the comments I would say, the quote before the Array is the issue.

My response looks like this: {"d":"[{\"label\"

Here you can find some examples and some more information to JSON https://en.wikipedia.org/wiki/JSON

Just to sum up: if pure json is being send escaping is not necesary, in a string you have to escape (or combine with single quotes)

{
  "name": "test",
  "array": [ 1, 2, 3, 4], /* here are no quotes around the square brakets */
  "object":  {"prop1":1, "prop2":2} 
}

A good way to test is create a textfile, change the file extension to ".json", and add the JSON. Like this you can experiment with the quotes and so. to be sure that everything come back correct from the server, you can also change the return type to text and print the message into the console.

If the problem still persits post the raw json, which was sent from the Server.

Update: here https://embed.plnkr.co/OSYJQZ/ is a working example on firefox 45+ with your corrected json.
you can see how the json should look like (and copy out in needed).

Update2

The Problem is probably because the nested object in the Json is created as a string (List<string>). I would suggest to write something like this (or use the ResponseFormat Attribute, depence of the usecase )

//helper Object for the data Structure
class Helper{
    public string label;
    public string value;
    public string desc;
}

then in the code

    //create the list of Objects
    List<Helper> list = new List<Helper>();

    list.Add (new Helper(){
    label="771678 - - Fuel injector for Ford - L.Saldo: 0 - Reserv.: 0 - Order: 0 - KPris : 0,00 kr",
    value="771678",
    desc="Fuel injector for Ford" }       
    );

   //serialize the Object into Vaild JSON
   var jsonString = (new System.Web.Script.Serialization.JavaScriptSerializer()).Serialize(list);

Here are some other Questions on SO the deal with similar problems https://stackoverflow.com/a/3196640/1679286
https://stackoverflow.com/a/19564022/1679286

Update 3: Here is a solution I wipped up, its only a ashx Handler, but it is the fasted I could do without opening VS. I Hope this helps

<%@ WebHandler Language="C#" Class="JsonHandler" %>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;


public class JsonHandler : IHttpHandler
{

   class Helper{
        public string label;
        public string value;
        public string desc;
   }

    public void ProcessRequest(HttpContext context)
    {
        List<Helper> list = new List<Helper>();

        list.Add (new Helper(){
        label="771678 - - Fuel injector for Ford - L.Saldo: 0 - Reserv.: 0 - Order: 0 - KPris : 0,00 kr",
        value="771678",
desc="Fuel injector for Ford" }       
        );
        context.Response.ContentType = "application/json";

        var jsonString = (new System.Web.Script.Serialization.JavaScriptSerializer()).Serialize(list);

        context.Response.Write(jsonString);
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
}