System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: 376beb269db1a8aa10de6a26f09f4c4b416dbab0
Title: code produces error "database is locked"
Status: Closed Type: Incident
Severity: Important Priority: Medium
Subsystem: Transaction Resolution: Works_As_Designed
Last Modified: 2014-06-22 19:13:10
Version Found In: 1.0.92.0
User Comments:
anonymous added on 2014-06-22 06:06:14:
I've a form that put some values into a database. The database was previously populated with data. The thing is that the insertion works only the first time. From the second and onwards the code produces a "database is locked" error. Don't know if my fault (not great coder) or a bug.

The offending code is this:
Using objConexion As New SQLiteConnection
                objConexion.ConnectionString = cadena_conexion
                objConexion.Open()
                Using objComando As SQLiteCommand = objConexion.CreateCommand
                    Using trans As SQLiteTransaction = objConexion.BeginTransaction
                        objComando.CommandText = "INSERT INTO clientes " & _
                                              "(ID_cliente, STR_nombre, IT_RUT, STR_direccion, IT_fecha_nacimiento, ID_region, ID_comuna, IT_fono_fijo, " & _
                                              "IT_fono_movil, IT_email, ID_captador, IT_fecha_ingreso, IT_fecha_instalacion, IT_fecha_contrato, IT_obs) " & _
                                         "VALUES " & _
                                              "( NULL , @cliente, @rut, @direccion, @fecha_nac, @id_reg, @id_com, @tel_fijo_1, " & _
                                              "@tel_cel_1, @email_1, @capt, @fecha_ingreso, @fecha_inst, @fecha_cont, @obs)"
                        'hora de llenar los parametros
                        objComando.Parameters.AddWithValue("@cliente", cliente)
                        objComando.Parameters.AddWithValue("@rut", rut)
                        objComando.Parameters.AddWithValue("@direccion", direccion)
                        objComando.Parameters.AddWithValue("@fecha_nac", f_nac)
                        objComando.Parameters.AddWithValue("@id_reg", region)
                        objComando.Parameters.AddWithValue("@id_com", comuna)
                        objComando.Parameters.AddWithValue("@tel_fijo_1", tel_fijo)
                        objComando.Parameters.AddWithValue("@tel_cel_1", tel_cel)
                        objComando.Parameters.AddWithValue("@email_1", email)
                        objComando.Parameters.AddWithValue("@capt", captador)
                        objComando.Parameters.AddWithValue("@fecha_ingreso", f_ing)
                        objComando.Parameters.AddWithValue("@fecha_inst", f_ins)
                        objComando.Parameters.AddWithValue("@fecha_cont", f_cto)
                        objComando.Parameters.AddWithValue("@obs", obs)
                        objComando.ExecuteNonQuery()
                        trans.Commit()
                    End Using
                End Using
            End Using

and the exception is thrown in the "trans.Commit()" line. and a file "aguas.db-journal" is written to disk. The program is freeze about 1 minute and the exception is thrown

I'm uploading a reduced version of the project (Visual Basic .net, on visual studio 2013). This produces the exception on my windows 8.1 machine and my windows 7 notebook. Fair Warning: The code is horrible, i'm vb beginner and self-taught. The source file is in clientes.vb, and the database is aguas.db. i tried to modify the form to be self-explanatory  then just press the button, will call another form, fill it (where it says RUN, insert 4 or 5 zeros), and press "ingresar cliente" button. The first time will work, the next won't.

file is around 21 mb. hosted in mega (if this is a problem, i can upload somewhere else, but where?)

https://mega.co.nz/#!kMAwlY6J!rf_NxtqhvjLFZwvY1QLLxnUtQBbXaLuYmoVAgDu_pWA

mistachkin added on 2014-06-22 15:46:12: (text/x-fossil-plain)
After creating a command, you need to set the Transaction property (when you are
using transactions), e.g.:

Using objConexion As New SQLiteConnection
    objConexion.ConnectionString = cadena_conexion
    objConexion.Open()
    Using objComando As SQLiteCommand = objConexion.CreateCommand
        Using trans As SQLiteTransaction = objConexion.BeginTransaction
            objComando.Transaction = trans ' NOTE: This is new.
            objComando.CommandText = "INSERT INTO clientes " & _
                                  "(ID_cliente, STR_nombre, IT_RUT, STR_direccion, IT_fecha_nacimiento, ID_region, ID_comuna, IT_fono_fijo, " & _
                                  "IT_fono_movil, IT_email, ID_captador, IT_fecha_ingreso, IT_fecha_instalacion, IT_fecha_contrato, IT_obs) " & _
                             "VALUES " & _
                                  "( NULL , @cliente, @rut, @direccion, @fecha_nac, @id_reg, @id_com, @tel_fijo_1, " & _
                                  "@tel_cel_1, @email_1, @capt, @fecha_ingreso, @fecha_inst, @fecha_cont, @obs)"
            'hora de llenar los parametros
            objComando.Parameters.AddWithValue("@cliente", cliente)
            objComando.Parameters.AddWithValue("@rut", rut)
            objComando.Parameters.AddWithValue("@direccion", direccion)
            objComando.Parameters.AddWithValue("@fecha_nac", f_nac)
            objComando.Parameters.AddWithValue("@id_reg", region)
            objComando.Parameters.AddWithValue("@id_com", comuna)
            objComando.Parameters.AddWithValue("@tel_fijo_1", tel_fijo)
            objComando.Parameters.AddWithValue("@tel_cel_1", tel_cel)
            objComando.Parameters.AddWithValue("@email_1", email)
            objComando.Parameters.AddWithValue("@capt", captador)
            objComando.Parameters.AddWithValue("@fecha_ingreso", f_ing)
            objComando.Parameters.AddWithValue("@fecha_inst", f_ins)
            objComando.Parameters.AddWithValue("@fecha_cont", f_cto)
            objComando.Parameters.AddWithValue("@obs", obs)
            objComando.ExecuteNonQuery()
            trans.Commit()
        End Using
    End Using
End Using

anonymous added on 2014-06-22 19:13:10: (text/x-fossil-plain)
Still doesn't work. Produces the same error. Changed the code to include the line you indicated in each transaction, in all transactions of the file. Doesn't work.

A workaround i found half hour ago is not to use any prepared sentence nor transactions when dealing with INSERTs. 

That is i just insert the data the direct way:

objComando.CommandText = "INSERT INTO clientes (ID_cliente, ... , IT_obs) VALUES ( NULL , " & string_var_cliente & " ... " & "string_var_obs)"

and removing all the prepared sentenced and the transaction seems to work just fine. This on both machines.

Since you closed this, i think i'm just use unprepared and not enclosed in any transactions sentences. seems to work just fine.