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

create a database for a tableview



  • Hi,

    I'm new in Qt and c++ and have the following problem.

    I get some arrays with data from an machine.
    This data I want to display in a tableview, but I have no idea how i have to creat a database.
    I have a sqllight database.
    Is it possible to make the data variable there?
    If yes, how i have to insert the array?
    At this time my data in the database are static, but this is not what i need.
    I created the tables with the designer.
    I hope someone can help with an example code.

    that is my code so far

    db.h

    #include <QSqlQuery>
    
    static bool createConnection()
    {
        QSqlDatabase db_Werkzeug_Wech_Warn = QSqlDatabase::addDatabase("QSQLITE");
        db_Werkzeug_Wech_Warn.setDatabaseName(":memory:");
        if (!db_Werkzeug_Wech_Warn.open()) {
            QMessageBox::critical(nullptr, QObject::tr("Cannot open database"),
                QObject::tr("Unable to establish a database connection.\n"
                            "This example needs SQLite support. Please read "
                            "the Qt SQL driver documentation for information how "
                            "to build it.\n\n"
                            "Click Cancel to exit."), QMessageBox::Cancel);
            return false;
        }
    
        QSqlQuery query;
        query.exec("Create Table CustomiseToolChange (COLOR varchar(20), "
                   "TOOLNR int primary key, TIME varchar(20))");
    
        query.exec("insert Into CustomiseToolChange values('green', 13, '01:32')");
        query.exec("Insert Into CustomiseToolChange values('yellow', 14, '00:25')");
       ...
    

    test_screen.cpp

    #include "test_screen.h"
    #include "ui_test_screen.h"
    #include "tableCustomise.h"
    
    #include "QDebug"
    
    
    test_screen::test_screen(QWidget *parent) :
        QMainWindow(parent),
        ui(new Ui::test_screen)
    {
        ui->setupUi(this);
    
    
        myTableCustomiseToolChange = new modelCustomiseToolChange(this);
    
        
    
        myTableCustomiseToolChange = new TableCustomise(this);
    
        myTableCustomiseToolChange->setQuery("SELECT * FROM CustomiseToolChange");
        myTableCustomiseToolChange->setHeaderData(1, Qt::Horizontal, tr("Werkzeug Nr."));
        myTableCustomiseToolChange->setHeaderData(2, Qt::Horizontal, tr("Zeit"));
        ui->TableCustomiseToolChange->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
        ui->TableCustomiseToolChange->setModel(myTableCustomiseToolChange);
    
        ui->TableCustomiseToolChange->setColumnHidden(0, true);
    
        ui->TableCustomiseToolChange->verticalHeader()->hide();
        ui->TableCustomiseToolChange->setStyleSheet("QHeaderView::section { background-color:grey }");
        ui->TableCustomiseToolChange->setStyleSheet("border: 2px solid black; background: grey");
        ui->TableCustomiseToolChange->setSelectionMode(QAbstractItemView::NoSelection);
    
    

    tableCustomise.cpp

    #include <QtWidgets>
    #include "QSqlQueryModel"
    
    #include "tableCustomise.h"
    #include "QColor"
    
    
    
    TableCustomise::TableCustomise(QObject *parent)
        : QSqlQueryModel(parent)
    {
    }
    
    
    QVariant TableCustomise::data(const QModelIndex &index, int role) const
    {
        if(role == Qt::TextAlignmentRole)
        {
            return Qt::AlignCenter;
        }
    
        if(role == Qt::BackgroundRole )
        {
    
        QString content = index.sibling(index.row(), 0).data(Qt::DisplayRole).toString();
    
            if ( content == "red" )
            {
                return QBrush(Qt::red);
            }
            else if (content == "yellow")
            {
               return QBrush(Qt::yellow);
            }
            else if (content == "green")
            {
                return QBrush(Qt::green);
            }
        }
    
        //setzt die Daten
        return QSqlQueryModel::data(index, role);
    }
    

    Thank you for helping.



  • This really has nothing to do with Qt, it's just SQL. You can use QSqlQuery to run an UPDATE query on your database



  • Thank you for your answer.

    Can you explain it more exactly please.



  • @Mogli123 said in create a database for a tableview:

    query.exec("insert Into CustomiseToolChange values('green', 13, '01:32')");

    Here you are inserting a value. You can use the exact same technique to run an update query something like:
    query.exec("update CustomiseToolChange set COLOR='orange' where TOOLNR=13");



  • This post is deleted!


  • Thanks for your help.

    How can I insert a array with 30 variable values in it?
    The database is in a .h file is this a problem?
    The data from my code above are wrong, they are only test data.
    First of all I want fill the database only with the data from the array.
    Later I want to add some other data.

    Sorry for that if the questions are basic.
    I'm a noob.



  • http://doc.qt.io/qt-5/qsqlquery.html#execBatch

    or just call query.exec in a loop



  • Thank you very much it works

    thats my test example

        int lengthTableCustomiseToolChange = 30;
        QVector<qint32> tableCustomiseToolChange(lengthTableCustomiseToolChange);
        for(int i = 0; i < lengthTableCustomiseToolChange; i++)
        {
            tableCustomiseToolChange[i] = i;
            qDebug()<<"Testwert"<<tableCustomiseToolChange[i];
    
        }
        qDebug()<<"aktueller Wert ="<<tableCustomiseToolChange[19];
    
        for (int i=0; i<(lengthTableCustomiseToolChange/3); i++)
        {
            int j;
    
        QSqlQuery query;
        query.exec("Create Table CustomiseToolChange (POCKETSTATUS varchar(20), "
                                   "TOOLNR int primary key, TIME varchar(20), COLOR varchar(20))");
            query.prepare("INSERT INTO CustomiseToolChange (POCKETSTATUS, TOOLNR, TIME, COLOR) "
                          "VALUES (:POCKETSTATUS, :TOOLNR, :TIME, :COLOR)");
            query.bindValue(":POCKETSTATUS", tableCustomiseToolChange[j = i * 3 + 0]);
            query.bindValue(":TOOLNR", tableCustomiseToolChange[j = i * 3 + 1]);
            query.bindValue(":TIME", tableCustomiseToolChange[j = i * 3 + 2]);
            query.bindValue(":COLOR", "");
            query.exec();
         }
    


  • I would move the query.exec("Create Table part outside of the loop



  • Thank you for this tip :)


Log in to reply