Autocomplete extender for textbox not working

I using AJAX Autocomplete extender to setup my own AutoComplete textbox by going thru examples from diff AJAX & Asp forums. I am writing the code suing c#.

i dont seem to be getting any syntactical errors. The problem is, when i run the code, the autocomplete doesn't work. i have tried to code in three diff ways, as u will see below in the code snippet below. two of them have been commented for my personal use. i have aslo name the webservice as test (not autocomplete, as is the general trend).

I am a newbie to asp, ajax. so would prefer if explained in simple lang and great detail.

thanx.
using System;
using System.Collections;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Linq;
using jb_datamanipulate;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;


/// <summary>
/// Summary description for test
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// 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 test : System.Web.Services.WebService
{

    public test()
    {

        //Uncomment the following line if using designed components 
        //InitializeComponent(); 
    }

    [WebMethod]
    public string[] GetCompanyName(string prefixText)
    {
        string sql = "select CompanyName from Employers where CompanyName like @prefixText";
        
        SqlDataAdapter da = new SqlDataAdapter(sql, ConfigurationManager.ConnectionStrings["ConnStringpkt"].ConnectionString);
        da.SelectCommand.Parameters.Add("@prefixText", SqlDbType.NVarChar, 200).Value = (prefixText + "%");
        DataTable dt = new DataTable();
        da.Fill(dt);
        string[] items = new string[dt.Rows.Count];
        int i = 0;
        foreach (DataRow dr in dt.Rows)
        {
            items.SetValue(dr["CompanyName"].ToString(), i);
            i++;
        }
        return items;

        // code 2 start

        //cscSQLdata objData = new cscSQLdata();
        //string strConnection = "";
        //strConnection = ConfigurationManager.ConnectionStrings["ConnStringpkt"].ToString().Trim();

        //objData.connectionstring = strConnection;

        ////int count = 10;
        //int i = 0;
        //string sql = "select CompanyName from Employers where CompanyName like @prefixText";

        ////string sql = "select CompanyName from Employers where CompanyName like '" + prefixText + "%'";

        //SqlDataAdapter da = new SqlDataAdapter(sql,"strConnection");
        //da.SelectCommand.Parameters.Add("@prefixText", SqlDbType.NVarChar, 200).Value = prefixText + "%";
        //DataTable dt = new DataTable();
        //da.Fill(dt);
        //string[] items = new string[dt.Rows.Count];

        //foreach (DataRow dr in dt.Rows)
        //{
        //    items.SetValue(dr["CompanyName"].ToString(), i);
        //    i++;
        //}
        //return items;

        // code 2 end
        
        
        ///code 3 start xxxxxxxxxxxxxxxxxxxx-------------------xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

        //DataSet dtst = new DataSet();

        //SqlConnection sqlCon = new SqlConnection(ConfigurationManager.AppSettings["ConnStringpkt"].ToString().Trim());

        //string sql = "select CompanyName from Employers where CompanyName like '" + prefixText.ToString() + "%' ";
        ////string strSql = "SELECT CountryName FROM Tbl_Countries WHERE CountryName LIKE '" + prefixText + "%' ";
        ////string sql = "select CompanyName from Employers where CompanyName like @prefixText";

        //SqlCommand sqlComd = new SqlCommand(sql, sqlCon);

        //sqlCon.Open();

        //SqlDataAdapter sqlAdpt = new SqlDataAdapter();

        //sqlAdpt.SelectCommand = sqlComd;

        //sqlAdpt.Fill(dtst);

        //string[] cntName = new string[dtst.Tables[0].Rows.Count];

        //int i = 0;

        //try
        //{

        //    foreach (DataRow rdr in dtst.Tables[0].Rows)
        //    {

        //        cntName.SetValue(rdr["CompanyName"].ToString(), i);

        //        i++;

        //    }

        //}

        //catch { }

        //finally
        //{

        //    sqlCon.Close();

        //}

        //return cntName;

        /// code 3 end xxxxxxxxxxxxxxx---------------------------xxxxxxxxxxxxxxxxxxxxxxxxxxxxx

    }

}

--------------------------------------
the aspx part
--------------------------------------

body>
    <form id="form1" runat="server">
    <asp:ScriptManager EnablePartialRendering="true" ID="ScriptManager1" runat="server">
    <Services>

                <asp:ServiceReference Path="~/test.asmx" />

            </Services>
    </asp:ScriptManager>

<tr>          
<td align="left">

Company Name:<asp:RequiredFieldValidator ID="RequiredFieldValidator5" runat="server"
ValidationGroup="jobmaster" Display="Dynamic" ControlToValidate="txtCompanyName" 
Text='&nbsp;<img src="../images/app_common/required.png" />'>
</asp:RequiredFieldValidator><br />

<asp:TextBox ID="txtCompanyName" runat="server" Width="95%" CssClass="textbox_normal">
</asp:TextBox>

<cc1:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server" MinimumPrefixLength="1" 
 ServiceMethod="GetCompanyName" ServicePath="~/test.asmx" TargetControlID="txtCompanyName" onclientpopulating="ShowIcon"
 onclientpopulated="ShowIcon" Enabled="true" EnableCaching="true">
 </cc1:AutoCompleteExtender>
                    </td>
                    </tr>

