Autocomplete Textbox in vb.net using MySql

Question

I am trying to make autocomplete function which starts suggesting from datatable when users types. It seems all right but it pops up Not Found. In my database all columns name and connections seems to be alright. I don't understand where is the problem as it's even not throws any errors. This is the link of files I uploaded on server

ASPX

<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> 
    <link href="jquery-ui.css" rel="stylesheet" type="text/css" />  
    <script src="http://ajax.aspnetcdn.com/ajax/jquery/jquery-1.8.0.js"></script>
    <script src="http://ajax.aspnetcdn.com/ajax/jquery.ui/1.8.22/jquery-ui.js"></script>


    <script type="text/javascript">  
        $(function () {
            $("#area").autocomplete({
                source: function (request, response) {
                    var param = { cityname: $('#area').val() };
                    $.ajax({
                        url: "Dafault.aspx/GetAreas",
                        data: JSON.stringify(param),
                        dataType: "json",
                        type: "POST",
                        contentType: "application/json; charset=utf-8",
                        dataFilter: function (data) { return data; },
                        success: function (data) {
                            response($.map(data.d, function (item) {
                                return {
                                    value: item
                                }
                            }))
                        },
                        error: function (XMLHttpRequest, textStatus, errorThrown) {
                            alert(errorThrown);
                        }
                    });
                },
                minLength: 2//minLength as 2, it means when ever user enter 2 character in TextBox the AutoComplete method will fire and get its source data. 
            });
        });

    </script>  

</head>
<body>
    <form id="form1" runat="server">
        <asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>

    <div class="form-items">
        Select Area<br />
        <asp:TextBox ID="area" CssClass="area" runat="server"></asp:TextBox>

    </div>

VB

Imports System.Data
Imports MySql.Data.MySqlClient
Imports System.Configuration
Imports System.Web.Services
Partial Class _Default
    Inherits System.Web.UI.Page
    <WebMethod>
    Public Shared Function GetAreas(areaname As String) As List(Of String)
        Dim area As New List(Of String)()
        Dim con As New MySqlConnection("Data Source=182.50.133.88;port=3306;Initial Catalog=db;User Id=user;password=pwd;")



        Dim query As String = String.Format("SELECT areaName FROM areas", areaname)
        'Note: you can configure Connection string in web.config also.
        Dim cmd As New MySqlCommand(query, con)
        con.Open()
        Dim reader As MySqlDataReader = cmd.ExecuteReader()
        While reader.Read()
            area.Add(reader.GetString(0))
        End While
        Return area
    End Function
End Class

JavaScript is calling url: "Dafault.aspx/GetCities", but the function is called GetAreas ?? — Mike Bateman
– Mike Bateman, Commented Feb 25, 2016 at 14:06
@MrGadget Thank you to inform such a silly mistake. I changed it but now it shows Internal Server Error in pop up. — user3966883
– user3966883, Commented Feb 25, 2016 at 16:56
@StupidRomeo I would be very careful and check what is in the query for your case. If you debug it, what does it generate? What SQL string? — Ian
– Ian, Commented Feb 28, 2016 at 15:42

Mairaj Ahmad · Accepted Answer · 2016-03-08 05:42:59Z

1

+50

Your WebMethod expects parameter with same name which you have passed from ajax.

Change your param in javascript like this.

var param = { areaname : $('#area').val() };

Also you are using asp:Textbox which might change its id then it will not be found by jquery.

So you can use ClientIDMode=static so id of textbox will not be changed.

<asp:TextBox ID="area" CssClass="area" runat="server" ClientIDMode="Static"></asp:TextBox>

Update

Your query doesn't have a where clause so you are getting all records from database. Add a where clause and pass the areaname as parameter to query.

Dim query As String = String.Format("SELECT areaName FROM areas Where UPPER(areaName) like @areaName", areaname);
Dim cmd As New MySqlCommand(query, con)
cmd.Parameters.Add("@areaName",areaname.ToUpper()+"%");

Update 2

You can also check if the area name starts with the letter you have passed then add to list otherwise don't add in list.

if(reader.GetString(0).StartsWith(areaname))
area.Add(reader.GetString(0))

I don't know exact syntax of VB but i hope you will get the idea.

edited Mar 8, 2016 at 5:42

answered Mar 1, 2016 at 5:50

Mairaj Ahmad

14.6k2 gold badges29 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

user3966883 Over a year ago

Thanks man. In this I'm getting the results but it pickup all letters which contains typed letter. e.g. If area name is kothrud & If I type k then only areas which starts from K should suggest. How do I do that

Mairaj Ahmad Over a year ago

Your query doesn't have a where clause add a where clause.See the update in answer.

user3966883 Over a year ago

It shows Local variable cmd can not be referred to before it is declared.

Mairaj Ahmad Over a year ago

Yes add after this line Dim cmd As New MySqlCommand(query, con)

user3966883 Over a year ago

Nothing comes in output. See the link I made those changes healthsaviour.com/default.aspx

|

Stef Geysels · Accepted Answer · 2016-03-01 18:36:51Z

0

If you have only 1 letter that need to match with the suggestion, you can try this:

    While reader.Read()
        Dim str As String=reader.GetString(0)
        If str(0)= areaname then area.Add(str)
    End While

answered Mar 1, 2016 at 18:36

Stef Geysels

1,02711 silver badges28 bronze badges

Collectives™ on Stack Overflow

Autocomplete Textbox in vb.net using MySql

2 Answers 2

12 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related