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 = % ]

Have you added a breakpoint and checked the value of the 'sql' variable? — Karl Gjertsen
– Karl Gjertsen, Commented Nov 10, 2015 at 6:39
I added breakpoint and it's throwing an exception in this code. SqlCeResultSet rs = cmd.ExecuteResultSet( ResultSetOptions.Scrollable); — Source Codes
– Source Codes, Commented Nov 10, 2015 at 6:51
@KarlGjertsen yes here is the value "select Name from tblCustomers where Name like ae % where Name like ae % " — Source Codes
– Source Codes, Commented Nov 10, 2015 at 7:02
As Amit says, try running this in management studio. You'll see that you're missing the single quotes around the values you're searching on. — Karl Gjertsen
– Karl Gjertsen, Commented Nov 10, 2015 at 7:04

Amit · Accepted Answer · 2015-11-10 06:41:23Z

2

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.

answered Nov 10, 2015 at 6:41

Amit

46.5k9 gold badges84 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Source Codes Over a year ago

I added breakpoint and it's throwing an exception in this code. SqlCeResultSet rs = cmd.ExecuteResultSet( ResultSetOptions.Scrollable);

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related