Pulling sql data into jquery autocomplete

Question

I've looked for an answer to this, but not finding exactly what I'm looking for. So, please excuse if this has been answered any another thread.

I have a very large sql table that I'd like to use in a jquery autocomplete input field. I know I need to return that data as json formatted, just not sure the best way to accomplish this. This field is in an ASP.net MVC application.

I know I could probably do a php page that returns the result, but that seems a bit messy to me. Is the best way to go is by creating a web service that I call?

Thanks in advance for any help.

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

Here's some code as to how I have accomplished this. I am using the jquery UI plugin

The javascript on my View

$(function () {

        $("#suburb").autocomplete({
            source: function (request, response) {
                $.ajax({
                    url: '@Url.Action("ManufacturerAutoComplete", "AutoComplete")', type: "POST", dataType: "json",
                    data: { searchText: request.term, maxResults: 10 },
                    success: function (data) {
                        response($.map(data, function (item) {
                            return { label: item.DisplayValue, value: item.Suburb, id: item.SuburbID, postcode: item.Postcode, state: item.State }
                        }))
                    }
                })
            },
            select: function (event, ui) {

                $("#state").val(ui.item.state);
                $("#postcode").val(ui.item.postcode);

            }
        });

The input on my view

<input type="text" id="suburb" />

And finally the code from my Controller

[HttpPost]
        public JsonResult ManufacturerAutoComplete(string searchText)
        {
            
            if (searchText.Length > 2)
            {
                var service = new SuburbSearchServiceClient();
                var results = service.SearchSuburb(searchText, "Australia");

                List<Suburbs> sList = new List<Suburbs>();

                foreach (var item in results)
                {
                    sList.Add(new Suburbs() { SuburbID = item.SuburbID, Suburb = item.Suburb, State = item.State, Postcode = item.Postcode, DisplayValue = item.Suburb + " - " + item.State + " - " + item.Postcode });
                }

                return Json(sList);
            }
            else
            {
                var data = string.Empty;
                return Json(data);
            }
        }

You need to include the js and css from the jquery-ui plugin and the results of your auticomplete will be shown underneath the input element. As you can see from the JsonResult method, I am calling a web service to get my suburb data, but this could come from anywhere in reality. Hope this helps.

I think I get this, but I'm still struggling with the sql commands needed. I'm still working on this so I'll update with my progress. Thanks so much for the help. — Downtap, Jul 07 '11 at 14:28
I'm having an issue invoking the Json() method. What is the reference for that? — Downtap, Jul 07 '11 at 16:50

Pulling sql data into jquery autocomplete

1 Answers1