Update certain rows in datagrid from database

[Guest]

Hi guys

Thanks for answering a couple of my other questions in the last day or so - I haven't got to properly going through them as I have been working on my database/datagrid interaction, and it's been all consuming.  I'm almost done, but...

...I'm wondering what the best way is to retrieve certain rows from a database, and use them to overwrite the related rows in a datagrid?

My datagrid's dataprovider looks like...

song_id, title, artist, ...

... where song_id corresponds to the song_id primary key column in my database.

Rather than repopulate the whole datagrid by selecting all the rows in the database, I'm selecting certain rows using WHERE IN ( 2, 6, 77, 999... ) in my query, and am wondering how to use to the song_id in the returned data to find the correct row to overwrite in the datagrid.

I'm unsure how to fill in the blanks...

var L = returnedDatabaseResultsArray.length;// length of array of objects returned by my WHERE IN ( 2, 6, 77, 999... )

for (var i:int = 0; i < L; i++)

{

        var _rowNumber:int = returnedArray(i).song_id;//extract song_id out of this row of returned data

        //find the row in the dataprovider that contains song_id, then (what goes here?)...

    data_grid.dataProvider.replaceItemAt( returnedDatabaseResultsArray(i), _rowNumber );// overwrite that row with this row from database

}

Cheers guys

[kglad]

what are the elements of returnedArray?

are they row contents from your datagrid?  if so, does your datagrid contain song_id?  if not (because you don't want to display it), you can add it to your datagrid and not display it.

[Guest]

returnedArray contains row data for all the columns in my database, which correspond exactly to my dataprovider.

song_id is not displayed in the datagrid, but is in the dataprovider, and sorresponds with the song_id PK in the database

To make it a little clearer, when I'm booting up the app, I select ALL the rows from my database, and populate my dataprovider with the results like this...

function onSelected(evt:SQLResult):void

        {

            if (evt.data != null)

            {

                data_grid.dataProvider = new DataProvider(evt.data);   //{song_id:1212, Title:"Unbreakable", Artist:"Tough Guy".. etc}            

            }

            conn.close();

        }

What I'm looking for now is a way to load specific rows from the database, and, using their song_id property, overwrite the corresponding rows in the datagrid.

Thanks for you help Keith

[kglad]

i still don't understand.

you have a list of song_id's and you want to query the database to return the title,artist corresponding to those song_id's and then use those data to populate a datagrid?

if so, what's the problem?  do you want to do that without querying the database by using the already returned data of all song_id's and their corresponding title,artist?

[Guest]

It's part of my update process. Here's an example of an edit (note that I have a few more columns than SongID, Artist, Title - but I was trying to keep it compact  to explain.  This example shows use of another column - Path, which holds the location of a file on the hard drive that relates to songs in the datagrid. Not all songs in the datagrid have Path file info associated )...

Example (is set up as a set of functions in a recursive loop, and can cater for multiple row edits at one time):

- user makes an edit to the artist in the datagrid at row 4 and row 5 (multi-edit)

(Recursion starts)

- ROW 4 EDIT

- row 4 has file path data attached, so renameFunc() is called, which extracts the Path data, plus the new user inputted data from the DG to use to rename the file. File renaming has up to four different stages to complete to cover a) a plain case change, and b) the fact that in some files have another matching file with a different extension which needs to be renamed the same.

- if file/s successsfully renamed, all the row data for DG row 4 is placed as an object into an array - updateArray (to be sent soon to the database)

- regardless of fail or success, song_id and datagrid row id are placed as an oject into another array - selectArray {song_id:445435, dgRow:3300}

- if any stage of the file rename fails, file is renamed back to original

- ROW 5 EDIT

- row 5 doesn't have a file associated with it, so the edited datagrid row data is simply placed as an object into updateArray

(Recursion ends)

- at end of resursion, updateArray is sent to SQL function to be input using parametized queries (recursively looped through is there had have been more that one row to update)

NEXT- my plan was, after updating the database with the changed data, to use selectArray to select the rows in the database for ALL atttempted updated DG rows, regardless if the edits were successul or not, and use this data to overwrite the related rows in the datagrid.

I thought this approach would be easier than trying to undo any user inputted data in the DG.  User would then get a results prompt with a list of any rows not updated to to error.