Asp.Net, Ajax, MS SQL Server: Ajax AutoComplete Extender

This sample demonstrate, how you can use AutoCompleteExtender control from ASP.NET AJAX Control Toolkit to fetch more than one column & after Selecting a particular value inside ACE, it displays other columns data Respectively.
we need four things, one would be our
sample contact page, a webservice, & two class files CodeManager.vb & Hotel.vb.

--------------Scripting here
Default.aspx file
Here u need Drag & Drop a Script Manager


<asp:TextBox ID="TxtCode" runat="server" MaxLength="10"
CssClass="StyleTextForm" Width="123px" ></asp:TextBox>
<cc1:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server" TargetControlID="TxtCode"
ServicePath="~/WebService.asmx"
ServiceMethod="GetName" MinimumPrefixLength="1"
OnClientItemSelected="OnContactSelected"
CompletionListCssClass="StyleTextForm" >
</cc1:AutoCompleteExtender>


Javascript
function OnContactSelected(source, eventArgs) {
            var results = eval('(' + eventArgs.get_value() + ')');
//            $get('TxtCode').value = results.Code;
//            $get('LblName').value = results.Name;
            $get('ctl00_ContentPlaceHolder1_TxtCode').value = results.Code;
            $get('ctl00_ContentPlaceHolder1_TxtName').value = results.Name;
}

since i have used it in my MasterPage and Contentpage i have to use
$get('ctl00_ContentPlaceHolder1_TxtName').value
else we can directly use it
$get('TxtCode').value
-----------------------------------

Here is our webservice code.

Code for Webservice.vb
-------------


Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data.SqlClient
Imports System.Data
Imports System.Collections
Imports System.Web.Script.Services

<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<System.Web.Script.Services.ScriptService()> _
Public Class WebService
Inherits System.Web.Services.WebService

Dim Strconn As String = ConfigurationManager.ConnectionStrings("ConnStr").ConnectionString

<WebMethod(), System.Web.Script.Services.ScriptMethod()>_
Public Function GetName(ByVal prefixText As String, ByVal count As Integer) As String()
Dim items As New List(Of String)()
Dim Serializer As New JavaScriptSerializer
Dim manager As New CodeManager
Dim Codes As List(Of Hotel) = manager.GetCodes(prefixText, count)
For Each c As Hotel In Codes
items.Add(AutoCompleteExtender.CreateAutoCompleteItem(c.Code, Serializer.Serialize(c)))
Next
Return items.ToArray()
End Function

End Class

Code for class file CodeManager.vb
-------------------------------------


Imports System.Data
Imports System.Data.SqlClient
'Imports System.Collections.Generic

Public Class CodeManager
    Dim Strconn As String = ConfigurationManager.ConnectionStrings("ConnStr").ConnectionString
    Protected dsHotels As DataSet

    Public Sub New()
        dsHotels = New DataSet
    End Sub

    Public Function GetCodes(ByVal prefix As String, ByVal count As Integer) As List(Of Hotel)
        'Public Function GetCodes(ByVal id As Integer, ByVal prefix As String, ByVal count As Integer) As List(Of Hotel)
        Dim items As New List(Of Hotel)()
        Dim cnn As SqlConnection = New SqlConnection(Strconn)
        Dim Sqlstr = "SELECT TOP({0}) CODE, [NAME] FROM HOTELMAS WHERE (CODE LIKE '{1}%')"
        Dim adapter As New SqlDataAdapter(String.Format(Sqlstr, count, prefix), cnn)
        adapter.Fill(dsHotels, "Hotels")
        For Each row As DataRow In dsHotels.Tables("Hotels").Rows
            Dim Item As New Hotel
            Item.Code = DirectCast(row("CODE").ToString.Trim, String)
            Item.Name = DirectCast(row("NAME").ToString.Trim, String)
            items.Add(Item)
        Next
        Return items
    End Function
End Class

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

code for Hotel.vb Class
--------------------------


Public Class Hotel
    Private _Code As String
    Public Property Code() As String
        Get
            Return _Code
        End Get
        Set(ByVal value As String)
            _Code = value
        End Set
    End Property

    Private _Name As String
    Public Property Name() As String
        Get
            Return _Name
        End Get
        Set(ByVal value As String)
            _Name = value
        End Set
    End Property
End Class

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

Code in C#

code for WebService.cs
------------------


 using System.Web;
 using System.Collections;
 using System.Collections.Generic;
 using System.Web.Script.Serialization;
 using System.Web.Services;
 using System.Web.Services.Protocols;
 using AjaxControlToolkit;

 [WebService(Namespace = "http://tempuri.org/")]
 [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
 [System.Web.Script.Services.ScriptService()]
 public class WebService : System.Web.Services.WebService
 {
   
     [WebMethod(), System.Web.Script.Services.ScriptMethod()]
     public string[] GetName(string prefixText, int count)
     {
         List items = new List();
         JavaScriptSerializer Serializer = new JavaScriptSerializer();
         CodeManager manager = new CodeManager();
         List Codes = manager.GetCodes(prefixText, count);
         foreach (Hotel c in Codes) {
             items.Add(AutoCompleteExtender.CreateAutoCompleteItem(c.Code, Serializer.Serialize(c)));
         }
         return items.ToArray();
     }
}

--------------------
Coder for class file CodeManager.cs
-----------------------


 using System.Data;
 using System.Data.SqlClient;
 //Imports System.Collections.Generic

 public class CodeManager
 {
     string Strconn = ConfigurationManager.ConnectionStrings("ConnStr").ConnectionString;
     protected DataSet dsHotels;
   
     public CodeManager()
     {
         dsHotels = new DataSet();
     }
   
     public List GetCodes(string prefix, int count)
     {
         //Public Function GetCodes(ByVal id As Integer, ByVal prefix As String, ByVal count As Integer) As List(Of Hotel)
         List items = new List();
         SqlConnection cnn = new SqlConnection(Strconn);
         var Sqlstr = "SELECT TOP({0}) CODE, [NAME] FROM HOTELMAS WHERE (CODE LIKE '{1}%')";
         SqlDataAdapter adapter = new SqlDataAdapter(string.Format(Sqlstr, count, prefix), cnn);
         adapter.Fill(dsHotels, "Hotels");
         foreach (DataRow row in dsHotels.Tables("Hotels").Rows) {
             Hotel Item = new Hotel();
             Item.Code = (string)row("CODE").ToString.Trim;
             Item.Name = (string)row("NAME").ToString.Trim;
             items.Add(Item);
         }
         return items;
     }
 }

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

code for Hotel.cs Class
--------------------------


 using System.Data;
 using System.Data.SqlClient;
 using System.Web.UI.HtmlControls;

public class Hotel
 {
     private string _Code;
     public string Code {
         get { return _Code; }
         set { _Code = value; }
     }

     private string _Name;
     public string Name {
         get { return _Name; }
         set { _Name = value; }
     }
 }