Android Question datatype mismatch: INSERT INTO table VALUES

[fifiddu70]

Hello everyone, I state that I am not good in sqlite, I'm making a program with 33 fields edittex and be able to save and recall all in a simple way, but when I go to save tells me this error: sqliteexception: datatype mismatch: INSERT INTO table VALUES
see photo,
someone is willing to help me?
other question .... if some fields are empty at the time of the rescue, the database must be in error '.
I trust in you
part of the code

Sub btnnuovo_Click
    If txtlocale.Text="" Then
    Msgbox("Devi prima inserire un locale sulla casella di testo","AVVISO")
    Else
    Dim NewID As Int
    Try
        NewID = SQL1.ExecQuerySingleResult("SELECT max(id) FROM tabella ") + 1
    Catch
        NewID = 1
    End Try   
    SQL1.ExecNonQuery("INSERT INTO tabella VALUES('" & NewID & "','" & txtlocale.text & "','" & txtuser.text & "','"& txtpassword.text  & "','" & txtpiattaforma.Text &"','" & txtincasso.Text &"','"& txtdata.Text & "','" & txtora.Text &  "','"& txtfido.Text & "','" & txtpercentuale.Text & "','" & txtcaricati.Text & "','" & txtresiduo.Text & "','"& txtconsumato.Text & "','" & txtquotanoi.Text & "','" & txtquotalocale.Text & "','" & txtrimanenza.Text & "','" & txtstorico.Text & "','" & txtweb.Text & "','" & txtesattore.Text & "','" & txtnotespesevarie.Text & "','" & txtspesevarie.Text & "','" & txtspesecarburante.Text & "','" & txtaccontoagente.Text & "','" & txtaccontolocale.Text & "','" & txtnotespeselocale.Text & "','" & txtspeselocale.Text & "','" & txtdareagente.Text & "','" & txtdarelocale.Text & "','" & txtnote.Text & "','" & txtpercentualeagente.Text & "','" & txtquotaagente.Text & "','" & txtstoricoquotalocale.Text & "','" & txtstoricoincassi.Text & "')")
       
    Msgbox("Locale aggiunto al database","MEMORIZZATO")
    End If
    'btnleggi_Click
End Sub

 

[ggpanta]

Check the type of your fields in your schema, you probably are inserting TEXT in an INT. You can try changing that fh record to a number, if this works then it means the field type of the second field is an INT or something that doesnt accept TEXT.

 

[klaus]

What type is the ID column ?
You should set it to INTEGER PRIMARY KEY.
And when you insert an entry:
SQL1.ExecNonQuery("INSERT INTO tabella VALUES(Null,'" & txtlocale.text & "','" & txtuser.text...
This will automatically increase the ID by one.
No need to read the max value.

 

[mc73]

You should avoid 'straight' insertions since often you can get in trouble with the type of variables inserted. Instead, try using execNonQuery2 and pass an array of objects with the desired values.

 

[LucaMs]

You should avoid 'straight' insertions since often you can get in trouble with the type of variables inserted. Instead, try using execNonQuery2 and pass an array of objects with the desired values.

I agree. In this way you should have no problems with types.

If it can be useful to you, you can name the ID field (in the table) as _ID: in this case, its value increases automatically (by 1, for default)

P.S.
if you do not have to use SQLite for other reasons, I suggest you use a Map, which you can easily save; or the KeyValueStore by Erel

 

[fifiddu70]

this a part incomplete of my project.
run for see this error

 

[DonManfred]

this a part incomplete of my project.
run for see this error

I dont need to run if i have a look at the "insert sql" and have a look in the datastructure of table "tabella"

You are using an sql

QL1.ExecNonQuery("INSERT INTO tabella VALUES('" & NewID & "','" & txtlocale.text & "','" & txtuser.text & "','"& txtpassword.text  & "','" & txtpiattaforma.Text &"','" & txtincasso.Text &"','"& txtdata.Text & "','" & txtora.Text &  "','"& txtfido.Text & "','" & txtpercentuale.Text & "','" & txtcaricati.Text & "','" & txtresiduo.Text & "','"& txtconsumato.Text & "','" & txtquotanoi.Text & "','" & txtquotalocale.Text & "','" & txtrimanenza.Text & "','" & txtstorico.Text & "','" & txtweb.Text & "','" & txtesattore.Text & "','" & txtnotespesevarie.Text & "','" & txtspesevarie.Text & "','" & txtspesecarburante.Text & "','" & txtaccontoagente.Text & "','" & txtaccontolocale.Text & "','" & txtnotespeselocale.Text & "','" & txtspeselocale.Text & "','" & txtdareagente.Text & "','" & txtdarelocale.Text & "','" & txtnote.Text & "','" & txtpercentualeagente.Text & "','" & txtquotaagente.Text & "','" & txtstoricoquotalocale.Text & "','" & txtstoricoincassi.Text & "')")

With this SQL you insert an amount of fields into a table, yes. BUT you did not look at your structure. the ORDER of your fields in this table.
With your sql-command you give as first fieldvalue the NewID. But the field you are writing to is "Nome_esattore". I believe its wrong ;-)

