Hi Tom,
What I do is store the results of the SELECT statement in an array using a form text field. This is the value I update. I create another array using a hidden form field, this will also store the query results. These results, however, will never be updated and I will use them to compare against my other array to determine if values have changed once the form is submitted.
It gets a little trickey after this depending on how dynamic you want to get. If your not worried aboout using a "generic" type of screen, then create an array for each column you have (including the one you'll be updating with, preferrably the primary key). Once the form is submitted you can compare the updateable array with the hidden array (old values) for each column to determine which rows need updated.
I've also used the above method to perform inserts and deletes.
If the form is "generic" then you'll have one array for the following:
updateable values (text),
old values (hidden),
row Id (hidden)-> you need to determine a standard primary key name for each table you create and give it the same name. You'll need to use this to know what row to update (unless you want to use every column in your WHERE clause to know what row to update).,
column name (hidden)-> to determine which row Id.
column count (hidden)-> how many column are in the table.
column position (hidden) -> position of column within column count.
When the form is submitted you can compare the old and new values. Once you've found the ones that have changed, you need to find the ROWID within COLUMNCOUNT by using the column name array and that will be the value you use to update.
I am currently working on inserting and deleting data using this method.
Unfortunatly, the bigger the table the longer it will take to comapre values, this is why I posted my question. Currently my tables are only config tables and are pretty small.
If you want the code for either of these methods, let me know.
Hope this helps.
Thanks
