I am a beginner in ASP.Net AJAX , I am using Visual Studio2005 and I am trying to implement AutoComplete with an Access database connection I search for the code which I should put inside the web service and I hardly found this code which is written in VB and deal with access.. but still I don’t know wither this code is correct or not and where I should make changes to appropriate with mine..
-
The web service I call it AutoComplete.
-
The search text box I call it myTextBox.
-
The access database is in C:\Documents and Settings\user\My Documents\Visual Studio 2005\WebSites\AutoComplete\db1.mdb
-
The table which I choose from is recipes.
-
The column I should choose the word from is Name.
The code is:
Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System
Imports System.IO
Imports System.Collections
Imports System.Collections.Generic
Imports System.Threading
Imports System.Xml.Serialization
Imports System.Data
Imports System.Data.SqlClient
Public Class WebService
Inherits System.Web.Services.WebService
_
Public Function GetWordList(ByVal prefixText As String, _
ByVal count As Integer) As String()
If autoCompleteWordList Is Nothing Then
Dim MyArrayList As ArrayList = New ArrayList
Dim connectionString As String = System.Configuration.ConfigurationManager.ConnectionStrings("medsidConnectionString").ConnectionString.ToString
Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)
Dim queryString As String = "SELECT Distinct([Pacientes].Nombre) FROM [Pacientes] WHERE '" & prefixText & "%' ORDER BY Nombre"
Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
dbConnection.Open()
Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
While dataReader.Read()
MyArrayList.Add(dataReader("Nombre"))
End While
Dim temp As IDataReader() = CType(MyArrayList.ToArray(GetType(IDataReader)), IDataReader())
temp = MyArrayList.ToArray()
Array.Sort(temp, New CaseInsensitiveComparer())
autoCompleteWordList = temp
End If
Dim index As Integer = Array.BinarySearch(autoCompleteWordList, _
prefixText, New CaseInsensitiveComparer())
If index < 0 Then
index = Not index
End If
Dim matchingCount As Integer
For matchingCount = 0 To count - 1
If Not (matchingCount + index) < autoCompleteWordList.Length Then
Exit For
End If
If Not autoCompleteWordList((index _
+ matchingCount)).StartsWith(prefixText, _
StringComparison.CurrentCultureIgnoreCase) Then
Exit For
End If
Next matchingCount
Dim returnValue(matchingCount - 1) As String
If matchingCount > 0 Then
Array.Copy(autoCompleteWordList, index, returnValue, 0, _
matchingCount)
End If
Return returnValue
End Function
End Class
Any advise would be helpful, thanks