Autocomplete Text Box with dropdown from MySQL database webservice vb

Autocomplete Text Box with dropdown from database webservice vb

Ajax autocomplete extender with a textbox  using a MySQL database

1. Write webservice.vb in App_code

              Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports MySql.Data
Imports MySql.Data.MySqlClient
Imports System.Data
Imports System.Data.SqlClient

' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.

<System.Web.Script.Services.ScriptService()> _
<WebService(Namespace:="")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class WebService
    Inherits System.Web.Services.WebService

    <WebMethod()> _
    Public Function HelloWorld() As String
        Return "Hello World"
    End Function
    <WebMethod()> _
    Public Function GetCompletionList(prefixText As String) As String()
        Dim items As New List(Of String)(50)
        ''this function will return single value from table by concatenating rows with hash
        Using connection As New MySqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("vinConn").ConnectionString)
            ' connection.Close()

            Dim sqlComm As MySqlCommand
            Dim sqlReader As MySqlDataReader
            Dim dt As New DataTable()
            Dim j As Integer = 0
            Dim myquery = "select projectname from Projects_ntpc where projectname like '%" & prefixText & "%'"
                sqlComm = New MySqlCommand(myquery, connection)
                sqlReader = sqlComm.ExecuteReader()
                Dim i = dt.Rows.Count

                If i = 0 Then
                    items = Nothing

                End If

                While j < i


                    j = j + 1
                End While

                Return items.ToArray()

            Catch e1 As Exception
                'lblDebug.text = e.Message
                Return items.ToArray()
            End Try
        End Using
    End Function
End Class

2. Refer webservice in webservice.asmx file

<%@ WebService Language="VB" CodeBehind="~/App_Code/WebService.vb" Class="WebService" %>

3. aspx code

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="temp.aspx.vb" Inherits="temp" %>

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">

<html xmlns="">
<head runat="server">
    <style type="text/css">
        /*AutoComplete flyout */
            margin: 0px !important;
            background-color: inherit;
            color: windowtext;
            border: buttonshadow;
            border-width: 1px;
            border-style: solid;
            cursor: 'default';
            overflow: auto;
            height: 200px;
            text-align: left;
            left: 0px;
            list-style-type: none;
        /* AutoComplete highlighted item */
            background-color: #ffff99;
            color: black;
            padding: 1px;
        /* AutoComplete item */
            background-color: window;
            color: windowtext;
            padding: 1px;

    <form id="form1" runat="server">
     <asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
    <asp:updatepanel ID="Updatepanel1" runat="server">

      <asp:Button ID="btn_Search" runat="server" Text="Button" />
    <asp:TextBox ID="txtQuery" runat="server" Width="300px" Height="20px" autocomplete="off" onkeydown="if(window.event.keyCode == 13){document.getElementById('btn_Search').click();};"></asp:TextBox>
    <asp:Button ID="btnSearch" OnClick="btnSearch_Click" runat="server" Text="Search" CausesValidation="False">
<asp:AutoCompleteExtender ID="autoComplete1" runat="server" DelimiterCharacters=";,:"      CompletionListHighlightedItemCssClass="autocomplete_highlightedListItem" CompletionListItemCssClass="autocomplete_listItem"    
    CompletionListCssClass="autocomplete_completionListElement" CompletionSetCount="20" EnableCaching="true" CompletionInterval="500" MinimumPrefixLength="1" ServiceMethod="GetCompletionList"        ServicePath="WebService.asmx" TargetControlID="txtQuery" BehaviorID="AutoCompleteEx"> 
     </ContentTemplate> </asp:updatepanel>

- Vinod Kotiya


Most Viewed Post

solution for facebook waiting

Connect Sony XPLOD MEX-BT2500 Bluetooth CD Receiver with ur mobile

Crystal Report : Show data horizontally (Left to right) i.e. columns as rows