Ошибка при подтверждении новой строки datatype mismatch

@YCF_L explains the problem correctly. I believe the issue that you have @Muhammad Quanit is that you are not understanding what your query is asking for and what you’re trying to pass into the query.
To break it down for you, your query:

"Insert into Calculation_Info (prodID,item_name,net_amount,tax_rate,Amt_Of_Product,Grand_Total) values(?,?,?,?,?,?)"

Takes 6 values that you have to provide (Please NOTE the type of each they take in):

1. prodID (int)
2. item_name(String)
3. net_amount(double)
4. tax_rate(BigDecimal)
5. Amt_Of_Product(int)
6. Grand_Total(BigDecimal)

In your program, you have 6 text boxes that will obtain these values to put in HOWEVER when you call the method: getText() it returns a String type. So when we try to put String prodID = "3" into our SQL query, you would receive the error you originally received: datatype mismatch. Now your current problem is: java.lang.numberFormatException : for input String. This means that you are trying to convert a String value into a number; however it is FAILING. So what could be possible reasons for this? Well…

/* Lets say I have a string with value 4.7*/
    String s = "4.7";
/* Lets try to parse this using parseInt method*/
    Integer.parseInt(s); //NumberFormatException -- because 4.7 cannot be converted into an int
/* How about if my string was a word/phrase?*/
    String s = "foo";
/* Attempt to parse...*/
    Integer.parseInt(s); //NumberFormatException -- because "foo" cannot be converted into integer
    Double.parseDouble(s); //NumberFormatException -- because "foo" cannot be converted into a double

So as you can see, you have to be very careful and keep track of what you are converting and passing into your query (and vice versa).

As @YCF_L mentioned, perhaps your String contains white spaces at the start or end. There is a method called trim() in the String class that will help you with that.

/* Let's call the getText() method to obtain our string */
    String s = item_name.getText(); // s = "    Foo Bar " 
/* NOTE: the white spaces (' ') BEFORE and AFTER the String we want*/
    System.out.println(s.trim()); // Prints out "Foo Bar"

So the String OBJECT has a method called trim() which gets rid of TRAILING and LEADING white spaces, but NOT white spaces in between. So instead of getting " Foo Bar ", we will get "Foo Bar".

I upvoted @YCF_L ‘s answer — he deserves the credit. I just wanted you to understand this concept since you were still confused after his explanation followed by receiving the NumberFormatException.

Editted to include the trim() method that was mentioned in the comments.

I have a table created with the following statement

CREATE TABLE IF NOT EXISTS exam_table (id integer primary key AUTOINCREMENT, moodleexamid integer unique, examname text, examdesc text, examinstructions text, noofstations integer, examvenue text, examstartdate numeric, examstarttime numeric, examenddate numeric, examendtime numeric, examstaffid text, isDownloaded integer, isuploaded integer, timecreated DATETIME, timemodified datetime DEFAULT CURRENT_TIMESTAMP)»

I am able to export data from the db but when I am trying to import the data I a getting the following error.(Note — The data contains string with html tags)

