8 Replies Latest reply on Jan 20, 2010 9:16 PM by Foxchx

    Populating a Datagrid with Data from AIRSQL

    Foxchx Level 1

      I am looking for the quickest way to populate a list from a column in my AIRsql DB in my datagrid.

       

      I seem to be missing something as all the example i find are using xml with HTTP service... What if we simply need to call info from DB?

        • 1. Re: Populating a Datagrid with Data from AIRSQL
          Francisc Level 3

          How far are you with the SQLite DB?

           

          Is it loaded in the application?

          All that's left to do is display the data in the DataGrid?

          • 2. Re: Populating a Datagrid with Data from AIRSQL
            Foxchx Level 1

            Yeah, everything is running fine, i have pages working, one of my page contain a datagrid.[actually 4 diferent ones, but i assume that once i learn the code for one i can do the same with the other ones and just change which column they fetch from in my DB]

             

            I get really confiused with that http thingy, which i think is what i need to do but even there i am not sure.

             

            ---------------

             

            Just so you know more about it....

             

            My app have a search.

             

            Search result show in panel2 (in a basic datagrid)

            click on any result....and it will open panel3 (with all the info from that specific entry.)

             

            That works fine

             

            What i also want is to have Datagrid (still panel 2) but this time just showing a list of entry that you can click to open the panel 3 so that i can sort my DB entries by , say, woman/man/children etc and have the full list there instead of searching for a specific name....

             

            thanks

            • 3. Re: Populating a Datagrid with Data from AIRSQL
              Francisc Level 3

              You don't need the HTTPService thingy, all you need to is assign the event:ResultEvent to an ArrayCollection that is the dataProvider for the DataGrid.

              Here's an example:

               

              [Bindable]

              private var acSQLiteData:ArrayCollection=new ArrayCollection();

               

              private function getData()

              {

                   var sql:SQLStatement=new SQLStatement();

                   sql.text="select column1 as label,column2 as data from myTable where column3=@condition order by column1 asc";

                   sql.parameters['@condition ]='something';

                   sql.sqlConnection=mySQLConnection;//mySQLConnection is the SQLConnection

                   try

                   {

                        sql.execute();

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

                        acSQLiteData=new ArrayCollection(result);

                   }

                   catch(error:SQLError)

                   {

                        Alert.show("Data could not be fetched.","Error");

                   }

              }

               

              ...

               

              <mx:DataGrid id="myDG" width="100%" height="100%" dataProvider="{acSQLiteData}"/>

               

               

              Observation:

              ~~~~~~~~~~

              I selected colums from the SQLite database as label and as data because the DataGrid component takes those values as default to display. But you can tell the DataGrid what columns to show and in what way.

               

               

              Hope this helps.

              • 4. Re: Populating a Datagrid with Data from AIRSQL
                Foxchx Level 1

                Ok, i will try to implement that..

                 

                I realised though that i will need some more code to make the connection between the entry and the info page i want to open when the entry is clicked...

                 

                Out of curiosity.... I have been bashing my head on stuff like that all along this project, becasue i am used to take codes that work and duplicate it and adapt it to my new need, this being a good example:

                -----------

                Search....

                ...populate Data grid

                Click on entry...

                ...page(with all the info of that entry account(ROW) open)

                ------------

                 

                That i have and it works (employee directory slightly adapted)

                 

                Can't i just reuse this code, remove the search part, and simply populate the datagrid with the left over code??

                (that's what i wonder the most because it makes things easier for me, i am not very good at programming (u should see my code it's all duplicates of working code))

                 

                 

                anyways, working on that code you gave me, opefully it'll open my eyes because this project is killling me, i feel so dumb thanks to FLEX! hehe

                • 5. Re: Populating a Datagrid with Data from AIRSQL
                  Francisc Level 3

                  Well, working with Flex will give confidence over time, don't worry!

                   

                  If you need, I can quickly write an app that reads data from a database and shows it in a DataGrid and post the files here.

                  • 6. Re: Populating a Datagrid with Data from AIRSQL
                    Foxchx Level 1

                    Yeah well, i couldn't make your code work...i am really bad at making any code fit my app for some reason....

                     

                    you see, are right in what i need is only missing the <mx:Script> that will make it work... my problem is that i can recycle stuff but i still don't get the coding from scratch...(it's the Employee Directory app)

                     

                    I was looking at the {appModel.searchResults} that works, I have the code for that because it was in the application already... but i don't want to have the search functionability just the display and link to the employee's file...

                     

                     

                     

                     

                     

                     

                     

                    -------------------------- SearchResult working code ---------------------------------

                     

                     

                     


                    <mx:Script>
                    <![CDATA[
                         import com.adobe.empdir.model.Employee;
                         import com.adobe.empdir.model.ConferenceRoom;
                         import com.adobe.empdir.model.IModelObject;
                         import com.postbabel.empdir.MainContentUIModel_HListDetail;
                        
                         public function showContent():void{
                             this.showPanel();
                         }
                        
                         private var uiModel:MainContentUIModel_HListDetail =
                             MainContentUIModel_HListDetail.getInstance();
                        
                         private function displayModelObject(item:IModelObject):void{
                             if(item is Employee){
                                 uiModel.currentState =
                                     MainContentUIModel_HListDetail.EMPLOYEE_VIEW;
                             }else if(item is ConferenceRoom){
                                 uiModel.currentState =
                                     MainContentUIModel_HListDetail.CONFERENCEROOM_VIEW;
                             }

                     

                           
                             appModel.selectedItem = item;
                         }
                    ]]>
                    </mx:Script>


                    <mx:Script>
                        <![CDATA[
                            import com.adobe.empdir.ApplicationModel;
                           
                            [Bindable]
                            private var appModel:ApplicationModel =
                                ApplicationModel.getInstance();
                        ]]>
                    </mx:Script>

                     

                       
                        <mx:Box width="329" height="100%" verticalGap="0">

                     

                     

                     

                        <mx:DataGrid width="329" dataProvider="{appModel.searchResults}"
                           
                            change="displayModelObject(event.target.selectedItem)"
                             height="400" alternatingItemColors="[#eeeeee, #cccccc]">
                            <mx:columns>
                                <mx:DataGridColumn headerText="Col1" dataField="Col1"/>
                                <mx:DataGridColumn headerText="Col2" dataField="Col2"/>
                            </mx:columns>
                        </mx:DataGrid>

                     

                    </mx:Box>

                     

                     

                    </ContentPanel>

                     

                     

                     

                     

                     

                     

                     

                     

                     

                     

                    -------------------------- missing the <mx:Script> code that i need to populate the grid with ---------------------------------

                     

                    <?xml version="1.0" encoding="utf-8"?>
                    <ContentPanel xmlns="com.adobe.empdir.ui.*" xmlns:mx="http://www.adobe.com/2006/mxml" width="330" height="420"   backgroundColor="#cc0000" borderThickness="0">

                     

                     

                     

                     

                     

                     

                     

                     

                     


                        <mx:VBox  label="Test 录 Test" width="100%" height="300" verticalGap="0">
                      
                        <mx:DataGrid width="327" height="100%" alternatingItemColors="[#eeeeee, #cccccc]"  dataProvider="{ ??????? }">
                            <mx:columns>
                                <mx:DataGridColumn headerText="Col1" dataField="Col1"/>
                                <mx:DataGridColumn headerText="Col1" dataField="Col2"/>
                            </mx:columns>
                        </mx:DataGrid>
                            </mx:VBox>

                     

                     

                     

                     

                    </ContentPanel>

                     

                     

                     

                     

                     

                     

                    -------------------------- What i though i had to do with your code you gave me earlier --------------------------

                    -------------------------- (Obviously didn't work - I don't think that the SQLlite is right here as i use AIRsql.... or m I just wrong??) ---------------------------------

                     

                     

                     

                     

                     

                     

                    <?xml version="1.0" encoding="utf-8"?>
                    <ContentPanel xmlns="com.adobe.empdir.ui.*" xmlns:mx="http://www.adobe.com/2006/mxml" width="330" height="420"   backgroundColor="#cc0000" borderThickness="0">

                     

                     

                     

                     

                     

                    <mx:Script>
                    <![CDATA[

                     

                    [Bindable]

                    private var acSQLiteData:ArrayCollection=new ArrayCollection();

                     

                    private function getData()

                    {

                         var sql:SQLStatement=new SQLStatement();

                         sql.text="select column1 as label,column2 as data from myTable where column3=@condition order by column1 asc";

                         sql.parameters['@condition ]='something';

                         sql.sqlConnection=mySQLConnection;//mySQLConnection is the SQLConnection

                         try

                         {

                              sql.execute();

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

                              acSQLiteData=new ArrayCollection(result);

                         }

                         catch(error:SQLError)

                         {

                              Alert.show("Data could not be fetched.","Error");

                         }

                    }

                     

                    ]]>
                    </mx:Script>

                     


                         <mx:VBox  label="Test 录 Test" width="100%" height="300" verticalGap="0">
                       
                         <mx:DataGrid width="327" height="100%" alternatingItemColors="[#eeeeee, #cccccc]"  dataProvider="{acSQLiteData}">
                             <mx:columns>
                                 <mx:DataGridColumn headerText="Col1" dataField="Col1"/>
                                 <mx:DataGridColumn headerText="Col1" dataField="Col2"/>
                             </mx:columns>
                         </mx:DataGrid>
                             </mx:VBox>

                     

                     

                     

                     

                    </ContentPanel>

                     

                     

                     

                     

                     

                     

                     

                     

                    We all know that's wrong but i didn't see who i could adapt it to make it work!>!@

                     

                     

                    any other help would be grandly apreciated, thanks

                    • 7. Re: Populating a Datagrid with Data from AIRSQL
                      Francisc Level 3

                      Well you need to declare the sqlConnection var and import the class...

                      It sounds complicated but it isn't...

                       

                      Look, if you send me the ZIP file of the exported project, I can add the code for it to work.

                      It's hard like this to troubleshoot.

                       

                      Let me know.

                      • 8. Re: Populating a Datagrid with Data from AIRSQL
                        Foxchx Level 1

                        the application is quite big (at the newB level i am files and duplicates all over the place, hehe ) that's why i didn't attached it before and just gave you the code at hand...

                         


                        I am using Employee directory:

                         

                        http://download.macromedia.com/pub/developer/air/sample_apps/EmployeeDirectory.zip

                         


                        Want to populate that grid:

                         

                        http://www.shotnalive.com/filedump/flex/PageListPanel0.mxml

                         


                        and need to populate the grid on a new panel i made I.E. replace the content in InfoPanel.mxml with the accordeon from the PageListPanel0.mxml), it should work for me). Once again the problem is that i need the list to also be clickable so that the employee bio shows on the side when you click on the name like the existing "department" fonction of ED.

                        I got screwed because i wanted to re-use the (department script that is already in place in the "employee directory") but just missing something.

                         

                        ----------------------------
                        I am really bad at explaining myself, so here's the pointer on what i am trying to do.

                         


                        1- populate the data grid with the name and department name(2 col) of all employee in that department.

                         

                        2- allow to click the name and open the employee bio panel (that's already working for the department option of the  "Adobe Employee directory app" but i can't seem to re-use the old code because it has other options that don't allow me to make it work for some reason!?)

                         

                        3- so basically my database i have a column that list the 5 departments. So if an employee has "department3", the name should show in the "list 3" datagrid of the accordeon... so for all employee with the department 2 would show under the accordeon tab called "list 2" ..... etc...etc

                         


                        Hope you understand and can help me on that, thanks a lot man.