Problem with INSERT in sqlite3

Databases
1

I sqlite db with the following fields

id (AutoNumber)
date (date)
Account (text)
Owner (text)
cbu (text)
balance (double)

bancos.s3db called the db and table accounts

the following code:

Dim dbFile As FolderItem
Dim db As New SQLiteDatabase

dim f as string
dim c as string
dim p as string
dim cb as string
dim s as double

dbFile = GetFolderItem(“c:\PruebaXojo\bancos.s3db”)
db.DatabaseFile = dbFile
If db.Connect Then

f=trim(textfield1.text)
c=trim(textfield2.text)
p=trim(textfield3.text)
cb=trim(textfield4.text)
s=val(textfield5.text)

db.SQLExecute("BEGIN TRANSACTION")
db.SQLExecute ("INSERT INTO cuentas (fecha,cuenta,propietario,cbu,saldo) VALUES "_
+"('f','c','p','cb',s)")
If db.Error then
  MsgBox("Error: " + db.ErrorMessage)
  db.Rollback
Else
  db.Commit
End If

Else
MsgBox("The database couldn’t be opened. Error: " + db.ErrorMessage)
End If

the following code:

I get the following error and does not generate the record, any ideas?

Error: no Such column s

thank’s

2

db.SQLExecute (“INSERT INTO cuentas (fecha,cuenta,propietario,cbu,saldo) VALUES (‘f’,‘c’,‘p’,‘cb’,s)”)
tries to insert ‘f’, ‘c’, ‘p’, ‘cb’, and ‘s’

not the VALUES in those variable in your program

change your code to

dim sql as string ="INSERT INTO cuentas (fecha,cuenta,propietario,cbu,saldo) VALUES "_ +"('f','c','p','cb',s)" break db.SQLExecute ( sql )
and you’ll see what I mean

what you probably really want is a Prepared Statement which WILL take the values held IN the variables

  • note this is 100% written in the forum and untested but its the right idea -

[code]f=trim(textfield1.text)
c=trim(textfield2.text)
p=trim(textfield3.text)
cb=trim(textfield4.text)
s=val(textfield5.text)

db.SQLExecute(“BEGIN TRANSACTION”)
dim ps As SQLitePreparedStatement = db.Prepare( “INSERT INTO cuentas " +_
(fecha,cuenta,propietario,cbu,saldo) VALUES ?, ?, ?, ?, ?)”)

ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(3, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(4, SQLitePreparedStatement.SQLITE_INTEGER)

ps.insert( f, c, p, cb, s )
If db.Error then
MsgBox("Error: " + db.ErrorMessage)
db.Rollback
Else
db.Commit
End If
Else
MsgBox("The database couldn’t be opened. Error: " + db.ErrorMessage)
End If
[/code]

3

You need to create your sql via string concatenation.

db.SQLExecute (“INSERT INTO cuentas (fecha,cuenta,propietario,cbu,saldo) VALUES “_
+”(’+ f +”’,’"+ c +"’,’"+p+’,’"+cb+"’,+str(s)+")")

Edit: Norm’s approach is better, of course.

4

but the table is numeric

5

the compiler tells me the item does not exist ps.insert( f, c, p, cb, s )

f=trim(textfield1.text)
c=trim(textfield2.text)
p=trim(textfield3.text)
cb=trim(textfield4.text)
s=val(textfield5.text)

db.SQLExecute(“BEGIN TRANSACTION”)
dim ps As SQLitePreparedStatement = db.Prepare( “INSERT INTO cuentas " +_
(fecha,cuenta,propietario,cbu,saldo) VALUES ?, ?, ?, ?, ?)”)

ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(3, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(4, SQLitePreparedStatement.SQLITE_INTEGER)

ps.insert( f, c, p, cb, s ) ------------> error: this item does not exist

If db.Error then
MsgBox("Error: " + db.ErrorMessage)
db.Rollback
Else
db.Commit
End If
Else
MsgBox("The database couldn’t be opened. Error: " + db.ErrorMessage)
End If

6

Try changing

ps.insert( f, c, p, cb, s ) ------------> error: this item does not exist

to

ps.sqlexecute( f, c, p, cb, s )

7

perfect, it worked, but the variable s taking me as whole, ie if you put me 23.78 only generates the table field 23

8

Ready as I found it, lol at the end trial and error but your help, thank you very much everyone.

Change the last field constant

ps.BindType(3, SQLitePreparedStatement.SQLITE_DOUBLE)

9

hence why I said this

10

Perdon norman, no hablo ingles, estoy usando el traductor de google, lo que quise decir es muchas gracias por la ayuda

11

Eso nos hace como yo no hablo espaol
De nada