Sql query to retrieve database table data

[Cyrebo]

Hi, how do I retrieve certain content like name from a table? I want to retrieve all the names and then set them to a label in my form. For now I just want to how to get all the names. So far I've tried using a select count query to first get the number of rows but it didn't work. I'm not sure what functions to use for this if anyone can please help.

Here's what I have done:

db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(Path_to_DB);
    QFileInfo checkFile(Path_to_DB);
 
    if(checkFile.isFile())
    {
        if(db.open())
        {
            qDebug() << "Connected to database file";
        }
    }else{
        qDebug() << "Database file not found";
    }
 
    QSqlQuery query;
    QString qry = QString("SELECT Count(*) FROM customers");
 
    query.prepare(qry);
 
    if (query.exec())
    {
        int count = query.result(); //<<<Where I'm stuck
        qDebug() << count;
        for ( int i = 0; i < count; i++)
        {
            //create labels for each customer
            QLabel *label = new QLabel(QString());
            QCheckBox *chkbox = new QCheckBox;
 
            Ui::MainWindow *ui;
 
            ui->gridLayout->addWidget(label,0,0);
            ui->gridLayout->addWidget(chkbox,0,1);
 
        }
    }
    else
    {
        query.lastError();
    }

db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(Path_to_DB);
    QFileInfo checkFile(Path_to_DB);

    if(checkFile.isFile())
    {
        if(db.open())
        {
            qDebug() << "Connected to database file";
        }
    }else{
        qDebug() << "Database file not found";
    }

    QSqlQuery query;
    QString qry = QString("SELECT Count(*) FROM customers");

    query.prepare(qry);

    if (query.exec())
    {
        int count = query.result(); //<<<Where I'm stuck
        qDebug() << count;
        for ( int i = 0; i < count; i++)
        {
            //create labels for each customer
            QLabel *label = new QLabel(QString());
            QCheckBox *chkbox = new QCheckBox;

            Ui::MainWindow *ui;

            ui->gridLayout->addWidget(label,0,0);
            ui->gridLayout->addWidget(chkbox,0,1);

        }
    }
    else
    {
        query.lastError();
    }

To copy to clipboard, switch view to plain text mode 

Thanks in advance!

[Lykurg]

Hi,

see QSqlQuery::value(). Further there is no need to query the total size first. Just do

SELECT name FROM customers

SELECT name FROM customers

To copy to clipboard, switch view to plain text mode 

and then loop through it using QSqlQuery::next(). This way you can replace your current for loop.


EDIT: And here you do not need prepare!

[Cyrebo]

Thanks for the quick response, the sql is sort of working because this will not actually let me implement the second part of my code..as you can see the last half of my code is commented out because my program flat out crashes when its not. I get the dreaded "The program has finished unexpectedly error" that gives no feedback on what caused it to so I'm not sure what I'm doing wrong here...

How would I implement a loop to create a label and checkbox for each item in the db individually?

QSqlQuery qry;
 
    if (qry.exec("SELECT name FROM customers"))
    {
        while(qry.next())
        {
            qDebug() << qry.value(0).toString();
//            QLabel *label = new QLabel(QString());
//            QCheckBox *chkbox = new QCheckBox;
 
//            Ui::MainWindow *ui;
 
//            ui->gridLayout->addWidget(label,0,0);
//            ui->gridLayout->addWidget(chkbox,0,1);
        }
    }
    else
    {
        qDebug() << qry.lastError();
    }

QSqlQuery qry;

    if (qry.exec("SELECT name FROM customers"))
    {
        while(qry.next())
        {
            qDebug() << qry.value(0).toString();
//            QLabel *label = new QLabel(QString());
//            QCheckBox *chkbox = new QCheckBox;

//            Ui::MainWindow *ui;

//            ui->gridLayout->addWidget(label,0,0);
//            ui->gridLayout->addWidget(chkbox,0,1);
        }
    }
    else
    {
        qDebug() << qry.lastError();
    }

To copy to clipboard, switch view to plain text mode 

[ChrisW67]

Uhh, exactly like you have, except you would not try to put all of the labels into the same cell of the grid layout.

[Lesiok]

In line 11 You define pointer ui. Next You use this pointer in line 13 and 14 but the pointer is NOT initialised.
I suggest first learn the basics of C++.

[Cyrebo]

