AspdotnetCodes.com
Search
Articles
Books
Resources
Asp.Net News
Downloads
Free Tech Magazines
Asp.Net Web Hosting
Archives
Softwares
Newsletter
Suggest Us
Link to Us
Feeds Subscription
Articles
Questions & Answers
Tips & Tricks
 AutoComplete extender
Posted by HSS on  5/29/2008 11:49:36 AM Category: AJAX
Add to my favorites
Email to friend
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

Post Your Reply Post New Question

 Viewer's Reply
Check your Query to fetch Data
Answered By Moderator1 on 5/31/2008 10:48:23 PM
Hi,

At first glance, I found your query to fetch data missed the column name and LIKE operator after WHERE clause.

Please correct that and i hope it will work fine.
 
Post Your Reply If you still have problem, post it again under this reply for better response.

 
Sponsored by
Building a Web Application Security Program
No Nonsense XML Web Development With PHP - Free 146 Page Preview!
Backup and Recovery Best Practices for Microsoft SQL Server 2005