Pass additional Parameter to ASP.Net AJAX AutoComplete Extender

dibyanshu.aryan6
 
on Oct 19, 2021 09:35 PM
261 Views

 autocomplete extender 2nd parameter value not working

when i remove shop_id in where condition its working but with it not working

[ScriptMethod]
[WebMethod]
public static List<string> GetCompletionList(string prefixText, int count, string contextKey)
{
    using (SqlConnection con = new SqlConnection())
    {
        con.ConnectionString = ConfigurationManager.ConnectionStrings["DBconn"].ConnectionString;
        using (SqlCommand com = new SqlCommand())
        {
            if(contextKey != "")
            {
                com.CommandText = "select cust_name from cust_reg where shop_id = @id and cust_name like @Search + '%'";
                com.Parameters.AddWithValue("@Search", prefixText);
                com.Parameters.AddWithValue("@id", contextKey);
            }

            com.Connection = con;
            con.Open();
            List<string> countryNames = new List<string>();
            using (SqlDataReader sdr = com.ExecuteReader())
            {
                while (sdr.Read())
                {
                    countryNames.Add(sdr["cust_name"].ToString());
                }
            }
            con.Close();
            return countryNames;
        }
    }
}

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
arjunv
 
on Oct 20, 2021 03:25 AM

Dear dibyanshu.aryan6,

   kindly refer below Sample.

HTML

<div>
    <asp:HiddenField ID="hfSession" runat="server" />
    <asp:ScriptManager ID="ScriptManager1" runat="server"
        EnablePageMethods="true">
    </asp:ScriptManager>
    <asp:TextBox ID="txtContactsSearch" runat="server" onkeyup="SetContextKey()"></asp:TextBox>
    <cc1:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server" ServiceMethod="SearchCustomers" TargetControlID="txtContactsSearch"
        MinimumPrefixLength="2" CompletionInterval="100" EnableCaching="false" CompletionSetCount="10" UseContextKey="true" FirstRowSelected="false">
    </cc1:AutoCompleteExtender>
</div>
<script type="text/javascript">
    function SetContextKey() {
        $find('AutoCompleteExtender1').set_contextKey($get("<%=hfSession.ClientID %>").value);
    }
</script>

Namespaces

C#

using System.Data.SqlClient;
using System.Configuration;
using System.Web.Services;

VB.Net

Imports System.Configuration
Imports System.Data.SqlClient
Imports System.Web.Services

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        Session["Country"] = "France";
        hfSession.Value = Session["Country"].ToString();
    }
}

[WebMethod]
public static List<string> SearchCustomers(string prefixText, int count, string contextKey)
{
    List<string> customers = new List<string>();
    if (!string.IsNullOrEmpty(contextKey))
    {
        using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            using (SqlCommand cmd = new SqlCommand())
            {
                string cmdText = "SELECT ContactName FROM Customers WHERE ContactName LIKE @SearchText + '%' AND Country = @Country";
                cmd.Parameters.AddWithValue("@SearchText", prefixText);
                cmd.Parameters.AddWithValue("@Country", contextKey);
                cmd.CommandText = cmdText;
                cmd.Connection = conn;
                conn.Open();
                using (SqlDataReader sdr = cmd.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        customers.Add(sdr["ContactName"].ToString());
                    }
                }
                conn.Close();
            }
        }
    }

    return customers;
}

 VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        Session("Country") = "France"
        hfSession.Value = Session("Country").ToString()
    End If
End Sub

<WebMethod>
Public Shared Function SearchCustomers(ByVal prefixText As String, ByVal count As Integer, ByVal contextKey As String) As List(Of String)
    Dim customers As List(Of String) = New List(Of String)()

    If Not String.IsNullOrEmpty(contextKey) Then

        Using conn As SqlConnection = New SqlConnection()
            conn.ConnectionString = ConfigurationManager.ConnectionStrings("constr").ConnectionString

            Using cmd As SqlCommand = New SqlCommand()
                Dim cmdText As String = "SELECT ContactName FROM Customers WHERE ContactName LIKE @SearchText + '%' AND Country = @Country"
                cmd.Parameters.AddWithValue("@SearchText", prefixText)
                cmd.Parameters.AddWithValue("@Country", contextKey)
                cmd.CommandText = cmdText
                cmd.Connection = conn
                conn.Open()

                Using sdr As SqlDataReader = cmd.ExecuteReader()

                    While sdr.Read()
                        customers.Add(sdr("ContactName").ToString())
                    End While
                End Using

                conn.Close()
            End Using
        End Using
    End If

    Return customers
End Function

Screenshot