I have error 'Attempt to use a closed connection.'

Question

I have some problems with my code. So, I want to add data to Accces by writing it in the form. (I am not so good in programming).

from tkinter import *
import pypyodbc
import ctypes

form=Tk ()
form.title ("Add data")
form.geometry ('400x200')

#Create connection
con = pypyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb)};UID=admin;UserCommitSync=Yes;Threads=3;SafeTransactions=0;PageTimeout=5;MaxScanRows=8;MaxBufferSize=2048;FIL={MS Access};DriverId=25;DefaultDir=C:/Users/HP/Desktop/PITL;DBQ=C:/Users/HP/Desktop/PITL/PITL.mdb;')
cursor = con.cursor ()

a = Entry (form, width=20, font="Arial 16")
a.pack ()
b = Entry (form, width=20, font="Arial 16")
b.pack ()

def Add (event):
    cursor.execute ("INSERT INTO Crime (`Number_of_article`, `ID_of_criminal`) VALUES (?, ?)", (a, b))

con.commit ()
cursor.close ()
con.close ()

Button=Button(form, text = 'PUSH ME')
Button.pack ()
Button.bind ('<Button-1>', Add)

form.mainloop ()

My error is:

Exception in Tkinter callback
Traceback (most recent call last):
  File "C:\Users\HP\AppData\Local\Programs\Python\Python36-32\lib\tkinter\__init__.py", line 1699, in __call__
    return self.func(*args)
  File "C:\Users\HP\Desktop\PITL\ADD DATA.py", line 19, in Add
    cursor.execute ("INSERT INTO Crime (`Number_of_article`, `ID_of_criminal`) VALUES (?, ?)", (a, b))
  File "C:\Users\HP\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pypyodbc-1.3.4-py3.6.egg\pypyodbc.py", line 1470, in execute
    self._free_stmt(SQL_CLOSE)
  File "C:\Users\HP\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pypyodbc-1.3.4-py3.6.egg\pypyodbc.py", line 1987, in _free_stmt
    raise ProgrammingError('HY000','Attempt to use a closed connection.')
pypyodbc.ProgrammingError: ('HY000', 'Attempt to use a closed connection.')

Also, If I change this code (below) to a = '*any number*' b = '*any number*', program will work

a = Entry (form, width=20, font="Arial 16")
a.pack ()
b = Entry (form, width=20, font="Arial 16")
b.pack ()

Remeber at what point in time the `Add()` function is actually executed. — Klaus D., Aug 20 '17 at 14:39
That's definitely not what I wrote. You are closing your connection directly after opening it. The `execute` is run much later. — Klaus D., Aug 20 '17 at 14:45

score 2 · Accepted Answer · answered Aug 20 '17 at 14:44

2

You're closing the connections before Add is called, move the close calls to the bottom of your code.

answered Aug 20 '17 at 14:44

Simon Hobbs

990
7
11

I had did it, nothing changed – Baga Aug 20 '17 at 14:46
Sorry, there is another mistake (next comment) – Baga Aug 20 '17 at 14:56

score 0 · Answer 2 · answered Aug 20 '17 at 14:53

The general best practice for .close() statements is to put them at the end of the code. But this may generate an error in this instance. Try deleting the .close() statements for now.

from tkinter import *
import pypyodbc
import ctypes

form=Tk ()
form.title ("Add data")
form.geometry ('400x200')

#Create connection
con = pypyodbc.connect('DRIVER={Microsoft Access Driver(*.mdb)};UID=admin;UserCommitSync=Yes;Threads=3;SafeTransactions=0;PageTimeout=5;    MaxScanRows=8;MaxBufferSize=2048;FIL={MS Access};DriverId=25;DefaultDir=C:/Users/HP/Desktop/PITL;DBQ=C:/Users/HP/Desktop/PITL/PITL.mdb;')
cursor = con.cursor ()

a = Entry (form, width=20, font="Arial 16")
a.pack ()
b = Entry (form, width=20, font="Arial 16")
b.pack ()

def Add (event):
    cursor.execute ("INSERT INTO Crime (`Number_of_article`, `ID_of_criminal`) VALUES (?, ?)", (a, b))


Button=Button(form, text = 'PUSH ME')
Button.pack ()
Button.bind ('<Button-1>', Add)

form.mainloop ()

con.commit ()

There is another mistake, `File "C:\Users\HP\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pypyodbc-1.3.4-py3.6.egg\pypyodbc.py", line 1296, in _BindParams if param_types[col_num][0] == 'u': TypeError: 'type' object is not subscriptable` I cannot put every text — Baga, Aug 20 '17 at 14:54

I have error 'Attempt to use a closed connection.'

2 Answers2