Here is a copied create-statement for your db shown in Firefox addon "SqliteManager"

CREATE TABLE tabella (Nome_esattore TEXT, Note_spese_varie TEXT, Spese_varie NUMERIC, Spese_carburante NUMERIC, Acconto_agente NUMERIC, Acconto_locale NUMERIC, Note_spese_locale TEXT, Spese_locale NUMERIC, Dare_agente NUMERIC, Dare_locale NUMERIC, Note TEXT, Percentuale_agente TEXT, Quota_agente NUMERIC, Storico_quota_locale NUMERIC, Storico_incassi NUMERIC, Piattaforma TEXT, Rimanenza NUMERIC, Storico_caricati NUMERIC, Caricati NUMERIC, Fido NUMERIC, Consumato NUMERIC, Percentuale TEXT, Quota_locale NUMERIC, Quota_noi NUMERIC, Residuo NUMERIC, Ora TEXT, Data TEXT, Incasso NUMERIC, Web TEXT, id INTEGER PRIMARY KEY, Locale TEXT, User TEXT, Password TEXT)

You have to write your sql in THIS fieldorder if you are using the VALUES-Sommand in your sql...

But like mc73 said; i would NOT suggest doing this in this way cause you are running into a lot of work and errors if you do any changes in this table!!

Try (and possibly better USE) it in this way:

Dim qry As String
   qry = "INSERT INTO tabella SET " _
  &" id="&NewID&", " _
   &" Locale='"&txtlocale.text&"', " _
  &" User='"&txtuser.text&"' "  ' NOTE: Last one do NOT need a comma after...

   SQL1.ExecNonQuery(qry)

I mean: NAME each field in sql with its fieldname instead of using the "VALUES(...)-command.

Or even have a look at mc73s suggestion. that may be a good solution too.

 

[fifiddu70]

DonManfred i use thi modify but non work, i have syntax error

Sub btnnuovo_Click
    If txtlocale.Text="" Then
    Msgbox("Devi prima inserire un locale sulla casella di testo","AVVISO")
    Else
    Dim NewID As Int
    Try
        NewID = SQL1.ExecQuerySingleResult("SELECT max(id) FROM tabella ") + 1
    Catch
        NewID = 1
    End Try   
   
    Dim qry As String
  qry = "INSERT INTO tabella SET " _
  &" id="&NewID&", " _
  &" Locale='"&txtlocale.text&"', " _
    &" User='"&txtuser.text&"', " _
    &" Password='"&txtpassword.text&"', " _
    &" Piattaforma='"&txtpiattaforma.text&"', " _
      &" Incasso='"&txtincasso.text&"', " _
    &" Data='"&txtdata.text&"', " _
  &" Ora='"&txtora.text&"', " _
  &" Fido='"&txtfido.text&"', " _
  &" Percentuale='"&txtpercentuale.text&"', " _
  &" Caricati='"&txtcaricati.text&"', " _
  &" Residuo='"&txtresiduo.text&"', " _
  &" Consumato='"&txtconsumato.text&"', " _
  &" Quota_locale='"&txtquotalocale.text&"', " _
  &" Rimanenza='"&txtrimanenza.text&"', " _
  &" Storico='"&txtstorico.text&"', " _
  &" Web='"&txtweb.text&"', " _
  &" Nome_esattore='"&txtesattore.text&"', " _
  &" Note_spese_varie='"&txtnotespesevarie.text&"', " _
  &" Spese_varie='"&txtspesevarie.text&"', " _
  &" Spese_carburante='"&txtspesecarburante.text&"', " _
    &" Acconto_agente='"&txtaccontoagente.text&"', " _
  &" Acconto_locale='"&txtaccontolocale.text&"', " _
  &" Note_spese_locale='"&txtnotespeselocale.text&"', " _
  &" Dare_agente='"&txtdareagente.text&"', " _
  &" Dare_locale='"&txtdarelocale.text&"', " _
  &" Note='"&txtnote.text&"', " _
    &" Percentuale_agente='"&txtpercentualeagente.text&"', " _
  &" Quotaagente='"&txtquotaagente.text&"', " _
  &" Storico_locale='"&txtstoricoquotalocale.text&"', " _
  &" Storico_incassi='"&txtstoricoincassi.text&"', " _
  SQL1.ExecNonQuery(qry)

 

