I'm building a GUI that allows users to search information in a ms access database (yup. It has to be the ms access) The user has a textfield where he can type his search and the Tableview should update instantly. At the moment the DB disappears whenever you type a letter in the field.

Took me a while to figure out the problem: my SQL statement is simply not right. (Thanks to model.lastError)

The whole function looks like this:

    self.ui.Kistenschild_suchen.textChanged.connect(self.update_filter)def update_filter(self, s):s = re.sub("[\W_]+", "", s)filter_str = 'Produkt LIKE %{}%"'.format(s)self.ui.model.setFilter(filter_str)print(self.ui.model.lastError())

In this case I typed k

The errormessage is:

PySide6.QtSql.QSqlError("-3100", "QODBC: Unable to execute statement", "[Microsoft][ODBC-Treiber für Microsoft Access] Syntaxfehler in Abfrageausdruck 'Produkt LIKE (%h%\")'.") at 0x000001CA4FB33B88>

Point of interest should be the '%h%")'."' Since it shows more characters than typed

I tried to change in several ways, like changing the % to * and? Still nothing

EDIT:

Here is the minimal reproducible example:

import re
import sysfrom PySide6.QtCore import QSize, Qt
from PySide6.QtSql import QSqlDatabase, QSqlTableModel
from PySide6.QtWidgets import (QApplication,QLineEdit,QMainWindow,QTableView,QVBoxLayout,QWidget,
)Driver= r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=D:\scripts\python\pyside_Tutorials\databases\chinook.accdb'db = QSqlDatabase("QODBC")
db.setDatabaseName(Driver)
db.open()class MainWindow(QMainWindow):def __init__(self):super().__init__()container = QWidget()layout = QVBoxLayout()self.search = QLineEdit()self.search.textChanged.connect(self.update_filter)self.table = QTableView()layout.addWidget(self.search)layout.addWidget(self.table)container.setLayout(layout)self.model = QSqlTableModel(db=db)self.table.setModel(self.model)self.model.setTable("Track")self.model.select()self.setMinimumSize(QSize(1024, 600))self.setCentralWidget(container)# tag::filter[]def update_filter(self, s):s = re.sub("[\W_]+", "", s)filter_str = 'Name LIKE "%{}%"'.format(s)self.model.setFilter(filter_str)print(self.model.lastError())print(s,type(s))# end::filter[]app = QApplication(sys.argv)
window = MainWindow()
window.show()
app.exec()

This code gives me the errormessage: <PySide6.QtSql.QSqlError("-3010", "QODBC: Unable to execute statement", "[Microsoft][ODBC-Treiber für Microsoft Access] 1 Parameter wurden erwartet, aber es wurden zu wenig Parameter übergeben.") at 0x0000016FC7535108>

Which means something like: "1 parameter was expected but too few parameters were passed"

MS-Access needs a double apostrophe like:

def update_filter(self, s):s = re.sub(r"[\W_]+", "", s)filter_str = f"Produkt '%{s}%'"self.model.setFilter(filter_str)```