CODEWITHSUNDEEP
jqueryasp.netajaxvb.netautocomplete
user3966883
user3966883

Reputation:

Autocomplete Textbox in vb.net using MySql

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

Upvotes: 1

Views: 1656

Answers (2)

Mairaj Ahmad
Mairaj Ahmad

Reputation: 14604

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.

Upvotes: 1

Stef Geysels
Stef Geysels

Reputation: 1047

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

Upvotes: 0

Related Questions