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

[Espinosa4]

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.

    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]

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]

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]

So the creation of the table accepts the Date type?

Ok, I try

 

[mangojack]

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

 

[LucaMs]

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]

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

 

[Espinosa4]

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]

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]

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]

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

 

[AndrewW]

Thank you - it works now.