How to Search in SQL Server Compact Database in windows form application using C#?

Question

My Code For Searching Data In SQL Server Compact Database is not working please review my code. any help will be greatly appreciated.

    #region btnSearch_Click
    private void btnSearch_Click(object sender, EventArgs e)
    {
        SqlCeConnection con = new SqlCeConnection("Data Source="
            + System.IO.Path.Combine(Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().Location), "Database.sdf"));
        sda = new SqlCeDataAdapter();
        if (con.State == ConnectionState.Closed)
        {
            con.Open();
        }
    string sql = "select Name from tblCustomers ";
    if (tbSearch.Text.Length > 0)
    {
    sql += "where Name like " + tbSearch.Text + " % ";
    }

    try
    {
    SqlCeCommand cmd = new SqlCeCommand(sql, con);
    cmd.CommandType = CommandType.Text;

    // if you don’t set the result set to
    // scrollable HasRows does not work
    SqlCeResultSet rs = cmd.ExecuteResultSet(
    ResultSetOptions.Scrollable);

    if (rs.HasRows)
    {

    int Name = rs.GetOrdinal("Name");


    // Hold the output
    StringBuilder output = new StringBuilder();

    // Read the first record and get it’s data
    rs.ReadFirst();
    output.AppendLine(rs.GetString(Name)
    + " " + rs.GetString(Name));

    while (rs.Read())
    {
    output.AppendLine(rs.GetString(Name)
    + " " + rs.GetString(Name));
    }

    // Set the output in the label
    lblResults.Text = output.ToString();
    }
    else
    {
    lblResults.Text = "No Rows Found.";
    }

    }
    catch (SqlCeException sqlexception)
    {
    MessageBox.Show(sqlexception.Message, "Error.",
    MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    catch (Exception ex)
    {
    MessageBox.Show(ex.Message, "Error.",
    MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    finally
    {
    con.Close();
    }

#endregion

it's throwing the bellow exception.

There was an error parsing the query. [ Token line number = 1,Token line offset = 53,Token in error = % ]

Answer 1

A useful way to solve such issues is to view the SQL string generated by your code right before sending it to SQL Server. If you can spot the problem immediately, that's great - fix it. If you can't try running the full query directly with the SQL Server Management Studio and see if you understand the problem. If you still can't post this query as a question on a Q&A site (just like here on SO) and it will be much easier to help you.

In this case, it looks to me like you're missing single quotes around the value ("like 'text'") - but I can't be sure cause it depends on the value of tbSearch.Text.