QSql database - unable to update data

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! ;-)