AutoComplete from database..

Question

0.00/5 (No votes)

See more:

I m having problem when implementing autocompleteextender in visual studio 2008, where when i enter my any text in my textbox,it will automaticly capture the keywords and suggest words from database..no errors found,but it didnt works when i enter any keyword inside the textbox..
This is my aspx file :

XML

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
        <asp:ScriptManager ID="ScriptManager1" runat="server">
        <Services>
        <asp:ServiceReference Path="AutoComplete.asmx" />
        </Services>
        </asp:ScriptManager>
        <div>
            <asp:TextBox runat="server" ID="myTextBox" Width="300" autocomplete="off" />
            <ajaxToolkit:AutoCompleteExtender
                runat="server"
                ID="autoComplete1"
                TargetControlID="myTextBox"
                ServicePath="AutoComplete.asmx"
                ServiceMethod="GetCompletionList"
                MinimumPrefixLength="2"
                CompletionInterval="1000"
                EnableCaching="true"
                CompletionSetCount="12" />
    </div>
    </form>
</body>
</html>

This is my asmx.cs file:

MSIL

using System;
using System.Web;
using System.Collections;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

/// <summary>
/// Summary description for AutoComplete
/// </summary>

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.Web.Script.Services.ScriptService]

public class AutoComplete : System.Web.Services.WebService
{
    [WebMethod]
    public string[] GetCompletionList(string prefixText)
    {
        DataSet dtst = new DataSet();
        SqlConnection sqlCon = new SqlConnection(ConfigurationManager.AppSettings["%$ ConnectionStrings:C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL.1\MSSQL\DATA\DATABASE1.MDFConnectionString %"]);
        string strSql = "SELECT Company_Name FROM Table1 WHERE CompanyName LIKE '" + prefixText + "%' ";
        SqlCommand sqlComd = new SqlCommand(strSql, sqlCon);
        sqlCon.Open();
        SqlDataAdapter sqlAdpt = new SqlDataAdapter();
        sqlAdpt.SelectCommand = sqlComd;
        sqlAdpt.Fill(dtst);
        string[] cntName = new string[dtst.Tables[0].Rows.Count];
        int i = 0;
        try
        {
            foreach (DataRow rdr in dtst.Tables[0].Rows)
            {
                cntName.SetValue(rdr["CompanyName"].ToString(), i);
                i++;
            }
        }
        catch { }
        finally
        {
            sqlCon.Close();
        }
        return cntName;
    }
}

Should i modify any code part?
Can i use the autocompleteextender using sqldatasource to call database?
Any ideas will be much appreciated.

Reference:http://www.c-sharpcorner.com/UploadFile/munnamax/AutocompleteExtender08062007113854AM/AutocompleteExtender.aspx[^]

Posted 11-Aug-10 16:40pm

pwtc222

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

obst900 · Answer 1 · 2010-08-20T08:17:00

Hi there!

I have a running project that uses this extension quering data from a database that works just fine.
This is my aspx block:

XML

<asp:TextBox ID="edCliente" runat="server" MaxLength="50" Width="250px" ValidationGroup="cabecalho"></asp:TextBox>
  <cc1:AutoCompleteExtender
       ID="AutoCompleteClientes"           runat="server"
       Enabled="True"                      TargetControlID="edCliente"
       ServiceMethod="PesquisaClientesProposta"    ServicePath="~/AutoCompleteClientes.asmx"
       CompletionSetCount="12"             MinimumPrefixLength="1"
       CompletionInterval="0">
  </cc1:AutoCompleteExtender>

And this is the asmx code:

C++

[WebMethod]
    public string[] PesquisaClientesProposta(string prefixText)
    {
        
        SqlConnection conn = new SqlConnection();
        conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["prodMIS"].ToString();
        conn.Open();

        StringBuilder ComandoSQL = new StringBuilder();
        ComandoSQL.AppendLine("SELECT DISTINCT NOME_CLIENTE NOME FROM MIS..CONTRACT CTR ");
        ComandoSQL.AppendLine("WHERE CTR.NOME_CLIENTE LIKE @NOME ");
        ComandoSQL.AppendLine("ORDER BY NOME_CLIENTE ");

        SqlParameter ParamNome = new SqlParameter("NOME", System.Data.SqlDbType.VarChar, 35, "NOME");
        ParamNome.Value = String.Concat(prefixText, "%");

        SqlCommand CMD = new SqlCommand(ComandoSQL.ToString(), conn);
        CMD.Parameters.Add(ParamNome);
        SqlDataAdapter SQLda = new SqlDataAdapter(CMD);
        SqlCommandBuilder CMDbuilder = new SqlCommandBuilder(SQLda);

        SqlDataReader SQLReader;
        SQLReader = CMD.ExecuteReader();


        List<string> listClientes = new List<string>();
        //listClientes.Add("-TODOS-");
        while (SQLReader.Read())
        {
            listClientes.Add(SQLReader["NOME"].ToString());
        }

        conn.Close();
        return listClientes.ToArray();

    }

I don't know if that helps you, but it's working fine for me.

Regards,
Obst