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.