How to create Auto complete textbox like Google place text box with SQL database

Question

i have to create auto complete text box in my web form for the below condition

1. if i type country name then the auto complete suggestion should show the list of countries,state and city name which are stored in my database.

2. if i type city name then the auto suggestion should show the country and statename from my database,similarly statename also

For Example : if the user type

tamilnadu

then the result will be

tamilnadu, India

if the user type chennai in text box then the auto complete suggestion should be

chennai,tamilnadu, india

if the user type Erode tamilnadu

then the result should be

Erode,tamilnadu,india

3. i have use different tables for each values like city, state, and country

all i have done till now is ive a textbox which shows only city search

My question is how can i achieve the result as i mentioned above like mulitple entries input to the textbox and multiple suggestion as a output like google place search

pls guide me i've search all over the web but i cant find the perfect solution

Here is my code for script

 <script  type ="text/javascript" >
    $(function () {
        $('#<%=txtSearch.ClientID%>').autocomplete({
            source: function (request, response) {
                $.ajax({
                    url: "Default.aspx\getCityName",
                    data: "{'pre':'" + request.term + "'} ",
                    datatype: "json",
                    type: "POST",
                    contentType: "application/json; charset=utf-8",
                    success: function (data) {
                        response($.map(data.d, function (item) {

                            return {
                                cityname: item.cityname,
                                country: item.country,

                                json: item

                            }
                        }));

                    },
              error: function (XMLHttpRequest, textStatus,                               errorThrown) {
                        alert(textStatus);
                    }

                });
            },

            focus: function (event, ui) {
                $('#<%=txtSearch.ClientID%>').val(ui.item.cityname);
                return false;
            },
            select: function (event, ui) {
                $('#<%=txtSearch.ClientID%>').val(ui.item.citycame);
                return false;
            }

        }).data("ui.autocomplete")._renderItem = function (ul, item) {
            return $("<li>")
            .append("<a>City:" + item.cityname + "<br>Country :" + item.country + "</a>")
            .appendTo(ul);
        };

    });

</script>

Code of my webservice :

 [WebMethod]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    public static List<Searchcity> getCityName(String pre) 
    {
        List<Searchcity> allcity = new List<Searchcity>();
        using(MydatabaseEntities dc = new MydatabaseEntities())
        {
            allcity = (from a in dc.Searchcities
                       where a.cityname.StartsWith(pre)
                       select a).ToList();

        }
        return allcity;
    }

Answer 1

You have to write SQL query with selection of 3 coloumns. 1> Country 2> State 3> City. SQL query should have 3 conditions.

Like below

if(select count(1) from Table where cityname like "@cityname")
begin

select city,state,country from table

end

else if(select count(1) from Table where state like "@statename")
begin

select 1 as city, state,country from table

end

else if(select count(1) from Table where country like "@countryname")

begin

select 1 as city, 1 as state, country from table

end

Please get this value in json using ajax.

Then append every each column with ',' seperator for example(Chennai,Tamilnadu,India).

If any coloumn value is 1 then omit it. for example (1,Tamilnadu,India then omit 1. so Tamilnadu,India). You need to think more with this example. If you have different tables then use joins.

Answer 2

This may help you!

AJAX

 var Autoarr = [];

        $.ajax({
                 url: "',
                dataType: "json",
                async: false,
                success: function (data) {
                    var result = $.map(data, function (val, key) {
                        return { type: key, cityname: val.cityname, country: val.country};
                    });

                    for (var i = 0; i < result.length; i++) {
                        Autoarr[i] = result[i].cityname;
                    }

                }
            });

AUTOCOMPLETE

 $(function () {

                $("#category").autocomplete({
                    source: Autoarr
                });
            });