0

I have created the below Python Tkinter application to insert values from text entry boxes into a SQL Server table. The program runs and insert number values into the SQL table but fails when trying to use letters and returns an

Exception in Tkinter callback

pyodbc.ProgrammingError: ('42S22', "[42S22] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'asdf'. (207) (SQLExecDirectW)").

Code for the insert function.

value1=DENSITY_VALUE.get()
value2=DATE_1.get()
value3=TIME_1.get()
value4=CHART_VALUE.get()

# Insert into table
cursor.execute("INSERT INTO TEST_DISA_MAIN_TABLE(DENSITY_VALUE, DATE_1, TIME_1, CHART_VALUE) VALUES ("+value1+", "+value2+", "+value3+", "+value4+")")

# Commit changes
conn.commit()
# Close Connection
conn.close()

# Clear the text boxes
DENSITY_VALUE.delete(0, END)
DATE_1.delete(0, END)
TIME_1.delete(0, END)
CHART_VALUE.delete(0, END)
Thom A
  • 88,727
  • 11
  • 45
  • 75
ABCDave
  • 11
  • 1
  • **Parametrise** your statements. The reason your code is failing is because you are injecting your values. – Thom A Jun 16 '22 at 14:18
  • What library are you using to connect to SQL Server? pyodbc? pymssql? Something else? – Thom A Jun 16 '22 at 14:19
  • @Larnu I am using pyodbc. How would I parametrise the insert statement to fix this? – ABCDave Jun 16 '22 at 14:23
  • Does this answer your question? [pyodbc insert into sql](https://stackoverflow.com/questions/20199569/pyodbc-insert-into-sql) – Thom A Jun 16 '22 at 14:25
  • If I understood the information in the link above I have changed the insert statement to the following: cursor.execute("INSERT INTO TEST_DISA_MAIN_TABLE(DENSITY_VALUE, DATE_1, TIME_1, CHART_VALUE) VALUES (?, ?, ?, ?)", 'value1', 'value2', 'value3', 'value4') However I am now receiving the following error: pyodbc.DataError: ('22018', "[22018] [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting the nvarchar value 'value1' to data type int. (245) (SQLExecDirectW)") The first 3 SQL values are data type int and the 4th is varchar. – ABCDave Jun 16 '22 at 14:46
  • @Larnu thanks it is working correctly now using parameters: cursor.execute("INSERT INTO TEST_DISA_MAIN_TABLE(DENSITY_VALUE, DATE_1, TIME_1, CHART_VALUE) values (?, ?, ?, ?)", value1, value2, value3, value4) – ABCDave Jun 16 '22 at 15:30

1 Answers1

-1

f"INSERT INTO TEST_DISA_MAIN_TABLE(DENSITY_VALUE, DATE_1, TIME_1, CHART_VALUE) VALUES ('{value1}', '{value1}', '{value1}', '{value1}')"

Try this. I think your problem is with quotes.

Serhii Pas
  • 1
  • 1
  • This is a terrible solution; it leaves the OP open to injection attacks (as they already were). – Thom A Jun 16 '22 at 14:18