Android Question I can't insert values into a sqlite table

Espinosa4 · Apr 27, 2014

Hello,

Can you help me please with this code?

I need to write this lines into a sqlite table but isn't possible in the emulator and some tablets and mobile phones. I don't know why.

In this code I don't write sql.begintransaction... But it doesn't work as well.

B4X:

    Try
        sSQL.ExecNonQuery("CREATE TABLE IF NOT EXISTS ConfLaboral(DesdeFecha Date,HastaFecha Date,TipoServicio Char(2),Fijo Char(1))")
        If DateTime.GetYear(DateTime.Now) = "2014" Then
            Dim CC As Cursor
            CC = sSQL.ExecQuery("SELECT * FROM ConfLaboral")
            If CC.RowCount <= 0 Then
                sSQL.BeginTransaction
                Try
                sSQL.ExecNonQuery("INSERT INTO ConfLaboral (DesdeFecha,HastaFecha,TipoServicio,Fijo) VALUES('2014-12-25','2014-12-25','FR','X')")
                sSQL.ExecNonQuery("INSERT INTO ConfLaboral (DesdeFecha,HastaFecha,TipoServicio,Fijo) VALUES('2014-12-24','2014-12-31','R','X')")
                sSQL.ExecNonQuery("INSERT INTO ConfLaboral (DesdeFecha,HastaFecha,TipoServicio,Fijo) VALUES('2014-12-08','2014-12-08','F','X')")
                sSQL.ExecNonQuery("INSERT INTO ConfLaboral (DesdeFecha,HastaFecha,TipoServicio,Fijo) VALUES('2014-12-06','2014-12-06','F','X')")
                sSQL.ExecNonQuery("INSERT INTO ConfLaboral (DesdeFecha,HastaFecha,TipoServicio,Fijo) VALUES('2014-11-01','2014-11-01','F','X')")
                sSQL.ExecNonQuery("INSERT INTO ConfLaboral (DesdeFecha,HastaFecha,TipoServicio,Fijo) VALUES('2014-10-10','2014-10-10','N',NULL)")
                sSQL.ExecNonQuery("INSERT INTO ConfLaboral (DesdeFecha,HastaFecha,TipoServicio,Fijo) VALUES('2014-10-09','2014-10-09','F','X')")
                sSQL.ExecNonQuery("INSERT INTO ConfLaboral (DesdeFecha,HastaFecha,TipoServicio,Fijo) VALUES('2014-08-15','2014-08-15','FR','X')")
                sSQL.ExecNonQuery("INSERT INTO ConfLaboral (DesdeFecha,HastaFecha,TipoServicio,Fijo) VALUES('2014-06-16','2014-08-31','R','X')")
                sSQL.ExecNonQuery("INSERT INTO ConfLaboral (DesdeFecha,HastaFecha,TipoServicio,Fijo) VALUES('2014-05-02','2014-05-02','N',NULL)")
                sSQL.ExecNonQuery("INSERT INTO ConfLaboral (DesdeFecha,HastaFecha,TipoServicio,Fijo) VALUES('2014-05-01','2014-05-01','F','X')")
                sSQL.ExecNonQuery("INSERT INTO ConfLaboral (DesdeFecha,HastaFecha,TipoServicio,Fijo) VALUES('2014-04-28','2014-04-28','FR',NULL)")
                sSQL.ExecNonQuery("INSERT INTO ConfLaboral (DesdeFecha,HastaFecha,TipoServicio,Fijo) VALUES('2014-04-21','2014-04-21','FR',NULL)")
                sSQL.ExecNonQuery("INSERT INTO ConfLaboral (DesdeFecha,HastaFecha,TipoServicio,Fijo) VALUES('2014-04-18','2014-04-18','FR',NULL)")
                sSQL.ExecNonQuery("INSERT INTO ConfLaboral (DesdeFecha,HastaFecha,TipoServicio,Fijo) VALUES('2014-04-17','2014-04-28','R',NULL)")
                sSQL.ExecNonQuery("INSERT INTO ConfLaboral (DesdeFecha,HastaFecha,TipoServicio,Fijo) VALUES('2014-03-16','2014-03-19','R','X')")
                sSQL.ExecNonQuery("INSERT INTO ConfLaboral (DesdeFecha,HastaFecha,TipoServicio,Fijo) VALUES('2014-01-22','2014-01-22','N',NULL)")
                sSQL.ExecNonQuery("INSERT INTO ConfLaboral (DesdeFecha,HastaFecha,TipoServicio,Fijo) VALUES('2014-01-06','2014-01-06','FR','X')")
                sSQL.ExecNonQuery("INSERT INTO ConfLaboral (DesdeFecha,HastaFecha,TipoServicio,Fijo) VALUES('2014-01-02','2014-01-06','R','X')")
                sSQL.ExecNonQuery("INSERT INTO ConfLaboral (DesdeFecha,HastaFecha,TipoServicio,Fijo) VALUES('2014-01-01','2014-01-01','FR','X')")
                sSQL.TransactionSuccessful
                Catch
                    Msgbox("Error al introducir los datos del calendario laboral","Error")
                End Try
                sSQL.EndTransaction
            End If
            CC.Close
        End If
    Catch
        Log(LastException)
    End Try

