sqlite database not returning results

Question

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();
    trans.Dispose();
}
return data;

The TAGTABLE has following fields:

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

Did you try to execute exactly same questy into SQLite designer of VS. ? — Tigran
– Tigran, Commented Sep 2, 2011 at 10:53
@armin I suspect the transaction is doing something funky to the entire command, I've posted some amended code that should work as expected. I can't answer what the trans.Commit line would do, I've no idea. — Adam Houldsworth
– Adam Houldsworth, Commented Sep 2, 2011 at 10:56
@armin There might be something funny with the column names, can you list the column names being returned and also some sample data? Also, can you provide a sample value of what tag might contain. — Adam Houldsworth
– Adam Houldsworth, Commented Sep 2, 2011 at 11:05

Adam Houldsworth · Accepted Answer · 2011-09-02 10:59:50Z

2

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.

edited Sep 2, 2011 at 10:59

answered Sep 2, 2011 at 10:54

Adam Houldsworth

64.8k11 gold badges161 silver badges190 bronze badges

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

3 Comments

armin Over a year ago

Still not working,only returning columns not rows.by the way I'm using system.data.sqlite wrapper.

Adam Houldsworth Over a year ago

@armin What are the column names of the columns being returned?

armin Over a year ago

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

Yahia · Accepted Answer · 2011-09-02 11:09:55Z

0

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...

edited Sep 2, 2011 at 11:09

answered Sep 2, 2011 at 10:56

Yahia

70.5k9 gold badges117 silver badges147 bronze badges

4 Comments

armin Over a year ago

security and injection is not an issue for me here,it's local program for manipulating images.no ones gonna bother coming up with an attack.

armin Over a year ago

select is working fine in sqlite manager,just not working in my program

armin Over a year ago

no exception is thrown,I traced the program step by step,but still no clue.and somebody told me using transactions would be a good programing practice.

Yahia Over a year ago

transactions are a good practice but not for everything... usually when you just SELECT there is seldomn need for a transaction... please show the valud of tag AND some sample data

Collectives™ on Stack Overflow

sqlite database not returning results

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related