Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

SQLite record insertion isn't correct using eventFilter() to catch the Enter key



  • Hi everyone!
    I'm creating desktop app processing data from SQLite3 database by QTableWidget. I want a new record to be added after pressing Enter on the keyboard when the empty cell of empty QTabbleWidget row is changed, but after pressing Enter my app inserts this record to an emty table on the 1 place instead of 0, and my display function does not see it, of course, showing just empty cells in QTableWidget and QSqlQuery::value: not positioned on a valid record in Debug. (I Know that because if i change value of the record index to 1 in my getValue() function, display() works well). I'm using eventFilter() to catch Enter pressing.
    The sructure of my project :
    DataProcessor class responds for all database processes:

    #include "dataprocessor.h"
    
    DataProcessor::DataProcessor()
    {
        mdb = QSqlDatabase::addDatabase("QSQLITE");
        mdb.setDatabaseName("MainData.sqlite");
        if(mdb.open())
            qDebug() << "DB is connected";
        else
            qDebug() << "DB is not connected";
        tableNames = mdb.tables();
    
        if (!tableNames.isEmpty())
            setCurrentTable(tableNames[0]);
        else
            setCurrentTable("DB is empty");
    
    }
    
    void DataProcessor::insertRecord(QString tableName, QString values, QStringList colNames)
    {
        bool b;
        QString strQuery = "", strColNames = "";
        QSqlQuery query;
    
        for (int i = 0; i < colNames.size(); i++)
            strColNames += colNames[i] + ", ";
    
        strColNames = strColNames.left(strColNames.lastIndexOf(','));
    
        strQuery = "INSERT INTO " + tableName + "(" + strColNames + ") "
                "VALUES (" + values + ")";
        qDebug() << strQuery;
    
        b = query.exec(strQuery);
        if (!b)
            qDebug() << query.lastError().text();
        else
            qDebug() << "Data insertion is successfull!";
    }
    
    QSqlQuery DataProcessor::selectAll(QString tableName)
    {
        bool b;
        QSqlQuery query;
        QString strQuery = "SELECT * FROM " + tableName + ";";
    
        b = query.exec(strQuery);
        if (!b)
            qDebug() << "Data is not selected";
        else
            qDebug() << "Data is selected";
        return query;
    
    }
    
    QString DataProcessor::getValue(QString tableName, int recordNum, int colNum)
    {
        QSqlQuery query = selectAll(tableName);
        QStringList colTypes = getColTypes(tableName);
        QString value = "";
        int i = 0;
        while( i <= 0)
        {
            if (query.next() == recordNum)
            {
                value = query.value(colNum).toString();
                i++;
            }
        }
        return value;
    }
    
    QString DataProcessor::getValueParams(QStringList typeNames, QStringList paramVals)
    {
        QString tempstr = "", result = "";
         for (int i=0; i<typeNames.size(); i++)
         {
            if (typeNames[i] == "int")
                 tempstr = "\%%1, ";
            else
                 tempstr = "\'\%%1\', ";
             tempstr = tempstr.arg(1);
             result += tempstr;
             result = result.arg(paramVals[i]);
          }
         int pos = result.lastIndexOf(QChar(','));
         result = result.left(pos);
         return result;
    }
    

    MainWindow class diplays and catches KeyPress events:

    MainWindow::MainWindow(QWidget *parent)
        : QMainWindow(parent)
        , ui(new Ui::MainWindow)
    {
        ui->setupUi(this);
        dataProcessor = new DataProcessor();
        currentTable = dataProcessor->getCurrentTable();
        ui->label_tableName->setText(currentTable);
        ui->main_Table->installEventFilter(this);
        display(DEFAULT); //DEFAULT is parameter to switch the regular showing of the data in table
    }
    
    void MainWindow::display(int param)
    {
        QStringList colNames = dataProcessor->getColNames(currentTable);
        int colCount = dataProcessor->getColNum(currentTable);
        int rowCount = dataProcessor->getRowNum(currentTable);
        ui->main_Table->setColumnCount(colCount);
        ui->main_Table->setHorizontalHeaderLabels(colNames);
        int i=0;
        QSqlQuery query = dataProcessor->selectAll(currentTable);
        switch (param)
        {
            case 0:
            ui->main_Table->setRowCount(rowCount);
                while (query.next())
            {
    
                for (int j=0; j<colCount; j++)
                {
                   QTableWidgetItem *item = ui->main_Table->item(i, j);
                   if (!item)
                   {
                       item = new QTableWidgetItem();
                       ui->main_Table->setItem(i, j, item);
                       item -> setText(dataProcessor->getValue(currentTable, i, j));
                   }
                 }
                i++;
            }
               ui -> retranslateUi(this);
    
            break;
    
            case 1:
            ui->main_Table->setRowCount(rowCount+1);
            bool endOfLoop = false;
            i = 0;
            while (!endOfLoop)
            {
               query.next();
                for (int j=0; j<colCount; j++)
                {
                    if (i<rowCount)
                   {
                        QTableWidgetItem *item = ui->main_Table->item(i, j);
                        if (!item)
                        {
                            item = new QTableWidgetItem();
                            ui->main_Table->setItem(i, j, item);
                            item -> setText(dataProcessor->getValue(currentTable, i, j));
                        }
                    }
    
                    else if (i > rowCount)
                            endOfLoop = true;
    
                    else
                    {
                        QTableWidgetItem *item = ui->main_Table->item(i, j);
                        if (!item)
                        {
                            item = new QTableWidgetItem();
                        }
                     }
                 }
               i++;
            }
               ui -> retranslateUi(this);
    
            break;
    
        }
    }
    
    bool MainWindow::eventFilter(QObject *obj, QEvent *evt)
    {
        bool returnStatement = false;
        QStringList colNames = dataProcessor->getColNames(currentTable);
        QStringList colTypes = dataProcessor->getColTypes(currentTable);
        QStringList insertValues;
        QString strInsertValues = "";
    
        if (evt->type() == QEvent::KeyRelease)
        {
             QKeyEvent *ke = static_cast<QKeyEvent *>(evt);
             if (ke->key() == Qt::Key_Enter || ke->key() == Qt::Key_Return)
             {
                 for (int i = 0; i < colNames.size(); i++)
                 {
                     QTableWidgetItem *item = ui->main_Table->item(ui->main_Table->currentRow(), i);
                     if(!item)
                     {
                         item = new QTableWidgetItem();
                         ui->main_Table->setItem(ui->main_Table->currentRow(), i, item);
                     }
    
    
                     if (item->text() != "" && item->text() != "-")
                     {
                         insertValues.append(item->text());
                     }
                     else if (i == ui->main_Table->currentColumn())
                     {
                      insertValues.append(item->text());
                     }
                     else
                         insertValues.append("0");
                 }
                 strInsertValues = dataProcessor->getValueParams(colTypes, insertValues);
                 dataProcessor->insertRecord(currentTable, strInsertValues, colNames);
    
                 display(DEFAULT);
                 returnStatement = true;
             }
                 else
             {
                 returnStatement = false;
             }
    
        }
        return returnStatement;
    }
    

    P.S. I did not provide any verification if the record exists or if the row is empty because insertion of the record did not work correctly from the start and i tried to insert it to an empty table, so at this stage this doesn't make sense for me.

    P.S.S There are only main functions, used for solving this problem, so if someone needs extra code info i'll provide it without any problem.


  • Qt Champions 2019

    Use a debugger to see where the error happens exactly.


  • Lifetime Qt Champion

    Hi and welcome to devnet,

    Beside @Christian-Ehrlicher good point, why not use QSqlTableModel to show and manage your SQL data ?

    On a side note, please do not store a QSqlDatabase member variable. You have the explanation in the class details.



  • @SGaist , thank you for your advice!
    I want to use QTableWidget because my tables won't store too much records, that's why i will not get much more perfomance. On the other side implementation of the QTableWidget seems to be simpler for me. In any case I'm a student and there is so much to learn left.
    P.S.: deleted QSqlDatabase member variable and organized connection through the QSqlDatabase::database() in places where I need this variable, thank you.



  • @Christian-Ehrlicher Debugger was not even necessary because the problem was just student's inexperience: i just needed to find QSqlQuery::at() in documentation and replace this piece of stupidity:

    QString DataProcessor::getValue(QString tableName, int recordNum, int colNum)
    {
        QSqlQuery query = selectAll(tableName);
        QStringList colTypes = getColTypes(tableName);
        QString value = "";
        int i = 0;
        while( i <= 0)
        {
            if (query.next() == recordNum)
            {
                value = query.value(colNum).toString();
                i++;
            }
        }
        return value;
    }
    
    

    by this:

    QString DataProcessor::getValue(QString tableName, int recordNum, int colNum)
    {
        QSqlQuery query = selectAll(tableName);
        QStringList colTypes = getColTypes(tableName);
        QString value = "";
        while(query.next())
        {
            if (query.at() == recordNum)
            {
                value = query.value(colNum).toString();
            }
    
        }
    
        return value;
    }
    
    

Log in to reply