updating databases

[Jan Schenkel]

--- rand valentine <jrvalent at wisc.edu> wrote:
>  Hi, everyone. I have been working at making a
> dictionary database, using
> runrev 2.0.1 and Valentina VXCMD, on Mac OS 9.2.2.
> Now since I am kind of a
> beginner with sql, I have been trying to play it
> safe as far as having
> cursors and that sort of stuff goes . So what I do
> is
> 
> 1. build a simple "select all" query using the
> database query builder, which
> opens the Valentina database.
> 
> 2. I then execute a revDataFromQuery command that
> loads a scrolling field
> with the whole data set.
> 
> 3. Then I click on a line in the scrolling field to
> load that record's field
> values into individual revolution text fields (not
> designated as "database"
> fields in any way).
> 
> 4. I have handlers that record the entry value of a
> given field, and the
> exit value -- if these are different, I then try to
> update the particular
> field in the particular record in the Valentina
> database, which is
> identified by a unique code. To do this, I use a
> revExecuteSQL command that
> contains an UPDATE sql command to update the
> appropriate record in the
> Valentina db.
> 
> But that record never seems to get updated! Why not?
> Am I fundamentally
> wrong in my understanding about something? I have
> also tried using a
> revCommitDatabase instruction following the
> attempted update, but again,
> when i go to the database query builder and
> "refresh" the * query, the
> record i'm trying to change is unchanged. Any help
> would be great
> appreciated.
> 
> rand valentine
> 


Hi Rand,

Have you checked 'the result' after your call to
revExecuteSQL ? If there was something wrong in the
SQL statement, it should say so.
Plus, it might be more interesting for you to use
database-linked fields than rolling your own update
queries. Here's a good trick :
- make your SELECT query using the database query
builder (name it "ListQuery")
- make a new field (name it "List"), and use the
inspector palette to turn it into a table via the
'table' panel
- link the same field to the SELECT query via the
'database' panel, and pick 'Show all' in the column
option menu
- now make another query with the exact same SELECT
statement (name it "RecordQuery") ; make sure the
correct primary key is selected so we can use the
build-in 'update after editing' functionality
- make a few more fields for the database fields of
the individual record you'll be selecting in the
"List" table-field
- to avoid having to write update scripts yourself,
simply hit the checkbox "Update after editing"
- now set the script of the "List" field to :
  on selectionChanged
    put the hilitedLine of me into tRecordNumber
    revDBQueryGoToRecord "RecordQuery", tRecordNumber
  end selectionChanged
- and presto, you can select a line in the "List"
field, and the other fields are updated, and you can
even save the changes !
- one small snag, though : the "List" field isn't
automatically updated ; but that can be easily solved
by putting the following in your card script :
  on closeField
    put the hilitedLine of fld "List" into tOldLine
    revRefreshQuery "ListQuery"
    set the hilitedLine of fld "List" to tOldLine
  end closeField

In this way, with a minimum of coding, you can display
all the records in a table, pick a individual record
in the table, and edit it via separate fields.

Hope this helped,

Jan Schenkel.

=====
"As we grow older, we grow both wiser and more foolish at the same time."  (La Rochefoucauld)

__________________________________
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com