Click here to Skip to main content
15,664,506 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
I am trying to search data from database but enable to get the result.Please could any body help me.
thanx in advance

<asp:TextBox ID="txtlicense" runat="server"></asp:TextBox>
 <ajax:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server" TargetControlID="txtlicense"
 MinimumPrefixLength="1" EnableCaching="true" ServicePath="WebService.asmx"
  ServiceMethod="GetLicense" CompletionInterval="100"
  CompletionSetCount="10" >

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for WebService
/// </summary>
[WebService(Namespace = "")]
[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 WebService : System.Web.Services.WebService {

    public WebService () {

        //Uncomment the following line if using designed components

    public List<string> GetLicense(string prefixText)
     SqlConnection con = new SqlConnection("Data Source=.;Integrated Security=True;Initial Catalog=LicenseManagementSystem");
        SqlCommand cmd = new SqlCommand("select LicenseNumber from LicenseDetails where LicenseNumber like @license+'%'", con);
        cmd.Parameters.AddWithValue("@license", prefixText);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        List<string> license = new List<string>();
        for (int i = 0; i < dt.Rows.Count; i++)
        return license;
Updated 28-Aug-18 20:10pm

Share this answer
Md.Ashraf Ali 24-Dec-13 5:37am    
Actually its working fine in another .aspx page but the same thing i want to use then nothing happens?
/*Code for autocomlete extender to fill textbox*/

/*Web method for getting our result from database*/
public string[] GetCompletionList(string prefixText, int count)

SqlConnection cn = new SqlConnection();
DataSet ds = new DataSet();
DataTable dt = new DataTable();

String strCn = "Data Source=db;Initial Catalog=dbb;User ID=sa;Password=dd@123";
cn.ConnectionString = strCn;
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select Name from tbl_EmpMaster WHERE (Name LIKE '%' + @myParameter + '%') AND (IsActive=1)";
cmd.Parameters.AddWithValue("@myParameter", prefixText);
SqlDataAdapter da = new SqlDataAdapter(cmd);
catch (Exception ex)

return 0;
dt = ds.Tables[0];

//Then return List of string(txtItems) as result
List<string> txtItems = new List<string>();
String dbValues;

foreach (DataRow row in dt.Rows)
//String From DataBase(dbValues)
dbValues = row["Name"].ToString();
//dbValues = dbValues();

return txtItems.ToArray();


/*Aspx page code*/

Search Name:
asp:TextBox ID="txtSearchname" runat="server" class="form-control input-sm m-bot15"
OnTextChanged="txtSearchname_TextChanged" AutoPostBack="true" Font-Bold="true"

asp:AutoCompleteExtender runat="server" BehaviorID="AutoCompleteEx1" ID="autoComplete1"
TargetControlID="txtSearchname" ServicePath="../AutoCompleteText.asmx"
ServiceMethod="GetCompletionList" MinimumPrefixLength="1" CompletionInterval="100"
EnableCaching="true" CompletionSetCount="10" CompletionListCssClass="autocomplete_completionListElement "
CompletionListItemCssClass="autocomplete_listItem" CompletionListHighlightedItemCssClass="autocomplete_highlightedListItem"

Share this answer
CHill60 30-Aug-18 3:42am    
Reasons for my downvote:

1. This solution puts the code at risk of SQL Injection[^] despite your comment "//-----I Defined a parameter instead of passing value directly to prevent sql injection--------//"
That line of code should read
cmd.CommandText = "select Name from tbl_EmpMaster WHERE Name LIKE '%@myParameter%') AND (IsActive=1)";

2. You are also blindly throwing away any information about any errors encountered by using
That is very bad coding practice
Richard Deeming 30-Aug-18 9:59am    
"... WHERE Name LIKE '%' + @myParameter + '%' ..."

The code from the solution is (almost) correct. The only problem is the mis-matched parentheses.

"... WHERE Name LIKE '%@myParameter%' ..."

Your version will search for records containing the literal string "@myParameter", not the value of the parameter.
CHill60 30-Aug-18 10:36am    
Even with
cmd.Parameters.AddWithValue("@myParameter", prefixText);
? I've never used + before when adding parameters (it's been a while though)
Richard Deeming 30-Aug-18 10:43am    
Yes. The code in the solution adds '%' to the start and end of the parameter, but is not vulnerable to SQLi. The code in your comment doesn't use the parameter at all. :)

The alternative would be to add the prefix and suffix to the parameter value in C#:
cmd.CommandText = "select Name from tbl_EmpMaster WHERE (Name LIKE @myParameter) AND (IsActive=1)";
cmd.Parameters.AddWithValue("@myParameter", "%" + prefixText + "%");
CHill60 3-Sep-18 4:33am    
Thanks for the info by the way - I forgot to come back and say so. I definitely need to brush up on my .NET stuff.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900