Updating a single record

Can I update a single record in a sqLite DB in one shot like…

[code]rs.Edit
rs.Field("W").IntegerValue = rs.Field("W").IntegerValue
rs.Update[/code]

or do I have to do something like…

[code]curTot = rs.Field("W").IntegerValue
curTot = curTot + 1

rs.Edit
rs.Field("W").IntegerValue = curTot
rs.Update[/code]

it can, but I would use a +1 in the first example…

Thanks, Christian. Yea, poor editing on my part. The line should read…

rs.Field("W").IntegerValue = rs.Field("W").IntegerValue + 1

Wanted to know if that worked before testing. Thanks again.

It’s probably cleaner (easier to read and maintain) if you use SQL when possible.

yourdatabase.sqlExecute("UPDATE <table_name> SET w=w+1 WHERE <identification>=<value>") where table_name is the table where the “w” column is, identification=value is the unique id of the record.

A person that doesn’t know Xojo but knows SQL can later read what you did.

Tomas, to me that is neither cleaner nor easier. Beauty is in the eye of the beholder, I guess. Thanks for the suggestion, though.

instead of yourdatabase.sqlExecute(“UPDATE <table_name> SET w=w+1 WHERE =”)

i usually do

dim mySQL as string = “UPDATE <table_name> SET w=w+1 WHERE =”
yourdatabase.sqlExecute(mySQL)

^^ that way if there’s something going wrong with the sql you can breakpoint to see just what’s trying to execute.

Be sure, under all circumstances to properly escape anything you + in a SQL statement. For example,

Dim sql As String = "UPDATE table SET w=w+1 WHERE id=" + Str(id)

is fine, a number doesn’t really need escaped. But:

Dim sql As String = "UPDATE table SET w=w+1 WHERE name=" + name

Is a security hole waiting to be exploited, especially in WebApps. The above should be written as a rs.Edit/rs.Update statement or a prepared statement only.

Dim ps As PreparedSQLStatement = db.Prepare("UPDATE table SET w=w+1 WHERE name=?")
If ps = Nil Then
    // Look at/report db.Error and db.ErrorMessage
    Return
End If
ps.SQLExecute(name)

Looking at db.ErrorMessage should give you the information you need to fix the problem. I have found rs.Edit/rs.Update much less error prone than hand crafting SQL, and I love SQL.