Cody Hicks

Cody Hicks

  • NA
  • 1
  • 1.2k

Need to get data from SQL Ce for an Ajax AutoCompleteExten..

Jun 21 2013 9:24 PM

I have an Ajax AutoCompleteExtender I need to get data for and I cant seem to get my query to work in C#..throws exception. Every tutorial I've seen deals with normal SQL databases but I have a CE I want to get the data from. I took code from one of the tutorials and simply changed every object from Sqlxx to SqlCexx... I am apperantly able to connect to the database using my Connection String in the Web.config file but I get an exception during debug.
I've been told I can modify the prefixText to include a wildcard but I'm not sure how to go about this..

Here is my C# code:
__________________________________
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;
    }


}
__________________________________________________________________________

Another note, I've been told that I can modify the code by adding a line similar to the following but I'm not sure how:

param.Value = "%" + parameter.Value + "%";


Any Ideas?