jQuery AutoComplete TextBox using Microsoft Access Database in ASP.Net

Last Reply 5 days ago By dharmendr

Posted 5 days ago

I wanted to know the coding of how to Autocomplete the textbox using Microsoft access as database, So anybody can help me finding solution for this plz 

You are viewing reply posted by: dharmendr 5 days ago.
Posted 5 days ago

Hi ShihabKhan,

Clear jQuery AutoComplete TextBox when no match found

Refer below example.

HTML

<script type="text/javascript" src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.10.0.min.js"></script>
<script type="text/javascript" src="http://ajax.aspnetcdn.com/ajax/jquery.ui/1.9.2/jquery-ui.min.js"></script>
<link rel="Stylesheet" type="text/css" href="http://ajax.aspnetcdn.com/ajax/jquery.ui/1.9.2/themes/blitzer/jquery-ui.css" />
<script type="text/javascript">
    $(function () {
        $("#txtSearch").autocomplete({
            source: function (request, response) {
                $.ajax({
                    url: 'Default.aspx/GetFruits',
                    data: "{ 'prefix': '" + request.term + "'}",
                    dataType: "json",
                    type: "POST",
                    contentType: "application/json; charset=utf-8",
                    success: function (data) {
                        if (data.d.length > 0) {
                            response($.map(data.d, function (item) {
                                return { label: item.Text, val: item.Value };
                            }))
                        } else {
                            response([{ label: 'No results found.', val: -1 }]);
                        }
                    }
                });
            },
            select: function (e, u) {
                if (u.item.val == -1) {
                    $(this).val("");
                    return false;
                }
            }
        });
    });
</script>
Enter search term:
<input type="text" id="txtSearch" />

C#

using System.Collections.Generic;
using System.Data.OleDb;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{
    [System.Web.Services.WebMethod]
    [System.Web.Script.Services.ScriptMethod()]
    public static List<ListItem> GetFruits(string prefix)
    {
        List<ListItem> fruits = new List<ListItem>();
        string str = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\Fruit.accdb;Persist Security Info = False; ";
        OleDbConnection con = new OleDbConnection(str);
        OleDbCommand cmd = new OleDbCommand("SELECT FruitId,FruitName FROM Fruits WHERE FruitName LIKE '%' + @Fruit + '%'", con);
        cmd.Parameters.AddWithValue("@Fruit", prefix);
        con.Open();
        OleDbDataReader odr = cmd.ExecuteReader();
        while (odr.Read())
        {
            fruits.Add(new ListItem { Text = odr["FruitName"].ToString().Trim(), Value = odr["FruitId"].ToString().Trim() });
        }
        con.Close();

        return fruits;
    }
}

VB.Net

Imports System.Collections.Generic
Imports System.Data.OleDb

Partial Class Default
    Inherits System.Web.UI.Page

    <System.Web.Services.WebMethod()> _
    <System.Web.Script.Services.ScriptMethod()> _
    Public Shared Function GetFruits(ByVal prefix As String) As List(Of ListItem)
        Dim fruits As List(Of ListItem) = New List(Of ListItem)()
        Dim str As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Fruit.accdb;Persist Security Info = False; "
        Dim con As OleDbConnection = New OleDbConnection(str)
        Dim cmd As OleDbCommand = New OleDbCommand("SELECT FruitId,FruitName FROM Fruits WHERE FruitName LIKE '%' + @Fruit + '%'", con)
        cmd.Parameters.AddWithValue("@Fruit", prefix)
        con.Open()
        Dim odr As OleDbDataReader = cmd.ExecuteReader()
        While odr.Read()
            fruits.Add(New ListItem With {.Text = odr("FruitName").ToString().Trim(), .Value = odr("FruitId").ToString().Trim()})
        End While
        con.Close()

        Return fruits
    End Function
End Class

Screenshot