B4J Question B4XTable DB Query Error

[mangojack]

We have a B4XTable that on Table_CellClick presents a ChoiceBox.
On Making a selection the Cell is updated and the internal Data DB is Updated.

I have tried to tidy the Query using Smart String Literals which then Errors out .. stating "there is no Column Named " the ChoiceBox Selection Value"

The query Syntax appears correct , Can someone point out what I am doing wrong.

Sub B4XTableM_CellClicked (ColumnId As String, RowId As Long)
    Dim column As B4XTableColumn = B4XTableM.GetColumn(ColumnId)  
    Dim value As String = B4XTableM.GetRow(RowId).Get(ColumnId)  
    Dim visibleRowID As Int = (B4XTableM.VisibleRowIds.IndexOf(RowId))  
  
    'Global Vars
    ClickedRow=RowId
    ClickedCol=column.SQLID
'...... more code

Sub cBox_SelectedIndexChanged(Index As Int, Value As Object)
  
    'Value = "Weekly" !!!!
  
    'OK Working
    'B4XTableM.sql1.ExecNonQuery2($"UPDATE data SET ${ClickedCol} = ? WHERE rowid = ?"$, Array As String(Value, ClickedRow))
      
    'OK Working
    'Dim myquery As String = $"UPDATE data SET ${ClickedCol} = ? WHERE rowid = ?"$
    'B4XTableM.sql1.ExecNonQuery2(myquery, Array As String (Value, ClickedRow))
  
    'OK Woking
    'Dim myquery As String = "UPDATE data SET " & ClickedCol & " = '" & Value & "' WHERE rowid = " & ClickedRow
    'B4XTableM.sql1.ExecNonQuery(myquery)


  
    'ERROR !!!
    java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (no such column: Weekly) !!! Weekly , OR whatever Item is Choosen from ChoiceBox
  
    Dim myquery As String = $"UPDATE data SET ${ClickedCol} = ${Value} WHERE rowid = ${ClickedRow}"$
    B4XTableM.sql1.ExecNonQuery(myquery)  
  
  
    B4XTableM.Refresh
  
End Sub

Many Thanks

 

[DonManfred]

You query is missing quotes.

What about parametrized query?

 

[mangojack]

Thanks Don ... I was about to post , yes I can use parameterized query ... but It was more to know why the last query does not work

You query is missing quotes.

?? This was why I am attempting to use smart string.

ie: why does the 1st query work ?

Thanks

 

[DonManfred]

ie: why does the 1st query work ?

this one?

'OK Working
'B4XTableM.sql1.ExecNonQuery2($"UPDATE data SET ${ClickedCol} = ? WHERE rowid = ?"$, Array As String(Value, ClickedRow))

you are using parametrized query here. quotes are set automatically by sql object.

 

[mangojack]

you are using parametrized query here. quotes are set automatically by sql object.

Yes Understood ... But I thought Smart String Literal would look after that as well ?? as in the query that causes error . .... No ?

 Dim myquery As String = $"UPDATE data SET ${ClickedCol} = ${Value} WHERE rowid = ${ClickedRow}"$
    B4XTableM.sql1.ExecNonQuery(myquery)

Thanks

 

[DonManfred]

Dim myquery As String = $"UPDATE data SET ${ClickedCol} = ${Value} WHERE rowid = ${ClickedRow}"$

asuming Value is a Stringvalue:
try

Dim myquery As String = $"UPDATE data SET ${ClickedCol} = '${Value}' WHERE rowid = ${ClickedRow}"

 

[DonManfred]

But I thought Smart String Literal would look after that as well ?

No. Smart String Literal are not adding any quotes or something.

 

[mangojack]

asuming Value is a Stringvalue:
try

Dim myquery As String = $"UPDATE data SET ${ClickedCol} = '${Value}' WHERE rowid = ${ClickedRow}"

Yes that worked . A little confused now as I would have thought using Smart String would have alleviated the need for the 'quotes' ...

** Another very good reason why Parameterized Queries should be used

Cheers

 

[DonManfred]

Another very good reason why Parameterized Queries should be used

Fully aggree! ;-)