Hello,
I have an Ajax AutoCompleteExtender and I need to get it's data from my SQL Ce database. I have the C# code to do this but I have a feeling that the query is missing something or coded incorrectly.
Could someone please help me refactor it?
C#:
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Configuration;
using System;
using System.Data.SqlServerCe;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
[System.Web.Script.Services.ScriptMethod()]
[System.Web.Services.WebMethod]
public static List<string> GetCompletionList(string prefixText)
{
SqlCeConnection con = new SqlCeConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
con.Open();
SqlCeCommand cmd = new SqlCeCommand("SELECT Name FROM Current WHERE Name LIKE @Name+'%'", con);
cmd.Parameters.AddWithValue("@Name", prefixText);
SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
List<string> Names = new List<string>();
for (int i = 0; i < dt.Rows.Count; i++)
{
Names.Add(dt.Rows[i][1].ToString());
}
return Names;
}
}
Stack trace:
at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr)
at System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan()
at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options)
at System.Data.SqlServerCe.SqlCeCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.SqlServerCe.SqlCeCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at _Default.GetCompletionList(String prefixText) in c:\Users\CRH\Desktop\Dev Projects\HelpDesk\Default.aspx.cs:line 26
How can I put this in?:
param.Value = "%" + parameter.Value + "%";
Thanks in advance,
-Cody