locked
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

    Saturday, February 14, 2015 12:05 AM

Answers

  • 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


    Sunday, February 15, 2015 9:05 AM

All replies

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

    Sunday, February 15, 2015 2:50 AM
    Moderator
  • 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


    Sunday, February 15, 2015 9:05 AM
  • 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.

    Sunday, February 15, 2015 1:41 PM
    Moderator