AutoComplete With DataBase and AjaxControlToolkit
Introduction
This is a very simple code which makes an Auto Complete Combo with database. It is useful. First of all, you do not have to know about Ajax functions, just download the AJAX Control Toolkit on CodePlex and follow me, then enjoy. Besides when there are many rows, you can type part of the word in the textbox, then it can offer all of the words which are similar to it.
Background: What is Ajaxcontroltoolkit?
The ASP.NET AJAX Control Toolkit is an open-source project built on top of the Microsoft ASP.NET AJAX framework and contains more than 30 controls that enable you to easily create rich, interactive web pages. If you want to know more about it, visit this link.
Using the Code
At the first step, you must download AjaxControlToolkit from here for .NET 3.5 OR here for .NET 4.0.
You must go here and download ajaxcontroltoolkit, then copy ajaxcontroltoolkit and paste it to Bin folder. Right click on solution, choose Add Reference, in the browse tab, double click on the Bin folder, and double click on ajaxcontroltoolkit, then on the Build Menu > click Rebuild.
DataBase: New Query
CREATE TABLE [dbo].[tblCustomer](
[CompanyName] [nvarchar](500) NULL,
[ID] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
insert into dbo.tblCustomer(CompanyName) values('calemard')
insert into dbo.tblCustomer(CompanyName) values('dantherm')
insert into dbo.tblCustomer(CompanyName) values('dango dienenthal')
insert into dbo.tblCustomer(CompanyName) values('daewoo')
insert into dbo.tblCustomer(CompanyName) values('daim engineering')
Visual Studio 2008 - .NET 3.5: Create a Web site and name it AutoComplete. Create a Web Form and name it AutoComplete.aspx. In HTML view, write this code.
But there is a small difference between C# and VB in this section:
- This code at the bottom is for the C# coder.
- If you are a VB coder, please modify 2 sections in page tag.
- One: correct
language=VB - Two: correct
CodeFile="AutoComplete.aspx.vb"
- One: correct
<%@ Page Language="C#" AutoEventWireup="false"
CodeFile="AutoComplete.aspx.cs" Inherits="AutoComplete" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace=" AjaxControlToolkit"
TagPrefix="ajaxToolkit" %>
<!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>AutoComplete</title>
</head>
<body>
<form id="form1" runat="server">
<ajaxToolkit:ToolkitScriptManager ID="ScriptManager1" runat="server">
</ajaxToolkit:ToolkitScriptManager>
<ajaxToolkit:AutoCompleteExtender ID="autoComplete1" runat="server"
EnableCaching="true"
MinimumPrefixLength="1"
TargetControlID="myTextBox"
ServicePath="AutoComplete.asmx"
ServiceMethod="GetCompletionList"
CompletionInterval="1000"
CompletionSetCount="20">
</ajaxToolkit:AutoCompleteExtender>
<asp:TextBox ID="myTextBox" autocomplete ="off" runat="server"></asp:TextBox>
</form>
</body>
</html>
For VB
- Create Web Service: Solution > Right Click > Add New Item > Web Service >
- Name: AutoComplete.asmx
- Language: Visual Basic
- Go To > App_Code > AutoComplete.vb
' (c) Copyright Microsoft Corporation.
' This source is subject to the Microsoft Public License.
' See http://www.microsoft.com/opensource/licenses.mspx#Ms-PL.
' All other rights reserved.
Imports System
Imports System.Collections
Imports System.Linq
Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Xml.Linq
Imports System.Collections.Generic
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://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
Public Class AutoComplete
Inherits System.Web.Services.WebService
Dim cn As New SqlClient.SqlConnection()
Dim ds As New DataSet
Dim dt As New DataTable
<WebMethod()> _
Public Function GetCompletionList(ByVal prefixText As String, _
ByVal count As Integer) As String()
'ADO.Net
Dim strCn As String = "data source=.;_
Initial Catalog=MyDB;Integrated Security=True"
cn.ConnectionString = strCn
Dim cmd As New SqlClient.SqlCommand
cmd.Connection = cn
cmd.CommandType = CommandType.Text
cmd.CommandText = "select * from tblCustomer"
Try
cn.Open()
cmd.ExecuteNonQuery()
Dim da As New SqlDataAdapter(cmd)
da.Fill(ds)
Catch ex As Exception
Finally
cn.Close()
End Try
dt = ds.Tables(0)
'Compare String From Textbox(prefixText) AND String From DataBase(dbValues)
'Then return List of string(txtItems) as result
Dim txtItems As New List(Of String)
Dim dbValues As String
For Each row As DataRow In dt.Rows
dbValues = row("CompanyName").ToString()
'String From DataBase(dbValues)
dbValues = dbValues.ToLower()
'String From Textbox(prefixText)
prefixText = prefixText.ToLower()
'If String from DataBase is contained String from
'TextBox then add it to return ItemList
Dim b As Boolean = dbValues.Contains(prefixText)
If b = True Then
txtItems.Add(dbValues)
End If
Next
Return txtItems.ToArray()
End Function
End Class
For C#
- Web Service: Solution > Right Click > Add New Item > Web Service >
- Name: AutoComplete.asmx
- Language: C#
- Go To > App_Code > AutoComplete.cs
// (c) Copyright Microsoft Corporation.
// This source is subject to the Microsoft Public License.
// See http://www.microsoft.com/opensource/licenses.mspx#Ms-PL.
// All other rights reserved.
using System;
using System.Collections;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Linq;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
///<summary>
/// Summary description for AutoComplete
///</summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script,
// using ASP.NET AJAX, uncomment the following line.
[System.Web.Script.Services.ScriptService]
public class AutoComplete : System.Web.Services.WebService {
public AutoComplete () {
//Uncomment the following line if using designed components
//InitializeComponent();
}
[WebMethod]
public string[] GetCompletionList(string prefixText, int count)
{
//ADO.Net
SqlConnection cn =new SqlConnection();
DataSet ds = new DataSet();
DataTable dt = new DataTable();
String strCn = "data source=.;Initial Catalog=MyDB;Integrated Security=True";
cn.ConnectionString = strCn;
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from tblCustomer";
try
{
cn.Open();
cmd.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
}
catch
{
}
finally
{
cn.Close();
}
dt = ds.Tables[0];
//Compare String From Textbox(prefixText) AND String From DataBase(dbValues)
//Then return List of string(txtItems) as result
List<string> txtItems =new List<string>();
String dbValues;
foreach (DataRow row in dt.Rows)
{
dbValues = row["CompanyName"].ToString();
//String From DataBase(dbValues)
dbValues = dbValues.ToLower();
//String From Textbox(prefixText)
prefixText = prefixText.ToLower();
//If String from DataBase is contained String from TextBox
//then add it to return ItemList
Boolean b= dbValues.Contains(prefixText);
if (b == true)
{
txtItems.Add(dbValues);
}
}
return txtItems.ToArray();
}
}
Summary
GetCompletionList is a function that catches 2 arguments, prefixText which is string and count which is int.
When you type some characters, they are saved in prefixText and number of your characters are saved in count. And at the end, the function returns a list of string (they are similar to your characters) which has been obtained as follows...
I have written some code in ADO.NET section. They are all of rows in the field. Later we filter them and select some of them which are similar to your characters that have been typed in text box. I create txtItems in List of string data type, we can save words that we want. Then in a foreach loop, I compare strings From Textbox(prefixText) AND strings from DataBase(dbValues). First of all, I converted them into tolower, and then if strings from DataBase(dbValues) contained strings from Textbox(prefixText), I added those values to my result value (txtItems), finally I return txtItems.
Try Step by Step
- Go here for .NET 3.5 OR here for .NET 4.0 and download the AjaxControlToolkit file.
- Copy the folder "AjaxControlToolkit.Dll" and all dependers, there are 18 objects, to your web site Bin folder (C:\AutoComplete\Bin).
- Right click on solution, choose refresh, then right click again and click add reference, then in the browse tab double click on the Bin folder, and double click on ajaxcontroltoolkit, on the Build Menu > click Rebuild.
- Create Data Base and tables like above, and add some rows which have common words.
- Create Web Form and name it: "AutoComplete.aspx". In the HTML view, write some code as shown above. (This should be exactly like my code because this section is case sensitive).
- Create a webservice:
Solution > Right Click > Add New Item > Web Service > Name: AutoComplete.asmx Language: C# or VB
Go To > App_Code > AutoComplete.cs. - If you are a VB coder use the VB sample, otherwise use the C# sample.
- Run the program and in the Text Box, write a word that contains 2 characters or more such as da, and you will see a list of words that are similar to your characters.
Feedback
Feel free to leave any feedback on this article; it is a pleasure to see your feedback about this code.
History
- 24th May, 2011: Initial post
Post Comment
cheap backlinks[37.233.27.*]2014/7/19 1:32:17#1ryjInN Thanks-a-mundo for the blog article.Much thanks again. Really Great.