In line 11 You define pointer ui. Next You use this pointer in line 13 and 14 but the pointer is NOT initialised.
I suggest first learn the basics of C++.

I saw this later on and have changed my code quite a bit since that post. At the moment, only 1 label and lineEdit is showing up with 1 record, how do I make it one for each record? And my question on how to loop through all the total number of names from the sql query?

My code now:

QSqlQuery qry;
 
    if (qry.exec("SELECT name FROM customer"))
    {
        while(qry.next())
        {
            qDebug() << qry.value(0).toString();
                if(qry.isValid())
                {
                 QString cust = qry.record().value(0).toString();
                 QLabel *label = new QLabel(QString(cust));
                 QLineEdit *lineEdit = new QLineEdit;
                 lineEdit->setInputMask("0");
                 lineEdit->setMaxLength(1);
                 lineEdit->setGeometry(0,0,41,31);
                 label->setGeometry(0,0,150,41);
 
                 ui->gridLayout->addWidget(label,0,0); //<<<<<<<<<<<Fills the entire width of layout,why?
                 ui->gridLayout->addWidget(lineEdit,0,1);//<<<<<<<<<<<Fills the entire width of layout,why?
                }
        }
    }
    else
    {
        qDebug() << qry.lastError();
    }

QSqlQuery qry;

    if (qry.exec("SELECT name FROM customer"))
    {
        while(qry.next())
        {
            qDebug() << qry.value(0).toString();
                if(qry.isValid())
                {
                 QString cust = qry.record().value(0).toString();
                 QLabel *label = new QLabel(QString(cust));
                 QLineEdit *lineEdit = new QLineEdit;
                 lineEdit->setInputMask("0");
                 lineEdit->setMaxLength(1);
                 lineEdit->setGeometry(0,0,41,31);
                 label->setGeometry(0,0,150,41);

                 ui->gridLayout->addWidget(label,0,0); //<<<<<<<<<<<Fills the entire width of layout,why?
                 ui->gridLayout->addWidget(lineEdit,0,1);//<<<<<<<<<<<Fills the entire width of layout,why?
                }
        }
    }
    else
    {
        qDebug() << qry.lastError();
    }

To copy to clipboard, switch view to plain text mode 

[Lesiok]

And what if the base will be a few thousand records ? Read about QSqlTableModel

[Cyrebo]

I've managed to get it to read the names now and create a spinbox for each name rather than lineEdit/checkbox as previously. Now my problem is getting the value of the spinbox for each name like for example, the first name may get 1/2/3 value in the spinbox, how do i get this value and increment it in the database for that name? I don't know how to do this using QSqlTableModel for unknown names.

Here is where I'm at:

QSqlQuery qry;
 
    if (qry.exec("SELECT name FROM customer"))
    {
        while(qry.next())
        {
            qDebug() << qry.value(0).toString();
                if(qry.isValid())
                {
                 QString cust = qry.record().value(0).toString();
                 QLabel *label = new QLabel(QString(cust));
                 QSpinBox *spinbox = new QSpinBox;
                 spinbox->setMaximum(3);
                 label->setGeometry(0,0,80,41);
 
                 ui->verticalLayout->addWidget(label);
                 ui->verticalLayout->addWidget(spinbox);
                 }
        }
    }
    else
    {
        qDebug() << qry.lastError();
    }

QSqlQuery qry;

    if (qry.exec("SELECT name FROM customer"))
    {
        while(qry.next())
        {
            qDebug() << qry.value(0).toString();
                if(qry.isValid())
                {
                 QString cust = qry.record().value(0).toString();
                 QLabel *label = new QLabel(QString(cust));
                 QSpinBox *spinbox = new QSpinBox;
                 spinbox->setMaximum(3);
                 label->setGeometry(0,0,80,41);

                 ui->verticalLayout->addWidget(label);
                 ui->verticalLayout->addWidget(spinbox);
                 }
        }
    }
    else
    {
        qDebug() << qry.lastError();
    }

To copy to clipboard, switch view to plain text mode 

Need to know how to get the spinbox value and increment it for the name beside the spinbox in the label. I'm thinking it requires some kind of pointer but not sure...

[Lesiok]

Tell us what your problem is rather than how You try to solve it.

[Cyrebo]

Ok I want to read the value from the qspinbox I created specific to the label that the spin box that its beside. So something like name spinbox(1). I want to store that value to the person in the db table like on the form.