[DonManfred]

Dim qry As String
qry = "INSERT INTO tabella SET " _
&" id="&NewID&", " _
&" Locale='"&txtlocale.text&"', " _
&" User='"&txtuser.text&"', " _
&" Password='"&txtpassword.text&"', " _
&" Piattaforma='"&txtpiattaforma.text&"', " _
&" Incasso='"&txtincasso.text&"', " _
&" Data='"&txtdata.text&"', " _
&" Ora='"&txtora.text&"', " _
&" Fido='"&txtfido.text&"', " _
&" Percentuale='"&txtpercentuale.text&"', " _
&" Caricati='"&txtcaricati.text&"', " _
&" Residuo='"&txtresiduo.text&"', " _
&" Consumato='"&txtconsumato.text&"', " _
&" Quota_locale='"&txtquotalocale.text&"', " _
&" Rimanenza='"&txtrimanenza.text&"', " _
&" Storico='"&txtstorico.text&"', " _
&" Web='"&txtweb.text&"', " _
&" Nome_esattore='"&txtesattore.text&"', " _
&" Note_spese_varie='"&txtnotespesevarie.text&"', " _
&" Spese_varie='"&txtspesevarie.text&"', " _
&" Spese_carburante='"&txtspesecarburante.text&"', " _
&" Acconto_agente='"&txtaccontoagente.text&"', " _
&" Acconto_locale='"&txtaccontolocale.text&"', " _
&" Note_spese_locale='"&txtnotespeselocale.text&"', " _
&" Dare_agente='"&txtdareagente.text&"', " _
&" Dare_locale='"&txtdarelocale.text&"', " _
&" Note='"&txtnote.text&"', " _
&" Percentuale_agente='"&txtpercentualeagente.text&"', " _
&" Quotaagente='"&txtquotaagente.text&"', " _
&" Storico_locale='"&txtstoricoquotalocale.text&"', " _
&" Storico_incassi='"&txtstoricoincassi.text&"' "
SQL1.ExecNonQuery(qry)

 

[fifiddu70]

sorry i copy and paste your code but not work

 

[Mahares]

You need something like this:

Dim qry As String
Dim MyFields() As Object
MyFields=Array As Object(NewID,txtlocale.text,txtlocale.text,........,txtstoricoincassi.text)   'rest of edittext boxes
qry = "INSERT INTO tabella VALUES (?,?,?....?)"   'Account for all fields
SQL1.ExecNonQuery2(qry,MyFields)

 

[fifiddu70]

qry = "INSERT INTO tabella VALUES (?,?,?....?)"'Account for all fields ????????? whats?

 

[LucaMs]

So many question marks as there are fields to be included in the record, that is, those in the Array myFields, separated by commas.

Tanti punti interrogativi quanti sono i campi da inserire nel record, cioè quelli nell'Array MyFields, separati da virgole.

 

[Mahares]

If you have 30 fields in your table you put 30 question marks like this:

qry = "INSERT INTO tabella VALUES (?,?,?,?,?,?,?,?,?,?,   ?,?,?,?,?,?,?,?,?,?,  ?,?,?,?,?,?,?,?,?,? )"

If your table has 25 fields you put 25 question marks

 

[fifiddu70]

not work not work not work HELP ME !!!!!

 

