SQLite query syntax

I have just started using SQLite and am having difficulties with my query statements.
Here is an abbreviated copy of my code:

    public class ESItemdb
    {
        [PrimaryKey]
        public int UniqueId { get; set; }
        public string Title { get; set; }
        public string Group { get; set; }

        public override string ToString()
        {
            return string.Format("{0}", Title);
        }
    }

    public static class ESData
    {
        public static void OpenData()
        {
            try
            {
                var db = new SQLiteConnection(Vars.DatabasePath);
              //IEnumerable<ESItemdb> foo = db.Query<ESItemdb>("select * from ESItemdb where Group = \"?\"", "Messier");
                IEnumerable<ESItemdb> foo = db.Query<ESItemdb>("select * from ESItemdb where Group = \"Messier\"");
                List<ESItemdb> Messier = foo.ToList<ESItemdb>();
            }
            catch (Exception ex)
            {
                Toast.MakeText(this, ex.ToString(), ToastLength.Short).Show();
            }

        }
    }

If I use select * from ESItemdb it works just fine. If I use select * from ESItemdb where UniqueID <100 it works fine.
If I use either version of the select statement above, I get a SQLite syntax error. What is the correct format?

Thanks,
Dan

Tagged:

Best Answer

  • DanDickersonDanDickerson US ✭✭
    Accepted Answer

    I figured out the answer. The problem lies in the column name Group
    It seems that that is a reserved word. Once I changed the name to ESGroup, everything worked fine.
    Here is the new code:

        string bar = "Messier";
        IEnumerable<ESItemdb> foo = db.Query<ESItemdb>("select * from ESItemdb where ESGroup = ?", bar);
    

    Is there a list somewhere of reserved words?

Answers

  • DanDickersonDanDickerson USMember ✭✭
    Accepted Answer

    I figured out the answer. The problem lies in the column name Group
    It seems that that is a reserved word. Once I changed the name to ESGroup, everything worked fine.
    Here is the new code:

        string bar = "Messier";
        IEnumerable<ESItemdb> foo = db.Query<ESItemdb>("select * from ESItemdb where ESGroup = ?", bar);
    

    Is there a list somewhere of reserved words?

Sign In or Register to comment.