Share
Go down
avatar
Admin
Posts : 155
Join date : 2017-11-11
Age : 27
View user profilehttp://net-media.yoo7.com

How can I autocomplete values in a TextBox from SQL Server table in C# with ASP.NET

on Wed Dec 13, 2017 12:16 pm
I got a SQL Table with column names as Name, Ascii_Name, Alternate Names. When user types name from the set of values, the auto complete has to display values correspondingly.

For example if the user types 'Aba' it has to show all the values that start with 'Aba' including the ones in 'alternate names' column which doesn't follow this pattern like abt. To make it more clear another example is as soon as he types Abayah, it has to display all the names which follow this pattern including the ones from alternate names as well like abahyahran,abayrafda.
Code:
Name      
 | Ascii_Name                        |                Alternate_names                                              |
+------------+---------------------------+----------------------------------------------------------------------------------+
| Abat       | Abat1                 | Abat, Abat1, abt                                                                |
| Abayahr    | Abayahr1         | Abayahr,Abayahr1, abayahr, abahyahran, abayrafda |
| Abayah     | Abayah1          | Abayah,Abayah1, abayahtr, abayahnwer                      |
| Abath       | Abath1              | Abath, Abath1, abatgh, abatdfg                                    |
| Carne       | Carnt                 | Carne, Carnt, canrtrt, carnas

I'm kind of clueless in achieving this requirement, any advise or suggestions or pointers in this direction would be appreciated.

I'm not asking for any code only only indicators or broad guidelines in achieving this.

Answer
You can either use stored procedure or LINQ with Entity Framework. Here is an example of stored procedure.

Stored procedure:
Code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[AutoComplete_Search]
    @keyword NVARCHAR(50)
AS
BEGIN
    SELECT Name, Ascii_Name, Alternate_names
    FROM your_table
    WHERE Name LIKE '%' + @keyword + '%' OR Ascii_Name LIKE '%' + @keyword + '%' OR Alternate_names LIKE '%' + @keyword + '%'

END
.cshtml code is:
Code:
<script type="text/javascript">
        $(function () {
            $("#txtCustomer").autocomplete({
                source: function (request, response) {
                    $.ajax({
                        url: '/Home/AutoComplete/',
                        data: "{ 'keyword': '" + request.term + "'}",
                        dataType: "json",
                        type: "POST",

                        contentType: "application/json; charset=utf-8",
                        success: function (data) {
                            response($.map(data, function (item) {
                                return item;
                            }))
                        },
                        error: function (response) {
                            alert(response.responseText);
                        },
                        failure: function (response) {
                            alert(response.responseText);
                        }
                    });
                },
                select: function (e, i) {
                    $("#hfCustomer").val(i.item.val);
                },
                minLength: 1
            });
        });
    </script>
    @using (Html.BeginForm("Index", "Home", FormMethod.Post))
    {
        <input type="text" id="txtCustomer" name="CustomerName" />   
    }
This is controller.
Code:
[HttpPost]
public JsonResult AutoComplete(string keyword)
{
    using (DatabaseEntities entities = new DatabaseEntities())
    {
        var records = entities.AutoComplete_Search(keyword).ToList();
        return Json(records);
    }
}
Back to top
Permissions in this forum:
You can reply to topics in this forum