Using the LIKE Operator: Works With SQL Server but not SQLite

Question

My application is one that retrieves data online when it is connected to the internet, but allows the user to view the data when offline. To achieve this, I have the app download pertinent information from the online SQL server and save it to a SQLite DB for use when the machine is not connected to the internet. On one of my windows, I have a search function that finds "notes" that have the entered string. So first I wrote the query for when it is connected to the SQL database:

                SqlConnection conn = new SqlConnection(myconnstring);
                await conn.OpenAsync();
                string sqlstr = "SELECT * FROM " + Application.Current.Properties["connection"] + "_Notes WHERE Written_ID = @uid AND Content LIKE '%' + @srch + '%' ORDER BY Creation_Time DESC";
                SqlCommand cmd = new SqlCommand(sqlstr, conn);
                cmd.Parameters.AddWithValue("uid", Convert.ToInt32(Application.Current.Properties["userid"].ToString()));
                cmd.Parameters.AddWithValue("srch", searchbox.Text.ToString());
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable("Notes");
                sda.Fill(dt);
                notescontrol.ItemsSource = dt.DefaultView;
                conn.Close();

So this does exactly what I want it to do. It returns all the notes that have that string. Next I created the string for the SQLite DB (this database has an [Office] column that needs to match the user's current column):

                SQLiteConnection conn = new SQLiteConnection(mysqliteconnstring);
                await conn.OpenAsync();
                string sqlstr = "SELECT * FROM Notes WHERE Office = @off AND Written_ID = @uid AND Content LIKE '%' + @srch + '%' ORDER BY Creation_Time DESC";
                SQLiteCommand cmd = new SQLiteCommand(sqlstr, conn);
                cmd.Parameters.AddWithValue("off", Application.Current.Properties["connection"].ToString());
                cmd.Parameters.AddWithValue("uid", Convert.ToInt32(Application.Current.Properties["userid"].ToString()));
                cmd.Parameters.AddWithValue("srch", searchbox.Text.ToString());
                SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
                DataTable dt = new DataTable("Notes");
                sda.Fill(dt);
                notescontrol.ItemsSource = dt.DefaultView;
                conn.Close();

I did my own research and I did not find that there were any differences pertaining to the like statement in SQL vs SQLite. So where have I gone wrong?

The concatenation is different in SQL Server and SQLite – C.J. Jul 25 '14 at 17:45 — C.J., Jul 25 '14 at 17:45

score 2 · Accepted Answer · edited May 23 '17 at 12:20

According to this answer the string concatenation operator in SQLite is ||. So you need two different query texts. But you could avoid this problem if you change the way in which you pass the parameter for the LIKE condition

 string sqlstr = @"SELECT * FROM Notes WHERE Office = @off 
                     AND Written_ID = @uid 
                     AND Content LIKE @srch 
                     ORDER BY Creation_Time DESC";

 ....

 cmd.Parameters.AddWithValue("@srch", "%" + searchbox.Text + "%");

By the way, the searchbox.Text property is already a string, no need to call ToString() on it.

Using the LIKE Operator: Works With SQL Server but not SQLite

1 Answers1