`The following error occurred: {«code»:0,»message»:»Failed to import SQL; message=datatype mismatch»,»statement»:»INSERT OR REPLACE INTO exam_table SELECT ‘Test Examination2’ AS ‘examname’, ‘1’ AS ‘id’, ’22:40:00′ AS ‘examstarttime’, ‘0’ AS ‘isDownloaded’, ’22:40:00′ AS ‘examendtime’, ‘

Test Examination2

‘ AS ‘examinstructions’, ‘8-06-2016’ AS ‘examenddate’, ‘8-06-2016’ AS ‘timecreated’, ‘sydney’ AS ‘examvenue’, ‘2’ AS ‘noofstations’, ‘0’ AS ‘isuploaded’, ‘2017-01-05 11:10:49’ AS ‘timemodified’, ‘teststaff1’ AS ‘examstaffid’, ’14’ AS ‘moodleexamid’, ‘

Test Examination2

‘ AS ‘examdesc’, ‘8-06-2016’ AS ‘examstartdate’ UNION SELECT ‘Test Exam — 8/6/2016’, ‘2’, ’02:25:00′, ‘0’, ’02:25:00′, ‘

Test Exam — 8/6/2016

‘, ‘9-06-2016’, ‘9-06-2016’, ‘sydney’, ‘2’, ‘0’, ‘2017-01-05 11:10:49’, ‘teststaff1′, ’15’, ‘

Test Exam — 8/6/2016

‘, ‘9-06-2016’ UNION SELECT ‘Test Exam — CE243010’, ‘3’, ’19:55:00′, ‘1’, ’19:55:00′, ‘

Test Exam — CE243010

‘, ‘9-06-2016’, ‘9-06-2016’, ‘sydney’, ‘2’, ‘0’, ‘2017-01-05 11:10:49’, ‘teststaff1′, ’19’, ‘

Test Exam — CE243010

‘, ‘9-06-2016’ UNION SELECT ‘Testing Issue 158’, ‘4’, ’15:50:00′, ‘0’, ’23:50:00′, ‘

This exam is created to test the OSCE issue #158

sdgsdfg

sdfhdfghdfh

df dfhjgh fdghgfjh ghjghj,kfghjk dhgdkfgh dskjghkdfgh fghdfkh kdfg dfkghkdfhk fdhdfhkdfjhkj fghdfkghdkfhdkfghgfhkdfk

‘, ’24-11-2016′, ’22-11-2016’, ‘sydney’, ‘2’, ‘0’, ‘2017-01-05 11:10:49’, ‘teststaff1′, ’22’, ‘

This exam is created to test the OSCE issue #158

sdgsdfg

sdfhdfghdfh

df dfhjgh fdghgfjh ghjghj,kfghjk dhgdkfgh dskjghkdfgh fghdfkh kdfg dfkghkdfhk fdhdfhkdfjhkj fghdfkghdkfhdkfghgfhkdfk

‘, ’22-11-2016′»}

`

As you can see in the error message, there is a datatype mismatch while you insert the data into your table. It may happen when you try to insert a value of specific type into a column that doesn’t support this type and the value can’t be converted automatically by your db (for example, when you try to insert a string value into a column of type int).

Try to print the datatypes of your data you’re trying to load (print(type(Id_no)), print(type(Name)), etc..) and check if it’s compatible with the column datatype of your table.

I’m working on a small information inserting program for database creation, but at the time of wanting to create a new record I find the following error from line 72:

sqlite3.IntegrityError: datatype mismatch

the complete error indicates:

