Basic4Android sql1 update

Question

I am stuck using basic4android as regards updating a record in a sql db (Lover.db). This works:

 SQLLover.ExecNonQuery("UPDATE Profiles SET Mobile ='Nos', " & _
   "Name = 'Nme', Sunday = 'SundayX', Monday = 'MondayX', " & _
   "Tuesday = 'TuesdayX', Wednesday = 'WednesdayX', Thursday = 'ThursdayX', " & _
   " Friday = 'FridayX', Saturday = 'SaturdayX', " & _
   "StartTime = 'lblTimeFrom.text', EndTime = 'lblTimeTo.Text' " & _
   "WHERE Mobile='07xxxxxxxxx' AND Name='Sam'")

But of course the just updates with the data in the single quotes. I want to used some variables, and have tried this;

SQLLover.ExecNonQuery2("Update Profiles SET(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", & _
    Array As Object(Nos, Nme, SundayX, MondayX, TuesdayX, WednesdayX, & _
    ThursdayX, FridayX, SaturdayX, lblTimeFrom.Text, lblTimeTo.Text))

Is this the correct syntax? Of coures, Nos, Nme, SundayX etc are my variables.

score 0 · Answer 1 · answered Jan 19 '13 at 17:09

Your UPDATE syntax is entirely wrong. The correct syntax is UPDATE table SET column = value, column2 = value2 and so forth. You've done nothing even remotely close to that with your code. I'm not going to bother to type out the whole thing, but I'll do enough to get you started:

SQLLover.ExecNonQuery2("Update Profiles " & _
  "SET Mobile = ?, Name = ?, Sunday = ?, Monday = ?", & _
  Array As Object(Nos, Nme, SundayX, MondayX))

score 0 · Answer 2 · answered Feb 12 '16 at 15:18

With your code, the working one, to be able to used variable in the query, you have to escape the query and concatenate the variable. Look at this short example

SQLLover.ExecNonQuery("UPDATE Profiles SET Mobile = ' " & NOs & " ', Name = ' "&NMe & " ' WHERE Name = ' " & OldNameVariable & " ' ")

SO TO USE VARIABLE Insert "& VARIABLENAME &" as in the example (Mobile = ' "& Nos &"')

HINT: The last query should look like this: AND Name=' " & SamOldVariableName & " ' ")

Basic4Android sql1 update

2 Answers2