14 Replies Latest reply on Mar 9, 2012 3:07 AM by nfallon55

    Flex 4.6 mobile sqlite list specific data

    nikrdu

      First off I am new to using Flex, but I have pieced this app together with help from online tutorials and information. Basically my app is like a directory of names, addresses and so forth, but I also have additional fields in the db of "week" and "day". What I am trying to do is populate a list that only shows for example, the names from Week 1 on Tuesday. Below is some of the code I am using to help you understand what I am trying to do. I appreciate any help!

       

      <s:List dataProvider="{AddDoctorDatabase.doctors()}" labelField="name" change="onDoctorSelected(event)"
             
      left="0" right="0" top="0" bottom="0">
      </s:List>


      public static function doctors():ArrayCollection
         
      {
              var doctorList
      :ArrayCollection = new ArrayCollection();

              var sql
      :String = "SELECT id, week, day, name, address, city, state, zip, phone FROM doctors";
              var stmt
      :SQLStatement = new SQLStatement();
              stmt
      .sqlConnection = sqlConnection;
              stmt
      .text = sql;
              stmt
      .execute();
              var sqlResult
      :SQLResult = stmt.getResult();
             
      if (sqlResult) {
                  var result
      :Array = sqlResult.data;
                 
      if (result) {
                     
      for (var index:Number = 0; index < result.length; index++) {
                          doctorList
      .addItem(processRow(result[index]));
                     
      }
                 
      }
             
      }
             
      return doctorList;
         
      }

       


        • 1. Re: Flex 4.6 mobile sqlite list specific data
          nfallon55 Level 1

          You need the where clause.

           

          "Select name FROM doctors WHERE week = 'Tuesday'";

           

          If you are trying to use a variable you can use

           

          "Select name FROM doctors WHERE week ='" + myVariable + "'";

           

          You can also use the AND with the WHERE clause.

           

           

          Neil

          • 2. Re: Flex 4.6 mobile sqlite list specific data
            nikrdu Level 1

            Where exactly would this "where" clause go. Do I need to create a labelFunction instead of a labelField and then create a function that uses the "where" clause for that list?

             

            I appreciate the response Neil.

            • 3. Re: Flex 4.6 mobile sqlite list specific data
              nfallon55 Level 1

              If I'm reading your example correctly you are only trying to dump the names associated with a particular day in to a list.

               

              The WHERE clause would go:

               

              "SELECT id, week, day, name, address, city, state, zip, phone FROM doctors WHERE week = '" + myWeekVariable + "'AND day ='" + myDayVariable + "'";

               

               

              Neil


              • 4. Re: Flex 4.6 mobile sqlite list specific data
                nikrdu Level 1

                I'm sorry to keep on bothering you and being so ignorant with this, but I need a little bit more clarification. In the statement below, how would I specify populating the list with say the week being "Week 1" and the day being "Tuesday"? Also, "myWeekVariable" and "myDayVariable"...are those something I need to create and define somewhere? I am already using "week" and "day" as variables throughout my project.

                 

                <s:List dataProvider="{AddDoctorDatabase.doctors()}" labelField="name" change="onDoctorSelected(event)"
                       
                left="0" right="0" top="0" bottom="0">
                </s:List>

                • 5. Re: Flex 4.6 mobile sqlite list specific data
                  nfallon55 Level 1

                  In your <s:List/>  give it an id. <s:List id = "myList"/>

                   

                  In your area that you are running your select statement:

                   

                  "SELECT name FROM doctors WHERE week = '" + myWeekVariable + "'AND day ='" + myDayVariable + "'";

                   

                  stmt.execute();

                                 

                  var result:Array = stmt.getResult().data;

                  var myAC:ArrayCollection = new ArrayCollection;

                   

                   

                  for each( var obj:Object in result )

                  {

                  myAC.addItem(obj.list_name);

                   

                   

                  dbConnection.close();

                  myList.dataProvider = myAC;

                   

                   

                  This will fill the list with anything in th name field that matches your search criteria.  You can also save yourself the trouble of sorting the liist by using the ORDER BY in the select statement like this:

                   

                  "SELECT name FROM doctors WHERE week = '" + myWeekVariable + "'AND day ='" + myDayVariable + "'ORDER BY name";

                   

                   

                  Neil

                  • 6. Re: Flex 4.6 mobile sqlite list specific data
                    nfallon55 Level 1

                    Sorry about that.  I had a mistake in the for loop.

                     

                    "SELECT name FROM doctors WHERE week = '" + myWeekVariable + "'AND day ='" + myDayVariable + "'ORDER BY name";

                     

                    stmt.execute();

                                   

                    var result:Array = stmt.getResult().data;

                    var myAC:ArrayCollection = new ArrayCollection;

                     

                     

                    for each( var obj:Object in result )

                    {

                    myAC.addItem(obj.name);

                     

                     

                    dbConnection.close();

                    myList.dataProvider = myAC;

                     

                     

                    Neil

                    • 7. Re: Flex 4.6 mobile sqlite list specific data
                      nfallon55 Level 1

                      You will also have to add the import statement for the ArrayCollection to the top of your code:

                       

                      import mx.collections.ArrayCollection;

                       

                      Neil

                      • 8. Re: Flex 4.6 mobile sqlite list specific data
                        nikrdu Level 1

                        Everything makes sense, I am just confused on the week and day. I have two weeks and 5 days for each week. I will have a list for each week and day combination. So where do I specify in the list attribute to only show a specific week and a specific day? Is it something like below, or will that not work.

                         

                        dataProvider="{AddDoctorDatabase.doctors(Week 1, Tuesday)

                         


                        • 9. Re: Flex 4.6 mobile sqlite list specific data
                          nfallon55 Level 1

                          You do it with the select statement just as I have shown.  It will only select the name based on the week and day.  In the example that I gave you you are already setting the dataProvider to the list based on the return from the table.  You don't have to set it anywhere else.  If you want something other than the name then change that in the select statement and in the example change

                           

                          myAC.addItem(obj.name);

                           

                          to

                           

                          myAC.addItem(obj.whatever column in table);

                           

                           

                          If you are selecting something from a listbox then in the listbox indexChangeEvent you would add the code to see what the user selected which would be someListName.selectedItem

                           

                           

                          Neil

                          • 10. Re: Flex 4.6 mobile sqlite list specific data
                            nikrdu Level 1

                            Maybe I need to back up one step to make sure we are both on the same page. Below is the code from a view called MondayList.mxml in which I am displaying a list of names from Week 1 and Monday. Below that is snippet of the code from the AddDoctorDatabase.as that generates the db. From the way I am understanding you, I would need to write a seperate WHERE statement and function for each combination of weeks and days...? I thought it was possible to have one WHERE statement in one function that is listed in AddDoctorDatabase.as, and then in each view or .mxml file I could specify the list to show just specific weeks and days. Does this make sense?

                             

                            MondayList.mxml

                             

                            <s:View xmlns:fx="http://ns.adobe.com/mxml/2009"

                                                xmlns:s="library://ns.adobe.com/flex/spark"

                             

                                                title="Week 1 - Monday">

                                      <fx:Script>

                                                <![CDATA[

                                                          import model.AddDoctor

                                                          import model.AddDoctorDatabase

                                                          import mx.events.FlexEvent;

                             

                                                          import spark.events.IndexChangeEvent;

                             

                                                          protected function onDoctorSelected(event:IndexChangeEvent):void {

                                                                    var selectedDoctor:AddDoctor = event.currentTarget.dataProvider[event.newIndex];

                                                                    navigator.pushView(Details, selectedDoctor);

                                                          }

                             

                             

                             

                             

                                                ]]>

                                      </fx:Script>

                                      <fx:Declarations>

                             

                                      </fx:Declarations>

                                      <s:List dataProvider="{AddDoctorDatabase.doctors()}" labelField="name" change="onDoctorSelected(event)"

                                                          left="0" right="0" top="0" bottom="0">

                                      </s:List>

                                      <s:actionContent>

                                                <s:Button label="Back" click="navigator.popView()"/>

                                      </s:actionContent>

                            </s:View>

                             

                             

                            AddDoctorDatabase.as

                             

                            package model

                            {

                                      import flash.data.SQLConnection;

                                      import flash.data.SQLResult;

                                      import flash.data.SQLStatement;

                                      import flash.events.SQLEvent;

                                      import flash.filesystem.File;

                                      import flash.filesystem.FileMode;

                                      import flash.filesystem.FileStream;

                             

                                      import model.AddDoctor;

                             

                                      import mx.collections.ArrayCollection;

                             

                                      public class AddDoctorDatabase

                                      {

                                                private static var _sqlConnection:SQLConnection;

                             

                                                public static function get sqlConnection():SQLConnection

                                                {

                                                          if (_sqlConnection)

                                                                    return _sqlConnection;

                                                          openDatabase(File.applicationStorageDirectory.resolvePath("doctor.db"));

                                                          return _sqlConnection;

                                                }

                             

                                                public static function getDoctor(id:int):AddDoctor

                                                {

                                                          var sql:String = "SELECT id, week, day, name, address, city, state, zip, phone FROM doctors WHERE id=?";

                                                          var stmt:SQLStatement = new SQLStatement();

                                                          stmt.sqlConnection = sqlConnection;

                                                          stmt.text = sql;

                                                          stmt.parameters[0] = id;

                                                          stmt.execute();

                                                          var result:Array = stmt.getResult().data;

                                                          if (result && result.length == 1)

                                                                    return processRow(result[0]);

                                                          else

                                                                    return null;

                                                }

                             

                             

                             

                                                public function doctors():ArrayCollection

                                                {

                                                          var doctorList:ArrayCollection = new ArrayCollection();

                             

                            var sql:String = "SELECT id, week, day, name, address, city, state, zip, phone FROM doctors WHERE week = '"Week 1"'AND day ='"Monday"'";

                                                          var stmt:SQLStatement = new SQLStatement();

                                                          stmt.sqlConnection = sqlConnection;

                                                          stmt.text = sql;

                                                          stmt.execute();

                                                          var sqlResult:SQLResult = stmt.getResult();

                                                          if (sqlResult) {

                                                                    var result:Array = sqlResult.data;

                                                                    if (result) {

                                                                              for (var index:Number = 0; index < result.length; index++) {

                                                                                        doctorList.addItem(processRow(result[index]));

                                                                              }

                                                                    }

                                                          }

                                                          return doctorList;

                                                }

                             

                                                public static function addDoctor(doctor:AddDoctor):void

                                                {

                                                          var sql:String =

                                                                    "INSERT INTO doctors (week, day, name, address, city, state, zip, phone) " +

                                                                    "VALUES (?,?,?,?,?,?,?,?)";

                                                          var stmt:SQLStatement = new SQLStatement();

                                                          stmt.sqlConnection = sqlConnection;

                                                          stmt.text = sql;

                                        stmt.parameters[0] = doctor.week;

                                                          stmt.parameters[1] = doctor.day;

                                                          stmt.parameters[2] = doctor.name;

                                                          stmt.parameters[3] = doctor.address;

                                                          stmt.parameters[4] = doctor.city;

                                                          stmt.parameters[5] = doctor.state;

                                                          stmt.parameters[6] = doctor.zip;

                                                          stmt.parameters[7] = doctor.phone;

                             

                                                          stmt.execute();

                                                }

                             

                            .....

                            • 11. Re: Flex 4.6 mobile sqlite list specific data
                              nikrdu Level 1

                              Still there nfallon55 or does anyone else have any ideas?

                              • 13. Re: Flex 4.6 mobile sqlite list specific data
                                nikrdu Level 1

                                Did you see my question above?

                                • 14. Re: Flex 4.6 mobile sqlite list specific data
                                  nfallon55 Level 1

                                  Sorry about that.  I lost my system with the Windows 8 Preview and had to order new disks from Toshiba to get it back.

                                   

                                  You do have to do a different select statement for the different pieces of information that you want to get from a database.  If you just want the list of doctors that are for week 25 and Monday then you would query the database for those results.  If you then turn around and want the information from week 6 and Thursday you would need to query the database for those results. 

                                   

                                  Neil