sql query LIKE problems on c# using sqlite

Question

I'm using SQLite provider on c#. I'm trying to use SQL select * from table where name like 'part_of_name%'. But this query don't work correctly. Debug write System.InvalidOperationException in application System.Data.SQLite.dll.

Example: I have table Books(id, name) with 3 items:

Harry Potter and the Sorcerer's Stone
Harry Potter and the Chamber of Secrets
Godfather

In SQLite Manager query select * from Books where name like 'Harry%' shows me 1-st and 2-nd items, but in my c# application only 1-st and debug exception. When I try ... like '%' my application shows me only 1-st item, but when I try ... like 'God%' it shows me 3-rd item. Help me please. Thanks! This is the code:

sqliteConn.Open(); //open connection
sqliteDA.SelectCommand = new SQLiteCommand("select * from Books where name like '" + text + "%'", sqliteConn); //create SQL-query in sqlite data adapter
dataSet.Tables["Books"].Clear(); //clear our dataset.table with old info
sqliteDA.Fill(dataSet, "Books"); //fill our dataset.table with info from sqlite data adapter
sqliteConn.Close(); //close connection

What do you see if you try setting, for example, string cmd = "select id from Books..."? Does cmd exactly equal the query you're trying to generate? Also, exactly which line generates the exception? — King Skippus
– King Skippus, Commented Dec 29, 2012 at 0:57
string cmd like select * from Books where name='Harry Potter an the Chamber of Secrets' works correct. Exception was generated in line sqliteDA.Fill(dataset, "Books"); — Timothy
– Timothy, Commented Dec 29, 2012 at 1:23

Felipe Oriani · Accepted Answer · 2012-12-29 10:14:06Z

1

You should use parameters and apply the % on the parameter value. Try something like this and let me know if it works:

SQLiteCommand cmd = new SQLiteCommand("select * from Books where name like @name", sqliteConn); 
cmd.Parameters.AddWithValue("@name", string.Concat(text, "%"));
sqliteDA.SelectCommand = cmd;

DataTable dataTable = new DataTable();
sqliteDA.Fill(dataTable); //just a DataTable instance

edited Dec 29, 2012 at 10:14

answered Dec 29, 2012 at 1:00

Felipe Oriani

38.7k20 gold badges141 silver badges203 bronze badges

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

5 Comments

Timothy Over a year ago

Excuse me, but what I should write instead nameParametre?

Timothy Over a year ago

Nope, just an exception and incorrect result after query... I start to think, that it's just a bug in dll when using LIKE

Felipe Oriani Over a year ago

Could you post the exception, error message, inner exception?

Timothy Over a year ago

The first stage of processing exceptions of type "System.InvalidOperationException" Annex System.Data.SQLite.dll - in line sqliteDA.Fill(dataset, "Books");

Timothy Over a year ago

IT WORKS! Thank you so much for help. I just use DataTable and try some changes in my code and it's work!

Collectives™ on Stack Overflow

sql query LIKE problems on c# using sqlite

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related