Exception in Tkinter callback Traceback (most recent call last):
File
«C:UsersMaJiiAppDataLocalProgramsPythonPython39libtkinter_init_.py»,
line 1885, in Shut up.return self.func(*args) File «C:UsersMaJiiOneDriveEscritorioTesis
PCPythonPráctica_guiadapyum.py», line 72, in create
miCursor.execute(«INSERT INTO DATA_FARMACOS VALUES (NULL, ‘» + myName.get() +

Line 72 is as follows:

miCursor.execute("INSERT INTO DATOS_FARMACOS VALUES(NULL, '" + miNombre.get() +

The rest of the code is as follows:

from tkinter import *
from tkinter import messagebox
import sqlite3
#---------------------------------------------------------Funcionalidades---------------------------------------------------------------------------#
#---------------------------------------------------------Menú base de datos
# Creación de base de datos
def conexionBBDD():
miConexion = sqlite3.connect("Registro de Fármacos")

miCursor = miConexion.cursor()

try:

    miCursor.execute('''
        CREATE TABLE DATOS_FARMACOS (
        ID INTEGER PRIMARY KEY AUTOINCREMENT
        NOMBRE_FÁRMACO VARCHAR (50),
        CLASIFICACIÓN_FARMACOLÓGICA VARCHAR (50),
        USO_HUMANO VARCHAR (100),
        USO_ANIMAL VARCHAR (100),
        FUNCIONES VARCHAR (150),
        INTERACCIONES_FF VARCHAR (200),
        INTERACCIONES_FE VARCHAR (200),
        PROPORCIONES_USO VARCHAR (250))
        ''')

    messagebox.showinfo("BBDD", "Base de datos creada con éxito")

except: 

    messagebox.showwarning("Intento fallido", "La base de datos ya ha sido creada")

Salir de la aplicación
def salirAplicacion():
pregunta = messagebox.askquestion("Salir", "¿Deseas salir de la aplicación?")

if pregunta == "yes":
    root.destroy()

#---------------------------------------------------------Menú borrar

#Borrar campos
def borrarCampos():
miID.set("")
miNombre.set("")
miClas.set("")
miHum.set("")
miAnim.set("")
cuadroFun.delete(1.0, END) #para indicar qué caracteres borrar
miFF.set("")
miFE.set("")
cuadroProp.delete(1.0, END)

#-----------------------------------------------------Menú CRUD
def crear():

miConexion = sqlite3.connect("Registro de Fármacos")
miCursor = miConexion.cursor()

miCursor.execute("INSERT INTO DATOS_FARMACOS VALUES(NULL, '" + miNombre.get() +
    "','" + miClas.get() +
    "','" + miHum.get() +
    "','" + miAnim.get() +
    "','" + cuadroFun.get("1.0", END) +
    "','" + miFF.get() +
    "','" + miFE.get() +
    "','" + cuadroProp.get("1.0", END) + "')")

miConexion.commit()

messagebox.showinfo("BBDD", "Registro realizado exitosamente")

#----------------------------------------------- Personalización de ventana----------------------------------------------------#

root = Tk()

root.title("Inserción de datos sobre fármacos")

root.iconbitmap("mortero.ico")

root.config(bg = "black", bd = 15, relief = "sunken", cursor = "hand2")
#------------------------------------------------ Menú de opciones-------------------------------------------------------------#

barraMenu = Menu(root)

root.config(menu = barraMenu, width = 300, height = 300)
#-------Menú de bases de datos

bbddMenu = Menu(barraMenu, tearoff = 0)

bbddMenu.add_command(label = "Conectar", command = conexionBBDD)

bbddMenu.add_command(label = "Salir", command = salirAplicacion)



  


                       
  




#-----Menú borrar

borrarMenu = Menu(barraMenu, tearoff = 0)

borrarMenu.add_command(label = "Borrar campos", command = borrarCampos)
#-----Menú CRUD

crudMenu = Menu(barraMenu, tearoff = 0)

crudMenu.add_command(label = "Crear", command = crear)

crudMenu.add_command(label = "Leer")

crudMenu.add_command(label = "Actualizar")

crudMenu.add_command(label = "Borrar")
#-----Menú ayuda

helpMenu = Menu(barraMenu, tearoff = 0)

helpMenu.add_command(label = "Licencia")

helpMenu.add_command(label = "Acerca de...")

helpMenu.add_command(label = "Contacto")
#----Opciones desplegables de menú

barraMenu.add_cascade(label = "BBDD", menu = bbddMenu)

barraMenu.add_cascade(label = "Borrar", menu = borrarMenu)

barraMenu.add_cascade(label = "CRUD", menu = crudMenu)

barraMenu.add_cascade(label = "Ayuda", menu = helpMenu)
#----------------------------------------------------------Creación del primer Frame -----------------------------------------------#
miFrame = Frame(root)

miFrame.pack()
#Para poder modificar los datos de nuestras bases de datos, asignamos a cada entry una función de variable tipo string:

miID = StringVar()

miNombre = StringVar()

miClas = StringVar()

miHum = StringVar()

miAnim = StringVar()

#miFun

miFF = StringVar()

miFE = StringVar()

#miProp
#A las funciones que se consideren texto no es necesario asignar estas variables
cuadroID = Entry(miFrame, textvariable = miID) #verificar más tarde por si son numéricos

cuadroID.grid(row = 8, column = 1, padx = 10, pady = 10)
cuadroNombre = Entry (miFrame, textvariable = miNombre)

cuadroNombre.grid(row = 0, column = 1, padx = 10, pady = 10)

cuadroNombre.config(fg = "red", justify = "center")



  


                       
  




cuadroClas = Entry(miFrame, textvariable = miClas)

cuadroClas.grid(row = 1, column = 1, padx = 10, pady = 10)
cuadroHum = Entry(miFrame, textvariable =  miHum)

cuadroHum.grid(row = 2, column = 1, padx = 10, pady = 10)
cuadroAnim = Entry(miFrame, textvariable = miAnim)

cuadroAnim.grid(row = 3, column = 1, padx = 10, pady = 10)
cuadroFun = Text(miFrame, width = 15, height = 

cuadroFun.grid(row = 4, column = 1, padx = 10, pady = 10)
cuadroFF = Entry(miFrame, textvariable = miFF)

cuadroFF.grid(row = 5, column = 1, padx = 10, pady = 10)
cuadroFE = Entry(miFrame, textvariable = miFE)

cuadroFE.grid(row = 6, column = 1, padx = 10, pady = 10)
cuadroProp = Text(miFrame, width = 15, height = 

cuadroProp.grid(row = 7, column = 1, padx = 10, pady = 10)
#---------------scrolls

#Funciones
scrollFun = Scrollbar(miFrame, command = cuadroFun.yview)

scrollFun.grid(row = 4, column = 2, sticky = "nsew")
cuadroFun.config(yscrollcommand = scrollFun)



  


                       
  




#Proporciones
scrollProp = Scrollbar(miFrame, command = cuadroFun.yview)

scrollProp.grid(row = 7, column = 2, sticky = "nsew")
cuadroProp.config(yscrollcommand = scrollProp)
#-----------------------------------------------------------Creación de Labels-------------------------------------------------------#

IDLabel = Label(miFrame, text = "ID de producto: ")

IDLabel.grid(row = 0, column = 0, sticky = "e", padx = 10, pady = 10)
NombreLabel = Label(miFrame, text = "Nombre de fármaco:")

NombreLabel.grid(row = 1, column = 0, sticky = "e", padx = 10, pady = 10)
ClasLabel = Label(miFrame, text = "Clasificación farmacológica:")

ClasLabel.grid(row = 2, column = 0, sticky = "e", padx = 10, pady = 10)
HumLabel = Label(miFrame, text = "Uso en humanos:")

HumLabel.grid(row = 3, column = 0, sticky = "e", padx = 10, pady = 10)
AnimLabel = Label(miFrame, text = "Uso en animales:")

AnimLabel.grid(row = 4, column = 0, sticky = "e", padx = 10, pady = 10)
FunLabel = Label(miFrame, text = "Funciones:")

FunLabel.grid(row = 5, column = 0, sticky = "e", padx = 10, pady = 10)
FFLabel = Label(miFrame, text = "Interacciones fármaco-fármaco:")

FFLabel.grid(row = 6, column = 0, sticky = "e", padx = 10, pady = 10)



  


                       
  




FELabel = Label(miFrame, text = "Interacciones fármaco-excipiente:")

FELabel.grid(row = 7, column = 0, sticky = "e", padx = 10, pady = 10)



  


                       
  




PropLabel = Label(miFrame, text = "Proporciones de uso:")

PropLabel.grid(row = 8, column = 0, sticky = "e", padx = 10, pady = 10)
#----------------------------------------------------------Botones (Frame 2)----------------------------------------------------------#

miFrame2 = Frame (root)

miFrame2.pack()
botonC = Button(miFrame2, text = "Crear", command = crear)

botonC.grid(row = 1, column = 0, sticky = "e", padx = 10, pady = 10)
botonR = Button(miFrame2, text = "Leer")

botonR.grid(row = 1, column = 1, sticky = "e", padx = 10, pady = 10)
botonU = Button(miFrame2, text = "Actualizar")

botonU.grid(row = 1, column = 2, sticky = "e", padx = 10, pady = 10)
botonD = Button(miFrame2, text = "Borrar")

botonD.grid(row = 1, column = 3, sticky = "e", padx = 10, pady = 10)
#------------------------------------------------ Ejecución de programa -------------------------------------------------------------------------#

root.mainloop()