AJAX Auto Complete with sql look-up

Ok he's the thing,

I have the following auto complete on a aspx page for a seach function, im wanting it to look-up the names using the sql on line 20

1<ajaxToolkit:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">2 <Services>3 <asp:ServiceReference Path="AutoComplete.asmx" />4 </Services>5 </ajaxToolkit:ToolkitScriptManager>6 <h1>7 Search Page</h1>8 <table>9 <tr>10 <td>11 <asp:TextBox ID="searchTextBox" runat="server"></asp:TextBox></td>12 <td style="width: 100px">13 <asp:Button ID="searchButton" runat="server" Text="Search" /></td>14 </tr>15 </table>16 <ajaxToolkit:AutoCompleteExtender runat="server" ID="autoComplete1" TargetControlID="searchTextBox"17 ServicePath="AutoComplete.asmx" ServiceMethod="GetCompletionList" MinimumPrefixLength="2"18 CompletionInterval="1000" EnableCaching="true" CompletionSetCount="12">19 </ajaxToolkit:AutoCompleteExtender>20 <asp:SqlDataSource ID="autoCompleteSqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:autoCompleteConnectionString%>" SelectCommand="SELECT [fishName] FROM [tblFishTypes]"></asp:SqlDataSource>

Then i've the following code in the asmx file...

1Imports System.Web2Imports System.Web.Services3Imports System.Web.Services.Protocols4Imports System.Collections.Generic56<WebService(Namespace:="http://tempuri.org/")> _7<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _8<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _9<System.Web.Script.Services.ScriptService()> _10Public Class AutoComplete11 Inherits System.Web.Services.WebService1213 <WebMethod()> _14 Public Function GetCompletionList(ByVal prefixText As String, ByVal count As Integer) As String()15 Dim c1 As Char16 Dim c2 As Char17 Dim c3 As Char1819 If (count = 0) Then20 count = 1021 End If2223 Dim rnd As New Random()2425 Dim items As New List(Of String)2627 For i As Integer = 1 To count2829 c1 = CStr(rnd.Next(65, 90))30 c2 = CStr(rnd.Next(97, 122))31 c3 = CStr(rnd.Next(97, 122))3233 items.Add(prefixText + c1 + c2 + c3)34 Next i3536 Return items.ToArray()37 End Function3839End Class
 
Now i know i need to swap out the lines 23-33 and point instead to my sql connection string... right? but how? The random chaaters work but i want to limit the autocomplete to my database
 Thanks Dooie

The data source declared on the ASPX page is out of the scope on the web service. Create a SQL Data Reader and create an array of fish names from within the method.

Nope sorry, im still lost, could explain a little further?

Hi,

You can replace them by creating a SqlDataAdapter to fetch data from database.

Hereis an example of using SqlDataAdapter.

Ajax address lookup

Hi everyone,

The need has arisen to allow the users to query a database containing all the names of the contacts.

I was thinking that this would be an excellent task for ajax.

The primary requirement of the operation is to allow the user to enter the keystrokes

of the person they are looking for, and have the list of possible records displayed

in a grid, and as the users types in more letters, the list gets smaller and smaller.

A round trip to the database as the old school methods would allow simply

is not acceptable any more...

I have setup a web service to return a dataset based on the field and text that is entered

in the form, the problem i am having is actually using ajax to call the web service and

update the datagrid with the results from the web service call.

does anyone have a sample or know of a website that has a sample for this operation??

thanks

tony

The autocomplete extender does that.

http://ajax.asp.net

Thought this might help you all out there;

First, Some good resources to test with;

USA Zip code Information[Get State Code,City,Area Code,Time Zone,Zip Code ]

http://www.webservicex.net/uszip.asmx?wsdl

------------------------------------------

USA Zip code Information
[Get State Code,City,Area Code,Time Zone,Zip Code ]
http://www.webservicex.net/uszip.asmx?wsdl

------------------------------------------

I have to dig up my generic sample code then I'll post it here as well but, it's easier to write the code than to find the free services.

Yes, please post some sample code. I'm very interested in an exmple showing how to make use of that particular feature from them.