CODEWITHSUNDEEP
c#.netsqlite
Kayla Johnston
Kayla Johnston

Reputation: 9

SQLite Select query using user input c#

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"]);
        }
    }
}

Upvotes: 1

Views: 1632

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

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"]);
        }
    }
}

Upvotes: 1

Related Questions