[LucaMs]

Your are adding 31 objects in MyFields and 33 question marks in the query.


I campi che stai aggiungendo con:

MyFields=Array As Object

sono 31

i punti interrogativi sono 33

 

[LucaMs]

If you use underscore char to split lines, you can help yourself

    Dim MyFields() As Object
    MyFields=Array As Object(NewID, txtlocale.text, txtuser.Text, txtpassword.Text, _
                                              txtpiattaforma.Text, txtincasso.Text,txtdata.Text, _
                                              txtora.Text, txtfido.Text, txtpercentuale.Text, _
                                              txtcaricati.Text, txtresiduo.Text, txtconsumato.Text, _
                                              txtquotalocale.Text, txtrimanenza.Text, txtstorico.Text, _
                                              txtweb.Text, txtesattore.Text, txtnotespesevarie.Text, _
                                              txtspesevarie.Text, txtspesecarburante.Text, txtaccontoagente.Text, _
                                              txtaccontolocale.Text, txtnotespeselocale.Text, txtdareagente.Text, _
                                              txtdarelocale.Text, txtnote.Text, txtpercentualeagente.Text, _
                                              txtquotaagente.Text, txtquotalocale.Text, txtstoricoincassi.text)  'rest of edittext boxes
    qry = "INSERT INTO tabella VALUES (?,?,?,?,?,?,?,?,?,?, _
                                                  ?,?,?,?,?,?,?,?,?,?, _
                                                  ?,?,?,?,?,?,?,?,?,?, _
                                                  ?,?,?)"  'Account for all fields

In this textbox they seem not aligned

(NOTE: that is your code, with 31 and 33!)

 

[fifiddu70]

LucaMs per me il linguaggio sqlite è ostico, solo perchè non ho tempo per studiarlo, quindi spero di risolvere il problema con l'aiuto del forum ma ovviamente mi rendo conto che bisogna avere un minimo di conoscenza del sqlite.
mi sa che devo chiedere aiuto nel forum della mia lingua di origine magari per avere più aiuto in merito. di solito vengo sul forum inglese perchè trovo subito risposte.

LucaMs sqlite for me the language is confusing, just because I do not have time to study it, so I hope to solve the problem with the help of the forum but of course I realize that you have to have a minimum knowledge of sqlite

 

[LucaMs]

Yes, I understand. The Questions forum is visited by many more people (even if I use the "New posts", then I see your posts on each forum).

But it is not easy to learn a complex subject such as the db using a foreign language.

However on DB and SQLite I can give you a help.

 

[Mahares]

Here are the problems why your were crashing: If you do not understand my remarks, please ask someone who can translate it to you in Italian.
PROBLEM #1: In the below code you show 31 items but your table has 33 fields. You need 33 items to match the number of fields in the table.
PROBLEM #2: You show the first item to be inserted NewID, but the corresponding field for NewI in the table is the 30th field (29th 0 base). Some other fields are out of place. The data must match the fields you are putting them in. Otherwise, you will never get your code to work.

 MyFields=Array As Object(NewID,txtlocale.text,txtuser.Text,txtpassword.Text, _
    txtpiattaforma.Text,txtincasso.Text,txtdata.Text,txtora.Text,txtfido.Text, _
    txtpercentuale.Text,txtcaricati.Text,txtresiduo.Text,txtconsumato.Text, _
    txtquotalocale.Text,txtrimanenza.Text,txtstorico.Text,txtweb.Text, _
    txtesattore.Text,txtnotespesevarie.Text,txtspesevarie.Text,txtspesecarburante.Text, _
    txtaccontoagente.Text,txtaccontolocale.Text,txtnotespeselocale.Text,txtdareagente.Text, _
    txtdarelocale.Text,txtnote.Text,txtpercentualeagente.Text,txtquotaagente.Text, _
    txtquotalocale.Text, txtstoricoincassi.text)   '31 should be 33 fields

'he below part of the code is good. Copy it as it is:

qry = "INSERT INTO tabella VALUES (?,?,?,?,?,?,?,?,?,?,  ?,?,?,?,?,?,?,?,?,?,  _
    ?,?,?,?,?,?,?,?,?,?, ?,?,?)"  
    SQL1.ExecNonQuery2(qry,MyFields)