I have created a multiple field update in dbutils

[Ricky D]

I have added this function into the DBUtils on my computer.

It allows you to define a Fields map that holds as many fields for your table as you need to update.

This example code lets me update 2 of the fields in my table

Dim m As Map
m.Initialize

m.Put("FareDate",u.Fare.FareDate)
m.Put("FareAmount",u.Fare.FareAmount)

Dim wf As Map
wf.Initialize
wf.Put("TableID",lTableID.Text)

DBUtils.UpdateRecord2(u.mySQL,"Fares",m,wf)

'write updates to a table with multiple fields in the Fields map
Sub UpdateRecord2(SQL As SQL, TableName As String, Fields As Map, WhereFieldEquals As Map)
   Dim sb As StringBuilder
   sb.Initialize
   sb.Append("UPDATE [").Append(TableName).Append("] SET ")
   If WhereFieldEquals.Size = 0 Then
      Log("WhereFieldEquals map empty!")
      Return
   End If
   Dim args As List
   args.Initialize
   For i=0 To Fields.Size-1
      If i<>Fields.Size-1 Then
         sb.Append("[").Append(Fields.GetKeyAt(i)).Append("]=?,")
      Else
         sb.Append("[").Append(Fields.GetKeyAt(i)).Append("]=?")
      End If

      args.Add(Fields.GetValueAt(i))
   Next
   
   sb.Append(" WHERE ")
   For i = 0 To WhereFieldEquals.Size - 1
      If i > 0 Then sb.Append(" AND ")
      sb.Append("[").Append(WhereFieldEquals.GetKeyAt(i)).Append("] = ?")
      args.Add(WhereFieldEquals.GetValueAt(i))
   Next
   Log("UpdateRecord: " & sb.ToString)
   SQL.ExecNonQuery2(sb.ToString, args)
End Sub

You can copy and paste this function. You can actually use it anywhere - doesn't have to be in DBUtils but I like keeping it there.

regards, Ricky