sam

sam

  • NA
  • 202
  • 0

Autocomplete from database

Aug 15 2010 10:35 PM
Hi..
I implement an autocomplete texttbox using following code and it works fine :

[code]
<pre lang="xml">&lt;%@ Page Language=&quot;C#&quot; AutoEventWireup=&quot;true&quot; CodeBehind=&quot;Default.aspx.cs&quot; Inherits=&quot;AutoComplete2._Default&quot; %&gt;
&lt;%@ Register Assembly=&quot;AjaxControlToolkit&quot; Namespace=&quot;AjaxControlToolkit&quot; TagPrefix=&quot;asp&quot; %&gt;
&lt;!DOCTYPE html PUBLIC &quot;-//W3C//DTD XHTML 1.0 Transitional//EN&quot; &quot;http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd&quot;&gt;
&lt;script runat=&quot;server&quot;&gt;
[System.Web.Services.WebMethod]
[System.Web.Script.Services.ScriptMethod]
public static string[] GetCompletionList(string prefixText, int count)
{
    return &quot;this is sample text&quot;.Split();
}
&lt;/script&gt;
&lt;html xmlns=&quot;http://www.w3.org/1999/xhtml&quot; &gt;
&lt;head runat=&quot;server&quot;&gt;
    &lt;title&gt;Untitled Page&lt;/title&gt;
&lt;/head&gt;
&lt;body&gt;
    &lt;form id=&quot;form1&quot; runat=&quot;server&quot;&gt;
    &lt;div&gt;
        &lt;asp:ToolkitScriptManager ID=&quot;ToolkitScriptManager1&quot; runat=&quot;server&quot;&gt;
        &lt;/asp:ToolkitScriptManager&gt;
        &lt;asp:TextBox ID=&quot;TextBox1&quot; runat=&quot;server&quot; &gt;&lt;/asp:TextBox&gt;
        &lt;asp:AutoCompleteExtender ID=&quot;AutoCompleteExtender1&quot; runat=&quot;server&quot;
            ServiceMethod=&quot;GetCompletionList&quot;
            CompletionInterval=&quot;500&quot;
            ServicePath=&quot;&quot; TargetControlID=&quot;TextBox1&quot;
            EnableCaching=&quot;true&quot;
            CompletionSetCount=&quot;20&quot;
            DelimiterCharacters=&quot;;, :&quot;
            UseContextKey=&quot;True&quot;&gt;
        &lt;/asp:AutoCompleteExtender&gt;
        &lt;br /&gt;
    &lt;/div&gt;
    &lt;/form&gt;
&lt;/body&gt;
&lt;/html&gt;</pre>

[/code]


I want the autocomplete to read from database. And i tried used following code in above script,but it doesn't work and shows some error
in "SqlConnection oConn = new SqlConnection(connect);" part:

[code]
<script>

public string[] GetCompanyName(string prefixText, int count)
        {

            string sql = "Select * from Table1 Where Company_Name like '" + prefixText + "%" + "'";
            string connect = ConfigurationManager.AppSettings["Conn"];
            SqlConnection oConn = new SqlConnection(connect);
            SqlDataAdapter da = new SqlDataAdapter(sql, oConn);
            da.SelectCommand.Parameters.Add(@prefixText, SqlDbType.NVarChar, 50).Value = prefixText + "%";
            DataTable dt = new DataTable();
            da.Fill(dt);
            string[] items = new string[dt.Rows.Count];
            int i = 0;
            foreach (DataRow dr in dt.Rows)
            {
                items.SetValue(dr["Company_Name"].ToString(), i);
                i++;
            }
            return items;
        }
</script>
[/code]

could anyone tell me where's the wrong?Is my code above correct?

Answers (2)