QSql database - unable to update data

MR_mn

Hello,
I'm working with Qt 5.9.2 on a project which involves building a database and working with it (displaying, editing, updating data, etc..). Right now I've created a ui which is supposed to take appropriate user input data and updating the relative columns in a database I've created, the only problem is that it displays the message the data haven't been saved, and the database hasn't been updated, and I can't understand why.
Here is my .cpp file:

//your code here
#include "newequipment.h"
#include "ui_newequipment.h"
#include "secdialog.h"
#include <QSql>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QMessageBox>
#include <QSqlError>

NewEquipment::NewEquipment(QWidget *parent) :
    QDialog(parent),
    ui(new Ui::NewEquipment)
{
    ui->setupUi(this);
}

NewEquipment::~NewEquipment()
{
    delete ui;
}

void NewEquipment::on_pushButton_Save_clicked()
{
    NewEquipment conn;
    QString description, model, serial_number, manufacturer,
            year_of_manufacturing, current_eng_hours, sling_SN;
    description=ui->lineEdit_Description->text();
    model=ui->lineEdit_Model->text();
    serial_number=ui->lineEdit_SN->text();
    manufacturer=ui->lineEdit_Manufacturer->text();
    year_of_manufacturing=ui->lineEdit_Yearofmanuf->text();
    current_eng_hours=ui->lineEdit_Enghours->text();
    sling_SN=ui->lineEdit_SlingSN->text();

    if(!conn.connOpen()) {
        qDebug()<<"Connected...";
    return; }

    conn.connOpen();
    QSqlQuery qry;
    qry.prepare("insert into DB_unita (Description,Model,S/N,Manufacturer,Year of manufacturing,Current engine hours,Sling S/N) values ('"+description+"','"+model+"','"+serial_number+"','"+manufacturer+"', '"+year_of_manufacturing+"','"+ current_eng_hours+"','"+sling_SN+"')");

    if (qry.exec())
        {
        QMessageBox::information(this, "Saved!", "Saved data");
        conn.connClose();
        }
                 else {
                     QMessageBox::critical(this, tr("Error"),"Data not saved");
                     qDebug()<<qry.lastError().text();
                      }

}

Let me also say that this window which accepts user inputs is "called" from another window which displays the content of the database.
Thanks for any advice on the matter!

Regards,
M.

MR_mn

Here is also the header file content:

//#ifndef NEWEQUIPMENT_H
#define NEWEQUIPMENT_H

#include <QDialog>
#include <QSqlDatabase>
#include <QDebug>

namespace Ui {
class NewEquipment;
}

class NewEquipment : public QDialog
{
    Q_OBJECT


    QSqlDatabase mydb;

    bool connOpen()
    {
    mydb = QSqlDatabase::addDatabase("QSQLITE");
    mydb.setDatabaseName("C:/ProgettiQT/GestioneManutenzione/Database_unita.db");

    if (!mydb.open()) { qDebug() << "Failed to connect to database"; }
    else { qDebug() << "Connected to database"; }

    }

    void connClose()
    {
    mydb.close();
    mydb.removeDatabase(QSqlDatabase::defaultConnection);
    }



public:
    explicit NewEquipment(QWidget *parent = 0);
    ~NewEquipment();

private slots:
    void on_pushButton_Save_clicked();

private:
    Ui::NewEquipment *ui;
};

#endif // NEWEQUIPMENT_H

Thanks!
M.

VRonin

What does qDebug()<<qry.lastError().text(); print?
NewEquipment conn; why are you creating this object?
prevent SQL Injections using QSqlQuery::addBindValue

aha_1980

@MR_mn said in QSql database - unable to update data:

qry.prepare("insert into DB_unita (Description,Model,S/N,Manufacturer,Year of manufacturing,Current engine hours,Sling S/N) values ('"+description+"','"+model+"','"+serial_number+"','"+manufacturer+"', '"+year_of_manufacturing+"','"+ current_eng_hours+"','"+sling_SN+"')");

I'm no database expert, but I have nevertheless some hints for you:

Your column names contain spaces. Either avoid them or properly quote them.
I'd store the query in a string and print it with qDebug() before passing to qry.prepare(). That may give you a hint if the query looks like you expect.
Maybe silly question, but do you have write access to the table you want to change?

SGaist

Hi,

You are calling conn.connOpen twice, why ?

On a side note, as already written in numerous threads these past few months: don't store local QSqlDatabase objects. The reasons are explained in the QSqlDatabase details.

As a follow up question, why are re-opening that database again and again ?

MR_mn

@VRonin
This is what I get from the qDebug:
Connected to database
QSqlDatabasePrivate::removeDatabase: connection 'qt_sql_default_connection' is still in use, all queries will cease to work.
QSqlDatabasePrivate::addDatabase: duplicate connection name 'qt_sql_default_connection', old connection removed.
Connected to database
"No query Unable to fetch row"

I'm creating that object to be able to be able to execute the connOpen and connClose functions.

I will try to use the addBindValue and see if I can solve.

