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 and browser refresh function

When a web app is 'ajaxified' to the extreme the browser accesses the app with a GET http request and from this moment on all interaction with the server is done through ajax.

The question I have now is how to deal with the browser refresh button. if the user presses it, all what the server is going to see is the initial GET request, which means that I am not getting viewstate or any other state variables, which in many cases is bound to break the application.

What do you think is the right way to deal with this problem? I do not really need this standard refresh functionality and would be glad to disable it, but I know of no reliable way of doing this.

Any thoughts?

I can think of two ways to handle this...none of which are optimal. One, you can put some sort of message warning the user what will happen if they use the refresh button. I can see using this temporarily or if you app is an in-house app. Two, you could use good ol' session to keep the user data on the back end. If the user hits refresh, you will have to build your page from that session data...so it basically won't matter if they hit refresh.

My app is a business app. Still, I can not expect that the refresh button will not be pressed. I need to handle it gracefully.

It looks like for now my definition of 'gracefully' will mean that I will

a) disable as many ways to cause a refresh as I can by intercepting keypresses with javascript, disabling popup menus, hiding the standard toolbar

b) if refresh makes it through my defences I will treat it as a new session.

I use the Really Simple History framework:

http://code.google.com/p/reallysimplehistory/

Ofcourse, you won't be able to use it with anything other than customjavascript .asmx calls. Leveraging Microsoft's Ajax Toolkit'scontrols you can't use anything liek that; well, you pretty much can'tdo ANY custom javascript.

Just something to look into.

This is cool stuff. We stole their idea and used a simplified variation of it to block the back button. Unfortunately it does not cover the refresh button. As to "can't do ANY custom javascript" I consider this to be a good thing - I do not trust my application programmers enough to give them free reign to do "ANY custom javascript". I hope that doing javascript within confines of the Microsoft ajax framework will give me better control over how the javascript is done.

Speaking of controlling the browser history - did you see thishttp://www.nikhilk.net/BackButtonSupport.aspx ?

Check out the ASP.NET Futures: http://www.asp.net/downloads/futures/, it has some support for managing browser history (http://quickstarts.asp.net/Futures/ajax/doc/history.aspx).

All that said, I don't think it guards against a refresh, but I could mistaken.

You could also try this technique...http://www.pascarello.com/rememberHistory.aspx

-Damien