Update Statement using Variable

Jan 27, 2016 at 10:16 PM
Guys

Hope you can help

I'm updating a record using the following

RetVal = SQLite3PrepareV2(myDbHandle, "UPDATE data SET ACTFINDATTIM = 'Data to save' WHERE _id= " + CStr(Worksheets("Sheet1").Range("V5").Value), myStmtHandle)

This works fine but when I try to change the String "data To Save" to the contents of a cell like follows

RetVal = SQLite3PrepareV2(myDbHandle, "UPDATE data SET ACTFINDATTIM = '" + Worksheets("Sheet1").Range("H27").Value + "' WHERE _id= " + CStr(Worksheets("Sheet1").Range("V5").Value), myStmtHandle)

Nothing Gets saved

Any Ideas where I'm going wrong?

Any Help Appreciated

Mark
Coordinator
Jan 28, 2016 at 7:46 PM
Hi Mark,

I'm guessing it's just something silly. Perhaps you can split out the string parts:
Dim newData As String
Dim query As String

newData = Worksheets("Sheet1").Range("H27").Value
query =  "UPDATE data SET ACTFINDATTIM = '" + Worksheets("Sheet1").Range("H27").Value + "' WHERE _id= " + CStr(Worksheets("Sheet1").Range("V5").Value

' Now you can Debug.Print these variable to check that they look right.
The better way to do this eventually is to use parameters that you bind.
Otherwise you have to be very careful in escaping the strings. See: https://xkcd.com/327/

-Govert
Jan 28, 2016 at 9:35 PM
Hi Govert

You were right it was something silly

The string I was trying to use from the cell was a file name and it had an " ' " in the file name

Removed it and it works fine

Thanks for pointing me in the right direction


Mark