Get first column value on click of any cell from Ajax AutoComplete Textbox having multiple column using jQuery in ASP.Net

Last Reply 5 months ago By kalpesh

Posted 5 months ago

hello,

https://www.aspforums.net/Threads/458558/Ajax-AutoComplete-Textbox-with-multiple-column-using-C-and-VBNet-in-ASPNet/

 

i am usign this article it works fine, i only want that when user click on any row it should populate ID in textbox what needs to be change please advice

Posted 5 months ago

Just add OnClientItemSelected event of AutoCompleteExtender and make change to find the clicked cell and then find nearest Id column value.

HTML

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
    <style type="text/css">
        body
        {
            font-family: Arial;
            font-size: 10pt;
        }
        table
        {
            border: 1px solid #ccc;
        }
        table th
        {
            background-color: #F7F7F7;
            color: #333;
            font-weight: bold;
        }
        table th, table td
        {
            padding: 5px;
            border-color: #ccc;
            text-align: center;
        }
        #dynamic
        {
            background-color: none !important;
            color: none !important;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:ScriptManager ID="ScriptManager1" runat="server" EnablePageMethods="true">
        </asp:ScriptManager>
        <asp:TextBox ID="txtEmployeeSearch" runat="server"></asp:TextBox>
        <cc1:AutoCompleteExtender ServiceMethod="SearchEmployees" MinimumPrefixLength="1"
            CompletionInterval="100" EnableCaching="false" CompletionSetCount="10" TargetControlID="txtEmployeeSearch"
            OnClientPopulated="Employees_Populated" ID="AutoCompleteExtender1" runat="server"
            OnClientItemSelected="Employees_Selected" FirstRowSelected="false">
        </cc1:AutoCompleteExtender>
    </div>
    <div>
        <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
        <script type="text/javascript">
            function Employees_Populated(sender, e) {
                var employees = sender.get_completionList().childNodes;
                var div = "<table>";
                div += "<tr><th>EmployeeId</th><th>FirstName</th><th>LastName</th><th>City</th><th>Country</th></tr>";
                // assigned the Id value for the td to use later in Employees_Selected event
                for (var i = 0; i < employees.length; i++) {
                    div += "<tr><td >" + employees[i].innerHTML.split('-')[0]
                        + "</td><td id=F_" + employees[i].innerHTML.split('-')[0] + ">" + employees[i].innerHTML.split('-')[1]
                        + "</td><td id=L_" + employees[i].innerHTML.split('-')[0] + ">" + employees[i].innerHTML.split('-')[2]
                        + "</td><td id=Ci_" + employees[i].innerHTML.split('-')[0] + ">" + employees[i].innerHTML.split('-')[3]
                        + "</td><td id=Co_" + employees[i].innerHTML.split('-')[0] + ">" + employees[i].innerHTML.split('-')[4] + "</td></tr>";
                }
                div += "</table>";
                sender._completionListElement.innerHTML = div;
            }

            function Employees_Selected(sender, e) {
                // Check only the clicked cell is Td or not
                if (e._item.tagName == "TD") {
                    // Get completionList child Node value
                    var employees = sender.get_completionList().childNodes;
                    // check each Td 
                    $.each($(employees).find("tr").find("td"), function (i, item) {
                        // check wheather td id is not blank and tdid same as clicked item td id
                        if (item.id == e._item.id && item.id != "") {
                            // Assign value of first closest td which is Id column value to textbox
                            $("[id*=txtEmployeeSearch]").val($(this).closest('tr').find('td').html());
                            return false;
                        }
                    });
                } else {
                    // clear the textbox if its not td in case
                    $("[id*=txtEmployeeSearch]").val('');
                }
            }
        </script>
    </div>
    </form>
</body>
</html>

C#

[System.Web.Script.Services.ScriptMethod()]
[System.Web.Services.WebMethod]
public static List<string> SearchEmployees(string prefixText, int count)
{
    using (SqlConnection conn = new SqlConnection())
    {
        conn.ConnectionString = ConfigurationManager
                .ConnectionStrings["constr"].ConnectionString;
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandText = "SELECT EmployeeId, FirstName, LastName,City,Country FROM Employees WHERE FirstName like @SearchText + '%'";
            cmd.Parameters.AddWithValue("@SearchText", prefixText);
            cmd.Connection = conn;
            conn.Open();
            List<string> employees = new List<string>();
            using (SqlDataReader sdr = cmd.ExecuteReader())
            {
                while (sdr.Read())
                {
                    employees.Add(AjaxControlToolkit.AutoCompleteExtender
                        .CreateAutoCompleteItem(string.Format("{0}-{1}-{2}-{3}-{4}",
                        sdr["EmployeeId"].ToString(), sdr["FirstName"], sdr["LastName"], sdr["City"], sdr["Country"]),
                        sdr["EmployeeId"].ToString()));
                }
            }
            conn.Close();
            return employees;
        }
    }
}

VB.Net

<System.Web.Script.Services.ScriptMethod(), _
System.Web.Services.WebMethod()> _
Public Shared Function SearchEmployees(ByVal prefixText As String, ByVal count As Integer) As List(Of String)
    Dim conn As SqlConnection = New SqlConnection
    conn.ConnectionString = ConfigurationManager _
     .ConnectionStrings("constr").ConnectionString
    Dim cmd As SqlCommand = New SqlCommand
    cmd.CommandText = "select EmployeeId, FirstName, LastName,City,Country from " & _
    " Employees where FirstName like @SearchText + '%'"
    cmd.Parameters.AddWithValue("@SearchText", prefixText)
    cmd.Connection = conn
    conn.Open()
    Dim employees As List(Of String) = New List(Of String)
    Dim sdr As SqlDataReader = cmd.ExecuteReader
    While sdr.Read
        employees.Add(AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem(String.Format("{0}-{1}-{2}-{3}-{4}", sdr("EmployeeId").ToString(), sdr("FirstName"), sdr("LastName"), sdr("City"), sdr("Country")), sdr("EmployeeId").ToString()))
    End While
    conn.Close()
    Return employees
End Function

Screenshot