sqlite database not returning results

Why my query does not return results? I'm using C#. It returns column headers but no rows. Is there a problem with my select statement?

here's my code :

conn = new SQLiteConnection("Data Source=local.db;Version=3;New=False;Compress=True;");
DataTable data = new DataTable();
SQLiteDataReader reader;
using (SQLiteTransaction trans = conn.BeginTransaction())
{
    using (SQLiteCommand mycommand = new SQLiteCommand(conn))
    {
        mycommand.CommandText = "SELECT * FROM TAGTABLE WHERE TAG = '"+tag+"' ;";
        reader = mycommand.ExecuteReader();
    }
    trans.Commit();
    data.Load(reader);
    reader.Close();
    reader.Dispose();开发者_Python百科
    trans.Dispose();
}
return data;

The TAGTABLE has following fields:

TID int,
Tag varchar(500),
FilePath varchar(1000)

You don't need the transaction, try the following:

    DataTable data = new DataTable();

    using (SQLiteConnection conn = new SQLiteConnection("Data Source=local.db;Version=3;New=False;Compress=True;"))
    using (SQLiteCommand mycommand = new SQLiteCommand(conn))
    {
        mycommand.CommandText = "SELECT * FROM TAGTABLE WHERE TAG = @tag;";
        mycommand.Parameters.AddWithValue("@tag", tag);
        conn.Open();

        using (SQLiteDataReader reader = mycommand.ExecuteReader())
        {
            data.Load(reader);  
        }
    }

    return data;

The most likely reason this won't return anything is if the SELECT doesn't yield any results.

Also note that anything implementing the IDisposable interface can be used in conjunction with the using statement, so manual closing / disposal of objects afterwards is not required.

Notice that the SQL has changed to use a parameterized query, this will help reduce the likelihood of SQL injection attacks, and is generally cleaner.

Since you don't show sample data and what should be returend some general pointers only:

• The way you build the SQL is wide open to SQL incjection (a serious security issue)
• Depending on the value of tag (for example if it contains ') the above SQL Statement would do something you don't expect
• Since everything is wrappend in using (a good thing!) the question is, whether there is some exception thrown inside the using block (check with the debugger)
• why are you using a transaction ? I can't see any reason which makes that necessary...

Please show some sample data with param value and expected result...