[/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] 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; } [/code] could anyone tell me where's the wrong?Is my code above correct?", "answerCount": 2, "datePublished": "2010-08-15T22:35+00:00", "author": { "@type": "Person", "name": "sam ", "url": "https://www.csharp.com/members/sam413" } , "suggestedAnswer": [ { "@type": "Answer", "text": "That means my complete code looks like this : [System.Web.Services.WebMethod] [System.Web.Script.Services.ScriptMethod] public static string[] GetCompletionList(string prefixText, int count) { string conString = ConfigurationManager.ConnectionStrings[\"ConnectionString\"].ConnectionString; SqlConnection connection = new SqlConnection(conString); connection.Open(); SqlParameter prm; string sql = \"Select Company_Name FROM Table1 WHERE Company_Name LIKE @prefixText\"; SqlDataAdapter cmd = new SqlDataAdapter(sql, connection); prm = new SqlParameter(\"@prefixText\", SqlDbType.VarChar, 50); prm.Value = prefixText + \"%\"; cmd.SelectCommand.Parameters.Add(prm); DataTable dt = new DataTable(); cmd.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++; } connection.Close(); return items; } Untitled Page ServiceMethod=\"GetCompletionList\" CompletionInterval=\"500\" ServicePath=\"\" TargetControlID=\"TextBox1\" EnableCaching=\"true\" CompletionSetCount=\"20\" DelimiterCharacters=\";, :\" UseContextKey=\"True\"> ", "upvoteCount": 0, "url": "https://www.csharp.com/forums/autocomplete-from-database2", "datePublished": "2010-08-16T00:13+00:00", "author": { "@type": "Person", "name": "sam ", "url": "https://www.csharp.com/members/sam413" } }, { "@type": "Answer", "text": "I have solved the problem. I added following code on aspx file: %@ Import Namespace = \" System.Data\" % > %@ Import Namespace = \" System.Data.SqlClient\" % > And this code on cs file: using System.Data.SqlClient; it works fine,it reads and suggests from database.", "upvoteCount": 0, "url": "https://www.csharp.com/forums/autocomplete-from-database2", "datePublished": "2010-08-16T00:11+00:00", "author": { "@type": "Person", "name": "sam ", "url": "https://www.csharp.com/members/sam413" } } ] } }
2
Answers

Autocomplete from database

sam

sam

14y
2.5k
1
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)
Next Recommended Forum