How Do I: Use the ASP.NET AJAX AutoComplete Control

Hi Joe, maybe you have a sample about this control using a database?

Hi Joe,

  I have an external web service that has been created for me to allow me access to a db. How can I use the AJAX to connect to this web service?

Jorge08 this is a small example with Access Database:

If (count = 0) Then

           count = 10

       End If

       Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("App_Data/ExpImpdb.mdb") & ";"

       Dim adp As New System.Data.OleDb.OleDbDataAdapter("select * from cars WHERE RecMerk Like '" & prefixText & "%' ", strConn)

       Dim dt As New System.Data.DataTable

       adp.Fill(dt)

       Dim items As New List(Of String)

       For i As Integer = 0 To dt.Rows.Count

           Dim datafile

           datafile = UCase(dt.Rows(i).Item("RecMerk"))

           i += 1

           items.Add(datafile)

       Next

       Return items.ToArray()

Thanks Joe! I've wanted to try making an AJAX control like this for a while, but always thought it'd be more complex - you made it look easy.

From a beginner's viewpoint it wasn't obvious to me that when creating the webmethod, you can change the webmethod name as you like but the parameter names _must_ be prefixText and count (but maybe that's me being dumb)

It seems pretty easy to hook this up to a database using LINQ. If it's of any help, here's my code to query product names in Northwind, (assumes you've already made the linq to northwind class)

[WebMethod]

public string[] GetCompletionList(string prefixText, int count)

{

 // OBS! In real world, initialize this someplace else

 NorthwindDataContext Db = new NorthwindDataContext();

 var matches = from p in Db.Products

 where p.ProductName.StartsWith(prefixText)

 select p.ProductName;

 return matches.ToArray<string>();

}

Hi Joe,

I tried the code and is working fine but when I implemented URL rewriting its displaying a js error 'Syntax error' and the auto complete is not working its displaying results as undefined. If I remove the code

<asp:ScriptManager ID="ScriptManager1" runat="server">

   <Services>

       <asp:ServiceReference Path="AutoComplete.asmx" />

   </Services>

</asp:ScriptManager>

there is no js error. I'm using C# and the auto complete text box is filled from DB. How can I solve this issue. Please help.

Thanks

Hi Joe, I just wanted to say thanks. I’m interning with Goldman Sachs- Tech Division. Your videos have saved my life on numerous occasions. You are appreciated.

Hello Joe,

I have downloaded your VB code and try to run it on my computer, it does work in firefox but doesn't work in IE6 and up. The example about the autocomplete that is on Ajax Toolkit live page works with all platform. Any suggestion here?

Thanks

Jagar,

when i tried to use this example and get info out of my database it keeps telling me that my variable.toarray is wrong and gives me an error of type'1-dimensional array of string cannot be converted to string??? why is that?

here is my code please help.

Dim dtst As DataTable

       Dim sqlconnection As SqlConnection = New SqlConnection("connectionstring")

       Dim objcmd As SqlCommand = New SqlCommand("select namefrom students where name like '" + prefixTex + "%' ", sqlconnection)

       sqlconnection.Open()

       Dim sqladpt As SqlDataAdapter = New SqlDataAdapter

       sqladpt.SelectCommand = objcmd

       sqladpt.Fill(dtst)

       Dim cntname As List(Of String)

       For i As Integer = 0 To dtst.Rows.Count

           Dim datafile

           datafile = UCase(dtst.Rows(i).Item("description"))

           i += 1

           cntname.Add(datafile)

       Next

       Return cntname.ToArray

thank you in advnace

there should be a space between name and form but the error is from cntname.toarray.

sorry i was typing too fast the line

dtatfile=ucase(dtst.rows(i).item("description")

should be

dtatfile=ucase(dtst.rows(i).item("name")

got it there was a mistake on the public function (prefixtex as string) as string() but the textbox doesnt work. it doesn't autocomplete

hi,

how can i show data in autocomplete list from northwind database in C#.I am a novice programmer.

Hi ,

Video is excellent. i want key/value pair, for example, if i select country name and value stored in the hidden field.

please let me how can i achive this.

Thanks

Gopi

Hi Joe, maybe you have a sample about this control using a database from C# code? Please help me.

Hi,

I ported the code to my WebApp and it works as long as I enter the full url including port number (localhost/AutoComplete.asmx) as the ServicePath Attribute for the AutoComplete Control. Is it possible to let the webservice run on a specified port number, or how did you manage to ommit the portnumber in your examples?

bill

hey gopikrishnag,

i had the same issue with key/value.

look here, it's easy:

blogs.msdn.com/.../how-to-use-a-key-value-pair-in-your-autocompleteextender.aspx

I've tried this using Visual Studio 2005 (with the AJAX 1.0 extensions for ASP.net 2.0 installed on my server). Browsing with IE6.0.2 sp2.

I've followed the steps in the video, but I get an annoyingly vague JavaScript error on my .aspx page saying, simply that:

'Sys' is undefined.

When I view the source of the .aspx page, I find that the Sys assembly is indeed required, and it seems to initialise my ScriptManager, which contains the details of the WebService I've created. It looks like it's fundamental to the method, but the CDATA section seems to be auto-generated.

//<![CDATA[

Sys.WebForms.PageRequestManager._initialize('ScriptManager1 ', document.getElementById('form1'));

Sys.WebForms.PageRequestManager.getInstance()._updateContro ls([], [], [], 90);

//]]>

So my question is, how can I satisfy this auto-generated dependency on Sys.WebForms.PageRequestManager to make my page load without errors, and therefore be able to AutoComplete the text box?

graham

Very good tutorial.....thanks

hi joe,i have done what you said in the video,but i am not geting output.webservice is running fine, is it getting values .but in .aspx i am not getting the output ...what shiould i do now??.......thanx in advance.

The video is excellent.

Is it possible to add an argument in the web service method?

How to pass value to the additional argument?

thank you

I finally got my SubSonic/Stored Procedure call to work with the Ajax AutoComplete:

   public string[] GetCompletionList(string prefixText, int count)

   {

       if (count == 0)

       {

           count = 10;

       }

       List<string> items = new List<string>(count);

       SubSonic.StoredProcedure sp = new SubSonic.StoredProcedure("GetAllInfo");

       sp.Command.AddParameter("@pIn", "RIGHT");

       sp.Command.AddParameter("@sSearch", prefixText);

       DataSet ds = sp.GetDataSet();

       foreach (DataRow dr in ds.Tables[0].Rows)

       {

           items.Add(dr[0].ToString());

       }

       return items.ToArray();

   }

Hi Joe,

Great demo. Of course I got some problems with the whole AutoComplete extender.

I am try to use it in two places:

1. A master page and

2. on a web user control

I have followed your example step by step.  When I create a new project and run it, it does run fine.

The problem is when I try to implement it on my "real" project.

On the bottom of the browser (IE 7) I get the "warning message" when I double click on it it give me the following message:

"Error: Sys.ArgumentUndefineException: Value cannot be undefined. Paramenter Name: type"

Any ideas what is going on in here????

I would appreciate any help since I have been trying to add this functionality for a while now.....

Thanks in advance

Hi I try this program and it works fine. Accept when I am using a database file with information. The field that I am trying to load is a sql field with nvar(9) but it has only numbers. When I do Return items.ToArray only the items that do not start with zeros are loaded to the array. I look at the field before it is added to the array and it has the leading zero, but it does not get returned. I want all fields loaded even the ones with the zeros. Please help.  Diana

Hi

Im trying to use this control on a webpart (user control) using a static method [webmethod], for some reason this does not work, the event never triggers. Is there a known issue about this extender and a user control or an issue implementing the method of the extender in a non webservise method???

Thanks in advance

bob

I have a textbox where users are required to enter values starting with zero. The autocomplete does not match values read with leading zeros, is it a bug or do i have to do some settings.

Just a small suggestion needed, if I change MinimumPrefixLength to 4 and then search for "New York",

then it does not count the space for it. ie "New " will not return any results.

Any suggestion for this?

Thanks

Nice work. But can you tell me, how to fetch values from Database? I mean using SqlConnetion and DataSet.

nice work but possible bog when using with firefox3, there's a gap between the textbox and the results, but there's no gap when using the same code in IE7, any idea anyone? and how can i show the results in someplace else, like a listbox?

Hi

The video was helpful. I have tried to impliment the autocompleteExtender during the row editing of a gridview as shown below:

<asp:GridView ID="Contact_Grdvw" runat="server" OnRowDeleting="Contact_Grdvw_RowDeleting"

                           BackColor="White" BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px"

                           OnRowEditing="Contact_Grdvw_RowEditing" OnRowUpdating="Contact_Grdvw_RowUpdating"

                           OnRowCancelingEdit="Milestone_Grdvw_RowCancelingEdit" CellPadding="3" AutoGenerateColumns="False">

                           <RowStyle BackColor="White" ForeColor="#3333CC" />

                           <Columns>

                               <%--<asp:TemplateField HeaderText="Edit-Update">

                                   <ItemTemplate>

                                       <asp:LinkButton ID="btnEdit" runat="server" Text="Edit" OnClick="Edit_contact_Click"

                                           CausesValidation="false" />

                                   </ItemTemplate>

                               </asp:TemplateField>--%>

                               <asp:CommandField HeaderText="Edit-Update" ShowEditButton="True" />

                               <asp:TemplateField HeaderText="Delete">

                                   <ItemTemplate>

                                       <asp:LinkButton ID="deleteButton" runat="server" CommandName="Delete" Text="Delete"

                                           OnClientClick="return confirm('Are you sure you want to delete ?');" />

                                   </ItemTemplate>

                               </asp:TemplateField>

                               <asp:TemplateField HeaderText="Last name">

                                   <ItemTemplate>

                                       <asp:Label ID="Label1" runat="server" Text='<%# Bind("Last_Name") %>'></asp:Label>

                                   </ItemTemplate>

                                   <EditItemTemplate>

                                       <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("Last_Name") %>'></asp:TextBox>

                                       <ajaxToolkit:AutoCompleteExtender

                                       ID="AutoCompleteExtender1"

                                       runat="server"

                                       TargetControlID="TextBox2"

                                       ServicePath="AutoComplete.asmx"

                                       ServiceMethod="GetCompleteList"

                                       MinimumPrefixLength="2"

                                       CompletionInterval="10"

                                       EnableCaching="true">

                                       </ajaxToolkit:AutoCompleteExtender>

                                   </EditItemTemplate>

                               </asp:TemplateField>

                               <asp:BoundField DataField="First_Name" HeaderText="First Name" />

                               <asp:BoundField DataField="Email" HeaderText="Email" />

                               <asp:BoundField DataField="Dept" HeaderText="Dept" />

                               <asp:BoundField DataField="Notify" HeaderText="Notify" />

                               <asp:BoundField DataField="Role" HeaderText="Role" />

                               <asp:BoundField DataField="contact_id" HeaderText="ID" />

                           </Columns>

                           <PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />

                           <SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />

                           <HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />

                       </asp:GridView>

and the remaining webservice code and the services tag in the script manager tag are same as in your example.

When i use the above griview there are no errors but the auto complete feature doesn't work during the row editing.

Please help me out if i am missing some thing

Thanks

Anand

Hi, Can anybody explain me how to get the sugessted values in a list using autocomplete extender with only the horizontal scrolling visible as the list is too large and goes very lengthy.I have tried iimplementing it with a <div id="div1" style="height:inherit; overflow:scroll">tag but it is showing both the vertical and horizontal scroll bars.

And also can I get the style sheet  explainig the other properties that are shown when a list is complete and item highlighted etc.