I am trying to update a table with the following code.
I debug and step through.
It connects, retrieves data, sets the field in the DB buffer.
But when I try to update SQL, it says:
Result Set is not updateable, must be singleton select and the affected table must have a primary key.
All the variables are set properly

Code:
DatabaseConnect("myDBprofile")
DatabaseRetrieve "select * FROM myTable WHERE id = 1", sFiller
DatabaseSet 1, 2, smyPath
DatabaseGet 1,2, sFiller
DatabaseUpdate "INSERT", sFiller
DatabaseDisconnect

Make sure your table has a primary key. Without the primary key the created data set is not updatable.

: I am trying to update a table with the following code.
: I debug and step through.
: It connects, retrieves data, sets the field in the DB buffer.
: But when I try to update SQL, it says: Result Set is not updateable, must be
: singleton select and the affected table must have a primary key.
: All the variables are set properly

: Code: DatabaseConnect("myDBprofile")
: DatabaseRetrieve "select * FROM myTable WHERE id = 1", sFiller
: DatabaseSet 1, 2, smyPath
: DatabaseGet 1,2, sFiller
: DatabaseUpdate "INSERT", sFiller
: DatabaseDisconnect

: Make sure your table has a primary key. Without the primary key the created
: data set is not updatable.

Yes, it has a primary key. It has 3 fields. ID (primary key), file_name, and date.
I am trying to set file_name. There is only one record in the DB.
In addition, I have verified that user I am connecting as has DBO rights.

: Make sure your table has a primary key. Without the primary key the created
: data set is not updatable.

What does Result set not updateable mean? Is is the buffer DB or the SQL db?
What is a singleton select? I only have 1 record open and one field modified.
Is my databaseupdate syntax correct? Shouldn't it just update the one field?

In order to update a table 24x7 must know the key of that table. The way the update works, 24x7 construct the proper UPDATE [table] WHERE [key here] SQL command. If there is no key it does not know who to construct the SQL and so it says the recordset is not updateable.

Are you the owner of that table? Do the user and schema names match?

: Yes, it has a primary key. It has 3 fields. ID (primary key), file_name, and
: date.
: I am trying to set file_name. There is only one record in the DB.
: In addition, I have verified that user I am connecting as has DBO rights.

: In order to update a table 24x7 must know the key of that table. The way the
: update works, 24x7 construct the proper UPDATE [table] WHERE [key here]
: SQL command. If there is no key it does not know who to construct the SQL
: and so it says the recordset is not updateable.

: Are you the owner of that table? Do the user and schema names match?

Yes, the user is owner of the table. The user and the schema names match.

How do you pass the arguments to the databaseUpdate function?
I originally put:
DatabaseUpdate "INSERT", sFiller
I also put UPDATE as well with the same results. Is this syntax correct?

my original query was:

DatabaseRetrieve "select * FROM myTable WHERE id = 1", sFiller

id is the primary key in that table.

You don't pass any arguments to the update, other then the desired update method and you get one argument back which is how many rows were updated, inserted, and/or deleted during the DatabaseUpdate call.
24x7 automatically constructs the proper update SQL with bind variables and for UPDATE/DELETE statements it automatically construct the WHERE part based on the table primary key or unique index definition.

It can update/insert/delete multiple table rows in a single DatabaseUpdate call, depending on how many rows were updated/inserted/deleted in the database buffer.

I don't know why in your case it doesn't see the table as updateable. Since you are updating just one row, I suggest you use the following instead of DatabaseUpdate

Dim(nRows, number)
Dim(sSQL, string)
DatabaseConnect("myDBprofile")
ConcatEx("UPDATE myTable SET [column]='", smyPath, "' WHERE id = 1", sSQL)
DatabaseExecute(sSQL, nRows)
DatabaseDisconnect

: Yes, the user is owner of the table. The user and the schema names match.

: How do you pass the arguments to the databaseUpdate function?
: I originally put: DatabaseUpdate "INSERT", sFiller
: I also put UPDATE as well with the same results. Is this syntax correct?

: my original query was: DatabaseRetrieve "select * FROM myTable WHERE id
: = 1", sFiller

: id is the primary key in that table.

That worked perfectly! Many thanks!

: You don't pass any arguments to the update, other then the desired update
: method and you get one argument back which is how many rows were updated,
: inserted, and/or deleted during the DatabaseUpdate call.
: 24x7 automatically constructs the proper update SQL with bind variables and
: for UPDATE/DELETE statements it automatically construct the WHERE part
: based on the table primary key or unique index definition.

: It can update/insert/delete multiple table rows in a single DatabaseUpdate
: call, depending on how many rows were updated/inserted/deleted in the
: database buffer.

: I don't know why in your case it doesn't see the table as updateable. Since
: you are updating just one row, I suggest you use the following instead of
: DatabaseUpdate

: Dim(nRows, number)
: Dim(sSQL, string)
: DatabaseConnect("myDBprofile")
: ConcatEx("UPDATE myTable SET [column]='", smyPath, "' WHERE id
: = 1", sSQL)
: DatabaseExecute(sSQL, nRows)
: DatabaseDisconnect