SQLite question

[Ricky D]

I have an SQLite table named Fares with 20 fields.

I have managed to Insert records using dbutils InsertMaps function and it's working nicely.

How do I manage to update the 20 fields? The UpdateRecord function only takes 1 field.

I can't see how this can be done.

I'm used to building a string like

s = "UPDATE Fares SET FareDate=" & FareDate & ",FareHash=" & tFareHash.Text & " WHERE TableID=" & lTableID.Text

then doing mySQL.ExexcuteNonQuery(s)

I tried this approach with the INSERT statement but compiler complained about my string s being too long.

If I built the string for this UPDATE it would be roughly as long as the INSERT one and will fail just like the INSERT failed.

So I managed to use the InsertMaps of dbutils and it worked.

But I can't do anything like that for an update.

Can anyone help?

Ricky

 

[Smee]

I thought that the set query only allowed for one field to be set at a time?

 

[klaus]

This code should work, but I haven't tried it with 20 fields.

s = "UPDATE Fares SET FareDate=" & FareDate & ",FareHash='" &  tFareHash.Text & "' WHERE TableID=" & lTableID.Text
mySQL.ExecNonQuery(s)

If the fields are text fields the values must be between quotes. You can add the other fields in the string.
The method is ExecNonQuery and not ExecuteNonQuery.
If the string is too long, you could update it with two queries.

@Smee
You can update more than one field, see here.

Best regards.

 

[Erel]

I recommend you to use ExecNonQuery2. It is simpler and safer (no issues with string escaping):

   Dim s As String
   s = "UPDATE Fares SET FareDate = ?, FareHash = ?, WHERE TableID = ?"
   SQL.ExecNonQuery(s, Array As Object(FareDate, tFareHash.Text, lTableID.Text))