Total Pageviews

Thursday, August 16, 2012

Autocomplete Text Box with dropdown from MySQL database webservice vb asp.net

Autocomplete Text Box with dropdown from database webservice vb asp.net

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:="http://vinodkotiya.com/")> _
<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()
            connection.Open()

            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 & "%'"
            Try
                sqlComm = New MySqlCommand(myquery, connection)
                sqlReader = sqlComm.ExecuteReader()
                dt.Load(sqlReader)
                Dim i = dt.Rows.Count

                sqlReader.Close()
                sqlComm.Dispose()
                If i = 0 Then
                    connection.Close()
                    items = Nothing

                End If

                While j < i

                    items.Add(dt.Rows(j).Item(0).ToString())

                    j = j + 1
                End While

                connection.Close()
                Return items.ToArray()


            Catch e1 As Exception
                'lblDebug.text = e.Message
                connection.Close()
                items.Add(e1.Message)
                Return items.ToArray()
            End Try
            'connection.Close()
        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" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <style type="text/css">
        /*AutoComplete flyout */
        .autocomplete_completionListElement
        {
            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 */
        .autocomplete_highlightedListItem
        {
            background-color: #ffff99;
            color: black;
            padding: 1px;
        }
        /* AutoComplete item */
        .autocomplete_listItem
        {
            background-color: window;
            color: windowtext;
            padding: 1px;
        }
</style>

</head>
<body>
   
    <form id="form1" runat="server">
     <asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
    </asp:ToolkitScriptManager>
    
    <div>
    <asp:updatepanel ID="Updatepanel1" runat="server">
    <ContentTemplate>

      <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:Button>
<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"> 
</asp:AutoCompleteExtender>
     </ContentTemplate> </asp:updatepanel>
    </div>
    </form>
</body>
</html>

- Vinod Kotiya



No comments: