SQLite Select query using user input c#

Question

I have a database with 1 table that has 1 column that keeps of my file watcher. I want the user to be able to search for a file extension of their choice but am having a hard time getting it to work correctly. I can query for all file extensions, but when I try to use user input as a variable something seems to not be working correctly. (Also using wildcard character for searching). My issue is primarily only with the select statement.(Or so I think.)

string str = textBox1.Text.Trim();

query = "SELECT * FROM LOG WHERE ENTRY like '%@str%'";

using (SQLiteCommand cmd = new SQLiteCommand(query, conn))
{
    using (SQLiteDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            listBox1.Items.Add((string) reader["ENTRY"]);
        }
    }
}

PoLáKoSz · Accepted Answer · 2018-07-15 14:40:16Z

1

You are not setting the parameter to the query. Also, you need to separate the wildcards from the parameter in the query text. Try this:

string str = textBox1.Text.Trim();

query = "SELECT * FROM LOG WHERE ENTRY like '%' || @str || '%'";

using (SQLiteCommand cmd = new SQLiteCommand(query, conn))
{
    // Might need a different data type here
    cmd.Parameters.Add("@str",  SQLiteType.Text).Value = str;

    using (SQLiteDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            listBox1.Items.Add((string)reader["ENTRY"]);
        }
    }
}

edited Jul 15, 2018 at 14:40

PoLáKoSz

3591 gold badge6 silver badges9 bronze badges

answered Oct 21, 2017 at 5:16

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

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

Collectives™ on Stack Overflow

SQLite Select query using user input c#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related