c# Sqlite Query problems

Question

Guys

Im writing a windows store app using an sqlite database and am very new to this so please forgive my ignorance but I cannot work out how to display a particular row based on a condition

My database has 1 table called "Cities" and has 23 columns

Through various articles I have tried to use the code below

        SQLiteAsyncConnection con = new SQLiteAsyncConnection("SqlLiteWin8-1.db");


        private async void Button_Click88(object sender, RoutedEventArgs e)
        {
            var query = con.Table<CityClass>().Where(x => x.WORKNUM == "112000");
            var queryResult = await query.ToListAsync();
            foreach (var item in queryResult)
            {
                tb3.Text = item.WORKNUM;
                tb4.Text = item.FAULTREF;
            }


        public class CityClass
    {

        public string WORKNUM { get; set; }
        public string DESCRIPTION { get; set; }
        public string REPDATTIM { get; set; }
        public string REPBY { get; set; }
        public string Id { get; set; }
        public string FAULTREF { get; set; }
        public string SCHOOL { get; set; }
        public string CATEGORY { get; set; }
        public string FIRSTCON { get; set; }
        public string TARGRESDATTIM { get; set; }
        public string ACTRESDATTIM { get; set; }
        public string WORKSRES { get; set; }
        public string RESMET { get; set; }
        public string CONTRACTOR { get; set; }
        public string ACTFINDATTIM { get; set; }
        public string WORKSCOM { get; set; }
        public string COMMET { get; set; }
        public string DEFAPPLIED { get; set; }
        public string AUDITREF { get; set; }
        public string EXTSTATUS { get; set; }
        public string REVCOM { get; set; }

public class DataBaseWrapper
    {

        public static string Cities = "cities";
    }

When I run the code i get a Table cityclass not found error

I have tried substituting cityclass for database wrapper but then it cant find the column names

I have read all the articles I could find on sqlite and windows store apps but keep running into same issues

Ideally I want to query the database so that it brings back just the row that equals the WORKNUM column and puts all 23 columns into an array so I can then select each one 1 at a time and put the contents into textbloxks

Any ideas where im going wrong or could you point me in the direction of any articles that could help me understand how to do this

Your help is greatly appreciated

Mark

Answer 1

Hi Mark,

May I know if you already have the database initialized? Otherwise you could not able to get any record. See a blog for how to create the database var sql code: http://blogs.msdn.com/b/andy_wigley/archive/2013/06/06/sqlite-winrt-database-programming-on-windows-phone-and-windows-8.aspx

--James

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 2

Thanks James I managed to work it out the following works

var ITEM = db.Query<CityClass>("Select * From Cities where WORKNUM='" + srcnum + "' ").FirstOrDefault();

i wasnt getting all the columns or using firstordefault

Thanks

Mark

Answer 3

Hi wanbaugh1,

Nice to see the shared solution.

--James

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.