[Solved] Help Databases QtSql

[Alberto]

Hello,
Sorry for my English. I'm new to Qt, I'm working on a project with databases. I've created a database "Datos1", in this database are the values of "id", "Day", "Month" and "Year" for five different ids.

I don't know how I could introduce the "id" in LineEdit and press Enter to make appear the "Day" and "Month" related to the "id" I've introduced. I just need these two data shown in a QTableWidget.

In addition, I would also like to be able to press a button that stores "id", "Month" and "Year" in another database "Saved".

These are the codes:

• Datos.pro:

#-------------------------------------------------
#
****# Project created by QtCreator 2015-09-29T19:26:52****
#
#-------------------------------------------------

QT       += core gui sql

greaterThan(QT_MAJOR_VERSION, 4): QT += widgets

TARGET = Partes_Hospital_Clinic
TEMPLATE = app


SOURCES += main.cpp\
        mainwindow.cpp \
    basedialog.cpp

HEADERS  += mainwindow.h \
    basedialog.h

FORMS    += mainwindow.ui \
    basedialog.ui

• mainwindow.h:

#ifndef MAINWINDOW_H
#define MAINWINDOW_H

#include <QMainWindow>
#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlQuery>
#include <QtSql/QSqlError>

namespace Ui {
class MainWindow;
}

class MainWindow : public QMainWindow
{
    Q_OBJECT

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

    /*

    void crearTablaDatos2();

    void insertarDatos2();

    */

private slots:

    void on_pushButton_clicked();

    void on_pushButton_2_clicked();

private:
    Ui::MainWindow *ui;
    QSqlDatabase db;
};

#endif // MAINWINDOW_H

• basedialog.h:

#ifndef BASEDIALOG_H
#define BASEDIALOG_H

#include <QDialog>
#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlQuery>
#include <QtSql/QSqlError>

namespace Ui {
class BaseDialog;
}

class BaseDialog : public QDialog
{
    Q_OBJECT

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

        void mostrarDatos2();

private:
    Ui::BaseDialog *ui;
};

#endif // BASEDIALOG_H

• main.cpp:

#include "mainwindow.h"
#include <QApplication>

int main(int argc, char *argv[])
{
    QApplication a(argc, argv);
    MainWindow w;
    w.show();

    return a.exec();
}

• mainwindow.cpp:

#include "mainwindow.h"
#include "ui_mainwindow.h"
#include "basedialog.h"

#include <QDebug>
#include <QStandardItemModel>

MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
{
    ui->setupUi(this);

    qDebug()<<"Aplicación iniciada";

    QString nombre;
    nombre.append("Datos1");

    db=QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(nombre);

    if(db.open()){
        qDebug()<<"Se ha conectado a la base de datos";
    }else{
        qDebug()<<"ERROR. No se ha conectado a la base de datos";
    }

}

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

/*

void MainWindow::crearTablaDatos2()
{
    QString consulta;
    consulta.append("CREATE TABLE IF NOT EXISTS Datos2("
                    "id INTEGER PRIMARY KEY,"
                    "Day VARCHAR(100),"
                    "Month VARCHAR(100),"
                    "Year VARCHAR(100)"
                    ");");
    QSqlQuery crear;
    crear.prepare(consulta);

    if(crear.exec())
    {
        qDebug()<<"La tabla Datos2 exixte o se ha creado correctamente";
    }else{
        qDebug()<<"ERROR. La tabla Datos2 no exixte o no se ha creado correctamente";
        qDebug()<<"ERROR:"<<crear.lastError();
    }

}

void MainWindow::insertarDatos2()
{
    QString consulta;
    consulta.append("INSERT INTO Datos2("
                    "id,"
                    "Day,"
                    "Month,"
                    "Year)"
                    "VALUES("
                    "5,"
                    "'5',"
                    "'5',"
                    "'2015'"
                    ");");
    QSqlQuery insertar;
    insertar.prepare(consulta);

    if(insertar.exec())
    {
        qDebug()<<"El dato se ha insertado correctamente";
    }else{
        qDebug()<<"ERROR. El dato no se ha insertado correctamente";
        qDebug()<<"ERROR:"<<insertar.lastError();
    }

}

*/

void MainWindow::on_pushButton_clicked()
{
    /*
    crearTablaDatos2();
    insertarDatos2();
    */
}

void MainWindow::on_pushButton_2_clicked()
{
        BaseDialog bs(this);
        bs.setWindowTitle("Database");
        bs.exec();

}

• basedialog.cpp:

#include "basedialog.h"
#include "ui_basedialog.h"

#include <QDebug>
#include <QStandardItemModel>

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

    mostrarDatos2();
}

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


void BaseDialog::mostrarDatos2()
{
        QString consulta;
        consulta.append("SELECT*FROM Datos2");
        QSqlQuery mostrar;
        mostrar.prepare(consulta);

        if(mostrar.exec())
        {
            qDebug()<<"Los datos se han mostrado correctamente";
        }else{
            qDebug()<<"ERROR. Los datos no se hana mostrado correctamente";
            qDebug()<<"ERROR:"<<mostrar.lastError();
        }

        int columna=0;

        while(mostrar.next()){

        ui->tableWidgetDatos2->setRowCount(4);


        QStringList Datos3;
        Datos3<<"Id"<<"Day"<<"Month"<<"Year";

        ui->tableWidgetDatos2->setVerticalHeaderLabels(Datos3);

            ui->tableWidgetDatos2->insertColumn(0);
            ui->tableWidgetDatos2->setColumnCount(10);

            ui->tableWidgetDatos2->setItem(0, 0, new QTableWidgetItem(mostrar.value(0).toByteArray().constData()));
            ui->tableWidgetDatos2->setItem(1, 0, new QTableWidgetItem(mostrar.value(1).toByteArray().constData()));
            ui->tableWidgetDatos2->setItem(2, 0, new QTableWidgetItem(mostrar.value(2).toByteArray().constData()));
            ui->tableWidgetDatos2->setItem(3, 0, new QTableWidgetItem(mostrar.value(3).toByteArray().constData()));

            columna++;
        }

}

