how to use ajax autocomplete extender to fill data from database using webservice?

Question

I have been stuck with part for past 3 hours. searching over the internet, reading blogs, looking and testing codes and examples but got nothing.

I m working with Ajax Auto Complete Extender from Ajax Control Toolkit on textbox and want to generate the lest of issues from the database based on the text inputted by the user.

For this i have created a webservice. The method in the webservice is -

    namespace CeteraQMS
    {
        /// <summary>
        /// Summary description for SearchIssues
        /// </summary>
        [WebService(Namespace = "http://tempuri.org/")]
        [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
        [ToolboxItem(false)]
        // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
        [System.Web.Script.Services.ScriptService]
        public class SearchIssues : System.Web.Services.WebService
        {        
            [WebMethod]
            [ScriptMethod]
            public string[] GetCompletionList(string prefixText, int count)
        {
            DataSet ds = null;
            DataTable dt = null;
            OracleConnection conn = null;
            StringBuilder sb = new StringBuilder();
            try
            {
                conn = new OracleConnection("Data Source=advbniit; User ID=usr; Password=abc providerName=System.Data.OracleClient");
                sb.Append("select issueno from cet_sepcet where issueno like '");
                sb.Append(prefixText);
                sb.Append("%'");
                OracleDataAdapter daRes = new OracleDataAdapter(sb.ToString(), conn);
                ds = new DataSet();
                daRes.Fill(ds);
                dt = ds.Tables[0];
            }           
            catch (Exception exc)
            {

            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }

            List<string> IssueList = new List<string>();
            for (int i = 0; i < dt.DataSet.Tables[0].Rows.Count; i++)
            {
                IssueList.Add(dt.DataSet.Tables[0].Rows[i][0].ToString());
            }           
            return IssueList.ToArray();
        }
     }

I am calling this webservice as -

 <asp:TextBox ID="txtIssueNo" runat="server" Width="130px" Style="margin-left: 5px"
                onkeypress="return allowDigit(this);" MaxLength="7"></asp:TextBox>
            <asp:AutoCompleteExtender ID="AutoCompleteExtender1" EnableCaching="true" BehaviorID="AutoCompleteCities"
                TargetControlID="txtIssueNo" ServiceMethod="GetCompletionList" ServicePath="SearchIssues.asmx"
                MinimumPrefixLength="1" CompletionSetCount="10" runat="server" FirstRowSelected="true">
            </asp:AutoCompleteExtender>

Pure and simple. But to my surprise nothing is happening when I input text in my textbox. Please lead me as if where I m going wrong.

Thanks in advance Akhil

PS - No error nothing is happening.

Answer 1

Above your [WebMethod] what do you have? I had issues with ajax autocomplete too but I figured out, Ill provide you with my example. I replaced my table and column with yours but, you would need to add some extra info on your sinc you have a username and password. I dont see a GetRecord table, did you include it? Your ajax source code looks fine, but you can create a style sheet along with it to add more preferences if you like.

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


[WebService]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.Web.Script.Services.ScriptService]
public class AutoComplete : WebService
{
    public AutoComplete()
    {
    }

[WebMethod]
    public string[] GetCompletionList(string prefixText, int count)
    {
        if (count == 0)
        {
            count = 10;
        }
        DataTable dt = GetRecords(prefixText);
        List<string> items = new List<string>(count);
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            string strName = dt.Rows[i][0].ToString();
            items.Add(strName);
        }
        return items.ToArray();
    }

    public DataTable GetRecords(string strName)
    {
        string strConn = ConfigurationManager.ConnectionStrings["ProjectASPConnectionString"].ConnectionString;
        SqlConnection con = new SqlConnection(strConn);
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = System.Data.CommandType.Text;
        cmd.Parameters.AddWithValue("@issueno", strName); ///What name to place here
        cmd.CommandText = string.Format("Select distinct issueno as issueno from cet_sepcet where issueno like '{0}%'", strName); //what command to write here
        DataSet objDs = new DataSet();
        SqlDataAdapter dAdapter = new SqlDataAdapter();
        dAdapter.SelectCommand = cmd;
        con.Open();
        dAdapter.Fill(objDs);
        con.Close();
        return objDs.Tables[0];
    }
}