Help required with SQLite syntax (attached db)
Code:
Dim cOld As cConnection
Set cOld = New cConnection
m_CN.AttachDataBase "D:\backup.db", "old"
m_CN.Execute "UPDATE main.stuff SET " & _
"main.resultsfrombatchaligner=old.resultsfrombatchaligner," & _
"main.acatranscriptionforbatchaligner=old.acatranscriptionforbatchaligner," & _
"main.f0text=old.f0text," & _
"main.pmtext=old.pmtext " & _
"WHERE main.rowid=old.rowid"
Debug.Assert m_CN.AffectedRows > 0
m_CN.DetachDataBase "old"
I am getting the error "Cannot compile SQL-Statement: near ".": syntax error".
What I want to do it restore the value of the columns resultsfrombatchaligner, acatranscriptionforbatchaligner, f0text and pmtext as I had accidentally set them to ''.
No other harm was done to the db, so I wanted to just restore these values using an older backup db.
Thank you for any help as I don't see my mistake.
Thank you.
Re: Help required with SQLite syntax (attached db)
You have to take care with the explicit naming of columns.
The prefixed namespaces "main" and "old" refer to DBs (not Tables).
A full name-referenc to a column needs to be given via DBName.TableName.ColumnName -
and not (as currently) DBName.ColumnName.
Olaf
Re: Help required with SQLite syntax (attached db)
Thread moved from VB6 CodeBank, which is for sharing working VB6 code snippets, to the Database Development forum.
Re: Help required with SQLite syntax (attached db)
Olaf, thank you, but the error message does not go away even with this code:
Dim cOld As cConnection
Set cOld = New cConnection
m_CN.AttachDataBase "D:\backup.db", "old"
m_CN.Execute "UPDATE main.stuff SET " & _
"main.stuff.resultsfrombatchaligner=old.stuff.resultsfrombatchaligner," & _
"main.stuff.acatranscriptionforbatchaligner=old.stuff.acatranscriptionforbatchaligner," & _
"main.stuff.f0text=old.stuff.f0text," & _
"main.stuff.pmtext=old.stuff.pmtext " & _
" WHERE main.stuff.rowid=old.stuff.rowid"
Debug.Assert m_CN.AffectedRows > 0
m_CN.DetachDataBase "old"
Re: Help required with SQLite syntax (attached db)
Remove all main. Including the dot
Re: Help required with SQLite syntax (attached db)
Thank you, but that doesn't work either.
Re: Help required with SQLite syntax (attached db)
Does the query work in a SQLite-client, e.g. DB Browser for SQLite?
Re: Help required with SQLite syntax (attached db)
Is this SQL part of a TRIGGER? If so, try changing the "main." to "new.". If that doesn't work, please post a minimally reproducible code sample for us to work with.