0

I created this table

"CREATE TABLE IF NOT EXISTS Inscripcion (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
    hash TEXT, 
    codigo_evento TEXT, 
    ticket TEXT, 
    nombre TEXT, 
    inscripcion_id TEXT, 
    validado int, 
    sincronizado int, 
    numero TEXT, 
    asiento TEXT, 
    adicionales TEXT, 
    otros TEXT ,
    categoria TEXT, 
    codigo_usuario TEXT, 
    rut TEXT, 
    talla TEXT, 
    fecha_validacion TEXT,
    nombre_responsable TEXT
)";

I'm getting from my Web Service data to insert into my database and do it well:

String sql = "INSERT INTO 'Inscripcion' VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
SQLiteStatement statement = db.compileStatement(sql);
db.beginTransaction();
for (int p = 0; p < array.length(); p++) {
    statement.clearBindings();
    statement.bindString(1, rowParticipante.getString("hash"));
    statement.bindString(2, rowParticipante.getString("codigo_evento"));
    statement.bindString(3, rowParticipante.getString("ticket"));
    statement.bindString(4, rowParticipante.getString("nombre"));
    statement.bindString(5, rowParticipante.getString("inscripcion_id"));
    statement.bindString(6, rowParticipante.getString("validado"));
    statement.bindString(7, "0");
    statement.bindString(8, rowParticipante.getString("numero"));
    statement.bindString(9, rowParticipante.getString("categoria"));
    statement.bindString(10, rowParticipante.getString("asiento"));
    statement.bindString(11, rowParticipante.getString("otros"));
    statement.bindString(12, rowParticipante.getString("adicionales"));
    statement.bindString(13, codigoUser);
    statement.bindString(14, rowParticipante.getString("rut"));
    statement.bindString(15, rowParticipante.getString("talla"));
    statement.bindString(16, rowParticipante.getString("fecha_validacion"));
    statement.bindString(17, rowParticipante.getString("nombre_responsable"));
    statement.execute();
}
db.setTransactionSuccessful();  
db.endTransaction();

I guess this is fine, but I get the following error:

08-20 11:45:58.645: E/SQLiteLog(20145): (1) table Inscripcion has 18 columns but 17 values were supplied

I know it has 18 fields, but I have to add only 17, since the first, the id is auto incrementing. How could resolve this error?

Improve this question

2 Answers 2

Reset to default
2

In SQL INSERTs, if you don't specify which fields you're inserting your values into, you have to provide values for ALL of the fields.

e.g. in a simple table with fields (a,b,c):

INSERT INTO yourtable VALUES ('x', 'y', 'z'); // OK
INSERT INTO yourtable VALUES ('x', 'y'); // NOT ok

for the not ok version, how is the DB to know WHICH fields you want those values to go into? maybe it's a=>x, c=>y, or c=>x, b=>y

Your query should be

INSERT ... (field1, field2,... field17) VALUES (value1, value2, ... value17)
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

So how could I add the ID in this case?
generally speaking you don't. you let the DB assign one for you, and retrieve it after the insert is done.
@sioesi. NO. As Marc has shown, you start from the field#1 (your id would be listed as field#0)
you could simply provide a null in the values list, ... values (null,?,?,?), but in general, NOT providing a field list is bad practice. if you later change the structure of your table and add/remove fields, your unspecific inserts are going to fail or insert data into the WRONG fields. you should always provide a field list.
Plus, if you don't include the field list in the insert, then it's harder to understand your own code.
-1

Try to insert null as the first value. id will automatically increment and other 17 values will be written into the table. 18 total

Improve this answer

8 Comments

You don't have to insert any value for the autoincrement id. @MarcB already gave the solution, which is to provide the INSERT command full syntax, including the field names.
With full syntax query is going to be large and not convenient as there are 18 columns to insert + 18 values. You can just avoid it by inserting NULL into id column. It will work fine. I don't get whats wrong with such approach?
it's not a "wrong" answer, but it is a bad answer. not providing a field list is bad practice, no matter how you look at it.
Please, give me some explanation why it's bad practice? What consequences may be with such "bad" practice?
what if your table structure changes? you remove one field, add another, now your field list in the insert won't match up anymore, and you could be inserting incorrect data into a field - if there's no data-type mismatch in the rearranged data, you won't get warned, you'll just end up with bad data.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.