Android Question datatype mismatch: INSERT INTO table VALUES

fifiddu70

Well-Known Member
Licensed User
Longtime User
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

B4X: 
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
 

Attachments

  • ooo.png
    25.5 KB · Views: 631
Sort by date Sort by votes

ggpanta

Member
Licensed User
Longtime User
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.
 
Upvote 0

klaus

Expert
Licensed User
Longtime User
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.
 
Upvote 0

mc73

Well-Known Member
Licensed User
Longtime User
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.
 
Upvote 0

LucaMs

Expert
Licensed User
Longtime User
mc73 said:
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.
Click to expand...


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
 
Last edited:
Upvote 0

fifiddu70

Well-Known Member
Licensed User
Longtime User
this a part incomplete of my project.
run for see this error
 

Attachments

  • sample.r.zip
    435 KB · Views: 290
Upvote 0

DonManfred

Expert
Licensed User
Longtime User
fifiddu70 said:
this a part incomplete of my project.
run for see this error
Click to expand...

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

B4X: 
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"


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:

B4X: 
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.
 

Attachments

  • sqlitemanager.jpg
    233.7 KB · Views: 480
Last edited:
Upvote 0

fifiddu70

Well-Known Member
Licensed User
Longtime User
DonManfred i use thi modify but non work, i have syntax error
B4X: 
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)
 
Upvote 0

DonManfred

Expert
Licensed User
Longtime User
B4X: 
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)
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
You need something like this:
B4X: 
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)
 
Upvote 0

LucaMs

Expert
Licensed User
Longtime User
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.
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
If you have 30 fields in your table you put 30 question marks like this:
B4X: 
qry = "INSERT INTO tabella VALUES (?,?,?,?,?,?,?,?,?,?,   ?,?,?,?,?,?,?,?,?,?,  ?,?,?,?,?,?,?,?,?,? )"

If your table has 25 fields you put 25 question marks
 
Upvote 0

LucaMs

Expert
Licensed User
Longtime User
If you use underscore char to split lines, you can help yourself

B4X: 
    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!)
 
Upvote 0

fifiddu70

Well-Known Member
Licensed User
Longtime User
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
 
Upvote 0

LucaMs

Expert
Licensed User
Longtime User
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.
 
Last edited:
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
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.

B4X: 
 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:
B4X: 
qry = "INSERT INTO tabella VALUES (?,?,?,?,?,?,?,?,?,?,  ?,?,?,?,?,?,?,?,?,?,  _
    ?,?,?,?,?,?,?,?,?,?, ?,?,?)"  
    SQL1.ExecNonQuery2(qry,MyFields)
 
Upvote 0
You must log in or register to reply here.
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…