Open in new window

ahadeeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

masterpassCommented:
More information needed ....

Is the web service hit ?
Is query is executed and returning results?
Is items array populated correctly?

If all of these are working perfectly make sure that you don't have java script errors in your page.
0
ahadeeAuthor Commented:
@masterpass,

could u pls elaborate. this is my first time using asp. how do i check, if the 3 things are working fine. thankx.
0
masterpassCommented:
1. put a break-point ans see if its getting it
2. type your query in SQL Server and see if data is coming ( alternatively when you put the break point in the web service you can see whether it is returning results
3. The break point will assist you in this case also

I think you are having problems retrieving the data from the DB and your query is not returning the data or JS error. The JS error can be seen in the left most corner of the browser[in IE]
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

masterpassCommented:
Also, no need for rehistering the service with scriptmanager

<asp:ScriptManager EnablePartialRendering="true" ID="ScriptManager1" runat="server">
    <Services>
                <asp:ServiceReference Path="~/test.asmx" />
            </Services>
</asp:ScriptManager>

can be replaced as

<asp:ScriptManager EnablePartialRendering="true" ID="ScriptManager1" runat="server" />
0
ahadeeAuthor Commented:
@masterpass,

i tried as u suggested, and i believe that the query might not be correct. I would like to ask, what do i write instead of @prefixText.

thanx a lot...
sql-querry-issue.jpg
sql-querry-issue-2.jpg
0
masterpassCommented:
Put the breakpoint at  "return items;"   in the GetCompanyName function and see

Have a look here : http://infinitezest.com/articles/using-autocompleteextender-with-results-from-database.aspx
0
ahadeeAuthor Commented:
@masterpass,

i copy- pasted the same code and tailored it to fit my needs. i used the break pts, and what happens is that, the break pt does not jump from the

step a:  SqlDataReader reader = sqlCmd.ExecuteReader();

to

step b: while (reader.Read())
                            CompNameList.Add(reader["CompanyName"].ToString());

any ideas. thanx for explaining...a lot!
1a.jpg
1b.jpg
1c.jpg
0
ahadeeAuthor Commented:
@masterpass,

as u mentioned earlier, do u think it has anything to do with populating....(just a thought)

code snippet below:  


count = 10;
                string connString = ConfigurationManager.ConnectionStrings["ConnStringpkt"].ToString().Trim();

                string selectString = "SELECT CompanyName from Employers WHERE CompanyName LIKE ’"+ prefixText +"%’";

                List<String> CompNameList = new List<string>(count);
                //List<String> CompNameList = new List<string>(count);
                using (SqlConnection sqlConn = new SqlConnection(connString))
                {
                    sqlConn.Open();

                    using (SqlCommand sqlCmd = new SqlCommand(selectString, sqlConn))
                    {
                        SqlDataReader reader = sqlCmd.ExecuteReader();
                        while (reader.Read())
                            CompNameList.Add(reader["CompanyName"].ToString());
                    }
                }
                return (CompNameList.ToArray());

Open in new window

0
masterpassCommented:
change

return  CompNameList.ToArray()

as

return CompNameList.ToString(); and put a break point only in this line ... and see what comes
0
ahadeeAuthor Commented:
@masterpass,


Error      3      Cannot implicitly convert type 'string' to 'string[]'      

I tried this right now and got the above error.
0
masterpassCommented:
modify your function as follows

public string GetCompanyName(string prefixText)
{
--
--

return CompNameList.ToString();
}
0
ahadeeAuthor Commented:
@masterpass.

tried that...but no change...

also tried putting the break pt at return CompNameList.ToString();

the code just ran, and did'nt break anywhere.


The same situation as b4 is occuring, which is not jumping from

step a:  SqlDataReader reader = sqlCmd.ExecuteReader();

to

step b: while (reader.Read())
                            CompNameList.Add(reader["CompanyName"].ToString());


I have posted an .jpg file, though it may not be of much use.

Thanx for taking the time, once again.
1d.jpg
0
masterpassCommented:
just put only one break point .. in the line

return CompNameList.ToString();

and see whether it returns any data
0
ahadeeAuthor Commented:
@masterpass,

Nope, i tried that already...its not returning any data. In fact, it doesn't even break at that point. Any ideas why?
0
masterpassCommented:
As of I see it .. I can't think of a reason why It should be behaving like this. Just make sure you have the data in DB ... Try modifying to this code
using (SqlConnection sqlConn = new SqlConnection(connString))
{
    sqlConn.Open();

    SqlCommand sqlCmd = new SqlCommand(selectString, sqlConn)
    SqlDataReader reader = sqlCmd.ExecuteReader();
    while (reader.Read())
        CompNameList.Add(reader["CompanyName"].ToString());
    
}

Open in new window

0
masterpassCommented:
one more Question .. Is your project configured as a AJAX Enabled website

Have a look at the last part : http://www.joelhainley.com/index.php/2010/01/19/asp-net-ajax-autocompleteextender-not-working/

If that seems to you as an issue ,, have a look towards conversion : http://www.asp.net/AJAX/Documentation/Live/ConfiguringASPNETAJAX.aspx
0
ahadeeAuthor Commented:
@masterpass,

Dont know if all of this is driving u crazy. Sorry abt that. Thanx for sticking with it, though.

I tried ur modification. no change. And the documetation seem correct to the best of my scrutiny. It does seem to be ajax enabled.

i just want to know, what should happen if we put the break point only at return CompNameList.ToString();

B'coz, for me, it just runs and doesnt break. So i put a break point in the 2nd line of the code...to see whats happening. And as i mentioned it runs fine till SqlDataReader reader = sqlCmd.ExecuteReader().

it doesnt go to the next step, which is the while.

also data in db is correct. i tried out the query once again in sql:

select CompanyName from Employers where CompanyName like 'i%'

that works perfectly.

btw. this query is in a string called selectString, and the whole query is inside " " (naturally, as it is a string). but, will it do execute the query without the  double quotes.

would like any further info...that might make it easier for u to check. thanx for ur time.
0
masterpassCommented:
Can you post your latest code of the function GetCompanyName. Please include the whole function ... I would appreciate If you can make a quick  post ...
0
ahadeeAuthor Commented:
@masterpass,

btw...my CompanyName field is of Nvarchar type of length =200; does that affect the functioning?
0
ahadeeAuthor Commented:
@masterpass,

Sorry for the delay... I just saw ur post right now.
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Collections;
using System.Web.Services;
using System.Web.Services.Protocols;
//using System.Web.Script.Services;

        /// <summary>
        /// Summary description for WebService
        /// </summary>
        //[ScriptService]
        [WebService(Namespace = "http://tempuri.org/")]
        [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

// 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 WebService : System.Web.Services.WebService
        {
            public WebService()
            {
                //Uncomment the following line if using designed components
                //InitializeComponent();
            }
            [System.Web.Script.Services.ScriptMethod]
            [WebMethod]
            public string GetCompanyName(string prefixText, int count)
            {

                count = 10;
                string connString = ConfigurationManager.ConnectionStrings["ConnStringpkt"].ToString().Trim();

                string selectString = "SELECT CompanyName from Employers WHERE CompanyName LIKE ’"+prefixText+"%’";

                List<String> CompNameList = new List<string>(count);
                //List<String> CompNameList = new List<string>(count);
                using (SqlConnection sqlConn = new SqlConnection(connString))
                {
                    sqlConn.Open();

                    SqlCommand sqlCmd = new SqlCommand(selectString, sqlConn);
                   
                        SqlDataReader reader = sqlCmd.ExecuteReader();
                        while (reader.Read())
                            CompNameList.Add(reader["CompanyName"].ToString());
                    
                }
                return CompNameList.ToString();
           
           }
        }

Open in new window

0
masterpassCommented:
Try this .. copy this as it is and try
[System.Web.Script.Services.ScriptMethod]
[WebMethod]
public string[] GetCompanyName(string prefixText, int count)
{

    count = 10;
    string connString = ConfigurationManager.ConnectionStrings["ConnStringpkt"].ToString().Trim();

    string selectString = "SELECT CompanyName from Employers WHERE CompanyName LIKE '" + prefixText + "%'";

    List<String> CompNameList = new List<string>(count);
    //List<String> CompNameList = new List<string>(count);
    using (SqlConnection sqlConn = new SqlConnection(connString))
    {
        sqlConn.Open();

        SqlCommand sqlCmd = new SqlCommand(selectString, sqlConn);
       
            SqlDataReader reader = sqlCmd.ExecuteReader();
            while (reader.Read())
                CompNameList.Add(AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem(reader["CompanyName"].ToString(),"dummyvarible"));// no need to do any thing with dummy varible
        
    }
    return CompNameList.ToArray();
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ahadeeAuthor Commented:
@masterpass,

Yay, that worked! Thanx a lot...although, what exactly was the problem, if u don't mind explaining.

Thanx once again and apologies for all the time delays...

Regards,
ahadee
0
masterpassCommented:
ahadee,

Two guesses, I did only two changes

1. string selectString = "SELECT CompanyName from Employers WHERE CompanyName LIKE ’"+prefixText+"%’"; [If you look at the single quote part ’"+prefixText+"%’" , just befre the double quotes, its not proper. I had to correct that to proper single quote

2. AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem() --> changes to autocomplete item ( we had a similar issue  some long time back, could remember the first time)

NB: Also the function returns string[] like your original function...

I am glad that it worked :)
0
ahadeeAuthor Commented:
@masterpass,

Thanx for explaining too...hope u wont mind me racking ur brain in the future!   :-)

Regards,
ahadee
0
masterpassCommented:
no probs .. any time :)
0
ahadeeAuthor Commented:
@masterpass,

Supposing i want to use autocomplete for another field, do i have to create another webservice page for it...or can i make write another function in the above webservice page?
0
masterpassCommented:
you can write another function to do so ... no need to create another webservice
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.