Post
Ask Question

Using Stored Procedure in AutoComplete Extender TextBox in ASP.Net C#

To use a Stored Procedure in an AutoComplete extender TextBox in ASP.Net C# use the following procedure.

  1. Open SQL Server Management Studio then

    select the database where you want to create a table and Stored Procedure (in my case it is the library database and the table name is available book).

  2. Create Table in SQL Server

    1. CREATE TABLE [dbo].[AvailableBook](  
    2. [Title] [varchar](500) NULL,  
    3. [Authorname] [varchar](100) NULL,  
    4. [Edition] [varchar](max) NULL,  
    5. [Year] [varchar](max) NULL,  
    6. [Volume] [varchar](max) NULL,  
    7. [BookNo] [varchar](max) NULL,  
    8. [ShelfNO] [varchar](50) NULL,  
    9. [enrollment] [varchar](50) NULL,  
    10. [Issuedate] [varchar](max) NULL,  
    11. [Returndate] [varchar](50) NULL,  
    12. [Status] [varchar](50) NULL,  
    13. [Bookid] [varchar](max) NULL,  
    14. [Type] [varchar](max) NULL  
    15. )  
    16. Insert values into the availablebook table like this:  
    17. insert into availablebook values('c#','apress','2011','2010','3','1','2','NULL','NULL','NULL','Available','1','NULL')  

  3. Create a Stored Procedure like this:
    1. Create proc [dbo].[searchbook]  
    2. @book varchar(90)='%'  
    3. as  
    4. begin  
    5. select Title,Authorname from availablebook where title like @book or authorname like @book  
    6. end  

    After that open Visual Studio then open your project or create a new project.
     
    Drag and drop a TextBox to your webform, in design view you are getting an arrow like addextender, see the following figure:

    AutoComplete-extender-textbox.jpg

    Select AutoCompleteExtender (don't forget to add a toolkit scriptmanager and reference it in your page header or in web.config).
     
    See example :(aspx page source view)

    1. <%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>  
    2. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
    3. <html xmlns="http://www.w3.org/1999/xhtml">  
    4. <head>  
    5.     <meta name="description" content="" />  
    6.     <meta name="keywords" content="" />  
    7.     <title>Big Business 2.0 by FCT</title>  
    8.     <meta http-equiv="content-type" content="text/html; charset=utf-8" />  
    9.     <link rel="stylesheet" type="text/css" href="Styles/style.css" />  
    10.     <script type="text/javascript" src="Scripts/jquery-1.7.1.min.js"></script>  
    11.     <script type="text/javascript" src="Scripts/jquery.dropotron-1.0.js"></script>  
    12.     <script type="text/javascript" src="Scripts/jquery.slidertron-1.1.js"></script>  
    13.     <style type="text/css">  
    14.         .watermark  
    15.         {  
    16.             background-color: Gray;  
    17.         }  
    18.     </style>  
    19. </head>  
    20. <body>  
    21.     <form id="Form1" runat="server">  
    22.     <h1>Library</h1>  
    23.     <asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">  
    24.     </asp:ToolkitScriptManager>  
    25.     Search Book:  <asp:TextBox ID="TextBox2" AutoCompleteType="Search" AutoPostBack="true"  
    26.         runat="server" OnTextChanged="TextBox2_TextChanged"></asp:TextBox>  
    27.     <asp:AutoCompleteExtender ID="TextBox2_AutoCompleteExtender" runat="server" MinimumPrefixLength="1"  
    28.         CompletionInterval="5" Enabled="True" ServicePath="~/WebService.asmx" EnableCaching="true"  
    29.         CompletionSetCount="12" ServiceMethod="GetCompletionList" TargetControlID="TextBox2">  
    30.     </asp:AutoCompleteExtender>  
    31.     <asp:TextBoxWatermarkExtender ID="TextBox2_TextBoxWatermarkExtender" WatermarkCssClass="watermark"  
    32.         WatermarkText="search by title" runat="server" Enabled="True" TargetControlID="TextBox2">  
    33.     </asp:TextBoxWatermarkExtender>  
    34.     <asp:RoundedCornersExtender ID="TextBox2_RoundedCornersExtender" runat="server" Enabled="True"  
    35.         TargetControlID="TextBox2">  
    36.     </asp:RoundedCornersExtender>  
    37.     </form>  
    38. </body>  
    39. </html>

  4. Add a webservice from a new item and reference it in your project and write the following code (be careful while giving the webservice name; in my case it is WebService.asmx).
    1. [WebMethod]  
    2. Public List<string> GetCompletionList(string prefixText)  
    3. {  
    4.     List<string> result = new List<string>();  
    5.     con.Open();  
    6.     SqlCommand cmd = new SqlCommand("searchbook", con);  
    7.     cmd.Parameters.AddWithValue("@book", prefixText);  
    8.     SqlDataReader dr = cmd.ExecuteReader();  
    9.     while (dr.Read())  
    10.     {  
    11.         result.Add(dr["Title"].ToString());  
    12.         result.Add(dr["Authorname"].ToString());  
    13.     }  
    14.     return result;  
    15. }

Rebuild your project and run it; you'll get some suggestions when you type characters.


Similar Articles