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
<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
<%@ 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>
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
Comments