RowSet.edit

General
1

the documentation says:

Be sure the SQL includes the primary key column

It means that a row of a table without a Primary Key cannot be edited?
Should another procedure to be used?

image

2

Use a SQL UPDATE prepared statement.

var updateSql as string = _
    "UPDATE my_table " + _
    "SET col1 = ?, col2 = ? " + _
    "WHERE condition"
db.ExecuteSQL updateSql, val1, val2
3

Something I’m doing wrong:

Public Sub UpdateSQL(taula as string, field as string, valor as string, index as string)
  
  var updateSql as string = "UPDATE " + taula  +  " SET " + field  + "= "+ valor + " LIMIT 1 OFFSET " + index
  db.ExecuteSQL updateSql

End Sub

Can you see where the mistake is?

Taula is the table name

Index is the listBox index, as reading the table records were passed to a listBox, so the first row (0) is the first record from the table.

Field is the name of the column to be updated

valor is the new value for that field

I just want to modify a field of the record.

4

using tables without unique primary keys can only lead to problems …

I’ve seen some commercial accounting apps with a database using no auto primary keys
(but still having a “id” field I suppose the app deals itself with the numbering scheme ?)
I still don’t understand the purpose of it
If anyone can explain why ?

5

This is a recipe for disaster. Use a prepared statement the way I described it. If you start inserting strings into the statement, you will open yourself to SQL injection.

But the UPDATE statement doesn’t recognize LIMIT or OFFSET, you must use a WHERE clause.

Finally, what @Jean-Yves_Pochez said.

6

It obvious that it’s better to add an ID Primary key to every table, but when opening a DB that is not created by yourself…

If UPDATE doesn’t recognize OFFSET how can I modify a record? I get the Record to be modified:
The following gets a RowSet, having it it seems that I would be able to modify it.

Public Function GetOneRecord(taula as string, value as string) As RowSet
  
  // els records son zero based
  
  var sql as string = "SELECT * from "
  sql = sql + taula
  sql = sql + " LIMIT 1 OFFSET "
  sql = sql + value
  sql = sql + ";"
  
  Var rows As RowSet
  try
    rows = db.SelectSQL(sql)
  Catch error As DatabaseException
    MessageBox("Error: " + error.Message)
  End Try
  return rows
End Function
7

Editing a RowSet is just issuing an UPDATE for you in the background, but it needs some way to uniquely identify the record you want to modify. If there is no primary key, it cannot do that.

If this is SQLite, I think you can add rowid to your columns for the primary key, but others would have more thoughts.

BTW, if taula contains "my_table; DELETE FROM my_table; – ", and “my_table” is a valid table, you might be in for a world of hurt. Do not do this.

8

Thanks Kim, you give me the right idea, this works:

Public Sub UpdateSQL(taula as string, field as string, valor as string, index as string)
  
  var updateSql as string = "UPDATE " + taula  +  _
  " SET " + field  + "= ? "+ _
  " Where rowid= " + index +";"
  
  db.ExecuteSQL updateSql,valor
End Sub
9

You need single quotes around valor.

But much better to do what @Kem_Tekinay suggested.