I will come back later with the result.

Thanks
M.

VRonin

@MR_mn said in QSql database - unable to update data:

I'm creating that object to be able to be able to execute the connOpen and connClose functions.

You don't need it, you can just call this->connOpen()

MR_mn

I tried to play a little bit with my code following your suggestions, now qDebug output is:
Connected to database
"near "/": syntax error Unable to execute statement"
" Parameter count mismatch"

Here is new code:

//#include "newequipment.h"
#include "ui_newequipment.h"
#include "secdialog.h"
#include <QSql>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QMessageBox>
#include <QSqlError>

NewEquipment::NewEquipment(QWidget *parent) :
    QDialog(parent),
    ui(new Ui::NewEquipment)
{
    ui->setupUi(this);
}

NewEquipment::~NewEquipment()
{
    delete ui;
}

void NewEquipment::on_pushButton_Save_clicked()
{


    QString description, model, serial_number, manufacturer,
            year_of_manufacturing, current_eng_hours, sling_SN;
    description=ui->lineEdit_Description->text();
    model=ui->lineEdit_Model->text();
    serial_number=ui->lineEdit_SN->text();
    manufacturer=ui->lineEdit_Manufacturer->text();
    year_of_manufacturing=ui->lineEdit_Yearofmanuf->text();
    current_eng_hours=ui->lineEdit_Enghours->text();
    sling_SN=ui->lineEdit_SlingSN->text();



    QSqlQuery qry;
    qry.prepare("INSERT INTO unita (Description,Model,S/N,Manufacturer,Year,Currentenginehours,SlingS/N) values (?, ?, ?, ?, ?, ?, ?)");

    qry.addBindValue(description);
    qry.addBindValue(model);
    qry.addBindValue(serial_number);
    qry.addBindValue(manufacturer);
    qry.addBindValue(year_of_manufacturing);
    qry.addBindValue(current_eng_hours);
    qry.addBindValue(sling_SN);

    qDebug()<<qry.lastError().text();

    qry.exec();

    if (qry.exec())
        {
        QMessageBox::information(this, "Saved!", "Saved data");
        this->connClose();
        }
                 else {
                     QMessageBox::critical(this, tr("Error"),"Data not saved");
                     qDebug()<<qry.lastError().text();

                      }

}

Here is the header file.

#ifndef NEWEQUIPMENT_H
#define NEWEQUIPMENT_H

#include <QDialog>
#include <QSqlDatabase>
#include <QDebug>

namespace Ui {
class NewEquipment;
}

class NewEquipment : public QDialog
{
    Q_OBJECT

private:

    QSqlDatabase mydb;

    void connOpen()
    {
    mydb.addDatabase("QSQLITE");
    mydb.setDatabaseName("C:/ProgettiQT/GestioneManutenzione/Database_unita.db");

    if (!mydb.open()) { qDebug() << "Failed to connect to database"; }
    else { qDebug() << "Connected to database"; }

    }

    void connClose()
    {
    mydb.close();
    mydb.removeDatabase(QSqlDatabase::defaultConnection);
    }



public:
    explicit NewEquipment(QWidget *parent = 0);
    ~NewEquipment();

private slots:
    void on_pushButton_Save_clicked();

private:
    Ui::NewEquipment *ui;
};

#endif // NEWEQUIPMENT_H

Thanks for any advice!
M.

aha_1980

Hi @MR_mn :

qry.prepare("INSERT INTO unita (Description,Model,S/N,Manufacturer,Year,Currentenginehours,SlingS/N) values (?, ?, ?, ?, ?, ?, ?)");

Your database columns "S/N" and "SlingS/N" contains slashes (/) and I guess that's driving the SQL engine crazy.

Take care with spaces and special chars. Most often, ASCII chars (a-z, A-Z 0-9) is allowed, often also the underscore (_), sometimes also the minus sign (-). All other characters can be dangerous when using text based "command" or programming languages.

I started my computer experience with MS-DOS, where file names were limited to 8 characters and most special chars were forbidden; so I'm used to such limitations.

MR_mn

Hi @aha_1980 , thanks for your advice, I changed something into the database and now I managed to have the program work, I guess the problem was with my database.

Now the strange thing is that whenever I insert new data, instead of having just one new row, I have two, and the data inside are the same. Any hint on this?

Thanks
M.

JonB

@MR_mn
That would be if you execute the INSERT twice, wouldn't it. And that's because you have 2 qry.exec() statements....

MR_mn

@JonB I thought my problem was related to what you pointed out, but actually I don't see the second query.exec() statement, where is it? I tried to take off the bindValue part, but I'm still getting the same problem.

Thanks
M.

JonB

@MR_mn
Assuming your code is as you have posted above --- if it isn't, all bets are off, till you post the actual code you're currently using --- search it for the string qry.exec if you want to know where you have written qry.exec....

SGaist

Hi,

From your code:

qDebug()<<qry.lastError().text();

    qry.exec(); << First call

    if (qry.exec()) << Second call

JonB

@SGaist
You are too soft! ;-)