Thanks to all

LucaMs · Apr 27, 2014

Move the Try statement after sSQL.ExecNonQuery:
you immediately get an error, because in SQLite the Date type does not exists.

http://www.sqlite.org/datatype3.html

Espinosa4 · Apr 27, 2014

Thanks LucaMs,
I've just saw where is the problem and it was an old table without a field when I tested in some mobiles, tablets, etc. The table exist but without the field.

Sorry and many many thanks for your help!

LucaMs · Apr 27, 2014

So the creation of the table accepts the Date type?

Ok, I try

mangojack · Apr 28, 2014

Lucas .. I don't think his table contains a Date Type .. just a string to hold a date.

LucaMs · Apr 28, 2014

Yes, but SQLite accepts that query to create the table. I did not know it.
SQLite has a very personal way, I would say "dangerous"

mangojack · Apr 28, 2014

Sorry .. my bad . Just reread his first post.

Espinosa4 · May 8, 2014

I know always is text but you can create as date and some software when you are editing the table you can see the record as date with a calendar and it is more easy to change values, etc

eps · May 8, 2014

As always, if you wish to troubleshoot SQLite issues you should try using a SQL Editor (I use the Firefox extension) and execute the SQL statement(s) manually.

AndrewW · Nov 7, 2014

A newcomer needing help!

Can you point me at the code to add a new record to an SQL table using variable names instead of values?

Such as : "INSERT INTO tblTickets VALUES( NULL, txtTicketNo, txtTicketColour).

I cannot find any reference to this in B4A manual.

klaus · Nov 7, 2014

SQL1.ExecNonQuery2("INSERT INTO tblTickets VALUES( NULL, ?, ?)", Array As Object(txtTicketNo, txtTicketColour))
or
SQL1.ExecNonQuery("INSERT INTO tblTickets VALUES( NULL,'" & txtTicketNo & "','" & txtTicketColour & "'").

AndrewW · Nov 7, 2014

Thank you - it works now.

Android Question I can't insert values into a sqlite table

Espinosa4

Active Member

LucaMs

Expert

Espinosa4

Active Member

LucaMs

Expert

mangojack

Expert

LucaMs

Expert

mangojack

Expert

Espinosa4

Active Member

eps

Expert

AndrewW

Member

klaus

Expert

AndrewW

Member

Similar Threads

Android Question I can't insert values into a sqlite table

Active Member

Expert

Active Member

Expert

Expert

Expert

Expert

Active Member

Expert

Member

Expert

Member

Similar Threads

Privacy & Transparency

Privacy & Transparency