'Updates a single field in a record
'Field is the column name
' - "No Error" in case of successfull
' - "No record to update" in case of query don't match
' - "Error:", with LastException.Message who follows, in case of error
Sub UpdateRecord(SQL As SQL, TableName As String, Field As String, NewValue As Object, _
WhereFieldEquals As Map) As String
SQL.BeginTransaction
Try
If WhereFieldEquals.Size = 0 Then
Return "Error:WhereFieldEquals map empty!"
End If
'Build the query
Dim sb As StringBuilder
sb.Initialize
sb.Append("SELECT count(*) FROM ").Append(TableName).Append(" WHERE ")
Dim args() As String
For i = 0 To WhereFieldEquals.Size - 1
If i > 0 Then sb.Append(" AND ")
sb.Append("[").Append(WhereFieldEquals.GetKeyAt(i)).Append("] = ?")
args(i) = WhereFieldEquals.GetValueAt(i)
Next
'First search if records exist
Dim NumberOfRecord As Int
NumberOfRecord = SQL.ExecQuerySingleResult2(sb.ToString, args)
If NumberOfRecord=0 Then
Return "No record to update"
Else
sb.Initialize
sb.Append("UPDATE [").Append(TableName).Append("] SET [").Append(Field).Append("] = ? WHERE ")
Dim args2 As List
args2.Initialize
args2.Add(NewValue)
For i = 0 To WhereFieldEquals.Size - 1
If i > 0 Then sb.Append(" AND ")
sb.Append("[").Append(WhereFieldEquals.GetKeyAt(i)).Append("] = ?")
args2.Add(WhereFieldEquals.GetValueAt(i))
Next
SQL.ExecNonQuery2(sb.ToString, args)
SQL.TransactionSuccessful
SQL.EndTransaction
Return "No Error"
End If
Catch
Return "Error:" & LastException.Message
End Try
End Sub
