Implement jQuery AutoComplete TextBox from database in c#

Hamameli...

SOLVED CLOSED ACTIVE ANSWERED
User: Hamamelis
Posted: on Mar 17, 2016 08:36 AM
Forum: jQuery and jQuery Plugins
Answer: 1
Views: 6462

Hi there, hope in your help.

I work in ASP.NET c# and MySQL database.

I need to make the AutoComplete TextBox from database using AJAX and I have tried this tutorial on the web.

http://www.aspsnippets.com/Articles/Implement-jQuery-AutoComplete-TextBox-from-database-using-AJAX-PageMethods-in-ASPNet.aspx

But I have a problem when the name consists of multiple words, e.g. :

name: Plane working on the search

name: Planner of not working because after PostBack Page the selected value on list in the TextBox is only Planner and the search return is empty.

If delete on TextBox txtCp the property

AutoPostBack="true"

and press Enter to search the script working.

Can you please help me figure out the problem?

Thanks in advance.

My code:

<asp:TextBox ID="txtCp" runat="server" OnTextChanged="txtCp_TextChanged" AutoPostBack="true"></asp:TextBox>
<asp:HiddenField ID="hfCp" runat="server" />

[WebMethod]
public static string[] GetCustomers(string prefix)
{
    List<string> customers = new List<string>();

    using (OdbcConnection conn =
        new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnMySQL"].ConnectionString))
    {
        using (OdbcCommand cmd = new OdbcCommand())
        {
            cmd.CommandText = "SELECT Name FROM doName WHERE Name LIKE CONCAT('%',?,'%'); ";
            cmd.Parameters.AddWithValue("param1", prefix);
            cmd.Connection = conn;
            conn.Open();

            using (OdbcDataReader sdr = cmd.ExecuteReader())
            {
                while (sdr.Read())
                {
                    customers.Add(string.Format("{0}", sdr["Name"].ToString().ToUpper()));
                }
            }
            conn.Close();
        }
    }
    return customers.ToArray();
}


protected void txtCp_TextChanged(object sender, EventArgs e)
{
    BindData();
}


    $(function () {
        $("[id$=txtCp]").autocomplete({
            source: function (request, response) {
                $.ajax({
                    url: '<%=ResolveUrl("Default.aspx/GetCustomers") %>',
                    data: "{ 'prefix': '" + request.term + "'}",
                    dataType: "json",
                    type: "POST",
                    contentType: "application/json; charset=utf-8",
                    success: function (data) {
                        response($.map(data.d, function (item) {
                            return {
                                label: item.split('-')[0],
                                val: item.split('-')[1]
                            }
                        }))
                    },
                    error: function (response) {
                        alert(response.responseText);
                    },
                    failure: function (response) {
                        alert(response.responseText);
                    }
                });
            },
            select: function (e, i) {
                $("[id$=hfCp]").val(i.item.val);
            },
            minLength: 1
        });
    });

Shashika...

0 3

Reply
ANSWER
User: Shashikant
Replied: on Mar 17, 2016 09:17 AM
Modified: on Mar 17, 2016 10:02 AM
Report

Hi,

I have created Sample,It is working Fine

HTML

<div>
    <script src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.10.0.min.js" type="text/javascript"></script>
    <script src="http://ajax.aspnetcdn.com/ajax/jquery.ui/1.9.2/jquery-ui.min.js" type="text/javascript"></script>
    <link href="http://ajax.aspnetcdn.com/ajax/jquery.ui/1.9.2/themes/blitzer/jquery-ui.css"
        rel="Stylesheet" type="text/css" />
    <script type="text/javascript">
        $(function () {
            $("[id$=txtSearch]").autocomplete({
                source: function (request, response) {
                    $.ajax({
                        url: '<%=ResolveUrl("~/CS.aspx/GetCustomers") %>',
                        data: "{ 'prefix': '" + request.term + "'}",
                        dataType: "json",
                        type: "POST",
                        contentType: "application/json; charset=utf-8",
                        success: function (data) {
                            response($.map(data.d, function (item) {
                                return {
                                    label: item.split('-')[0],
                                    val: item.split('-')[1]
                                }
                            }))
                        },
                        error: function (response) {
                            alert(response.responseText);
                        },
                        failure: function (response) {
                            alert(response.responseText);
                        }
                    });
                },
                select: function (e, i) {
                    $("[id$=hfCustomerId]").val(i.item.val);
                },
                minLength: 1
            });
        });   
    </script>
    Enter search term:
    <asp:TextBox ID="txtSearch" runat="server" AutoPostBack="true" OnTextChanged="txtCp_TextChanged" />
    <asp:HiddenField ID="hfCustomerId" runat="server" />
</div>

[WebMethod]
public static string[] GetCustomers(string prefix)
{
    List<string> customers = new List<string>();
    using (SqlConnection conn = new SqlConnection())
    {
        conn.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandText = "select ContactName, CustomerId from Customers where ContactName like @SearchText + '%'";
            cmd.Parameters.AddWithValue("@SearchText", prefix);
            cmd.Connection = conn;
            conn.Open();
            using (SqlDataReader sdr = cmd.ExecuteReader())
            {
                while (sdr.Read())
                {
                    customers.Add(string.Format("{0}-{1}", sdr["ContactName"], sdr["CustomerId"]));
                }
            }
            conn.Close();
        }
    }
    return customers.ToArray();
}

protected void txtCp_TextChanged(object sender, EventArgs e)
{
    string CustomerId = hfCp.Value;
    string Text = txtCp.Text;
    ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('Name: " + CustomerId + "\\nText: " + Text + "');", true);
}

Screenshot

Implement jQuery AutoComplete TextBox from database in c#

Company

Explore

Follow us

Contact Us