Does anyone know any way to do it?

Thanks for replying!

[edit: Fixed coding tags use ``` SGaist]

[SGaist]

Hi and welcome to devnet,

A QTableView would be more suited for that purpose. As for adding new data, it depends on how you want to add them, you can use QDataWidgetMapper

Hope it helps

[Alberto]

@SGaist
At the end my code reads the data I ask for in the LineEdit, but the QTableWidget can't show it.

• One attempt:

void MainWindow::visualizarUsuarios()
{
    QSqlQuery qry;
    qry.prepare("SELECT*FROM otra WHERE id=:ID");
    qry.bindValue(":ID", ui->lineEditBueno->text().trimmed());

    if(qry.exec())
    {
       ui->tableWidgetMuestra->setColumnCount(5);

            ui->tableWidgetMuestra->insertColumn(0);

            ui->tableWidgetMuestra->setItem(0, 0, new QTableWidgetItem(qry.value(1).toByteArray().constData()));

            ui->tableWidgetMuestra->setItem(0, 1, new QTableWidgetItem(qry.value(2).toByteArray().constData()));
          
        qDebug()<<"Los datos se han qry correctamente";

   } else {
       qDebug()<<"ERROR. Los datos no se hana qry correctamente";
       qDebug()<<"ERROR:"<<qry.lastError();
   }
    }

.
.
**`* **Another attempt:*******************************************
    

    QSqlQuery admirar;
    admirar.prepare("SELECT*FROM otra WHERE id=:id");
    admirar.bindValue(":id", ui->lineEditBueno->text().trimmed());
    if(admirar.exec()){

        int fila=0;

        ui->tableWidgetMuestra->setRowCount(0);

        while(admirar.next()){

           ui->tableWidgetMuestra->insertColumn(0);

           ui->tableWidgetMuestra->insertRow(fila);

            ui->tableWidgetMuestra->setItem(0, 0, new QTableWidgetItem(admirar.value(1).toByteArray().constData()));

            ui->tableWidgetMuestra->setItem(0, 1, new QTableWidgetItem(admirar.value(2).toByteArray().constData()));

            ui->tableWidgetMuestra->setItem(0, 2, new QTableWidgetItem(admirar.value(3).toByteArray().constData()));

            ui->tableWidgetMuestra->setItem(0, 3, new QTableWidgetItem(admirar.value(4).toByteArray().constData()));

           fila++;
        }

         qDebug()<<"Los datos se han mostrado correctamente";

    } else {
        qDebug()<<"ERROR. Los datos no se hana mostrado correctamente";
        qDebug()<<"ERROR:"<<admirar.lastError();
    }

*lastError():

Se ha conectado a la base de datos
QSqlQuery::value: not positioned on a valid record
QSqlQuery::value: not positioned on a valid record
Los datos se han qry correctamente

¿Do you now any way to solve it? I'm trying to continue using QTableWidget in order to not having to start learning another way to show data and break up all the project.

Thanks!

[SGaist]

You did not call next before accessing the result of your query

[Alberto]

Solved.
I tried a lot of things to solve the problem. Some of them included calling next before accessing the result as @SGaist said, but it continued saying:

QSqlQuery::value: not positioned on a valid record

At last I created a new project with a new database and it worked properly with this code:

    hospital=QSqlDatabase::addDatabase("QSQLITE");
    hospital.setDatabaseName("C:/Sqlite3/Hospital.sqlite");

    if(hospital.open()){
        qDebug()<<"4.Se ha conectado a la base de datos Hospital";
    }else{
        qDebug()<<"4.ERROR. No se ha conectado a la base de datos Hospital";
    }

    QSqlQuery mostrar;
    mostrar.prepare("SELECT*FROM Partes WHERE N_Parte=:ID");
    mostrar.bindValue(":ID",ui->lineEditN_Parte->text());

        if(mostrar.exec())
        {
            qDebug()<<"5.Los datos del parte se han mostrado correctamente";
        }else{
            qDebug()<<"5.ERROR. Los datos del parte no se han mostrado correctamente";
            qDebug()<<"5.ERROR:"<<mostrar.lastError();
        }

        mostrar.next();

        ui->tableWidget->setRowCount(15);

        QStringList Campos;
        Campos<<"Fecha de Emisión"<<"Unidad Hospitalaria";

            ui->tableWidget->setVerticalHeaderLabels(Campos);

            ui->tableWidget->insertColumn(0);
            ui->tableWidget->setColumnCount(1);

            ui->tableWidget->setItem(0, 0, new QTableWidgetItem(mostrar.value(1).toByteArray().constData()));
            ui->tableWidget->setItem(2, 0, new QTableWidgetItem(mostrar.value(2).toByteArray().constData()));

So I guess the problem was in the database, maybe in the columns, maybe in the rows, maybe in the values or maybe in the name of each of them.

The important thing is that the code I post in this reply works perfectly, so if anyone has a similar problem I hope it helps.

Thank you very much!