SQLite3 Master/Detail | Can't get data in the Detail table

? Offline

As usual, I'm in over my head. I'm past this point using another IDE, but that IDE was lacking. So I thought I'd give Qt a try. And now I'm having trouble getting my detail table to show data.

The two tables are like this:

Master:

query = "CREATE TABLE IF NOT EXISTS recipes("
                "recipe_id INTEGER PRIMARY KEY AUTOINCREMENT,"
                "name VARCHAR(50),"
                "note VARCHAR(1000),"
                "standard INTEGER);";

Detail:

query2 = "CREATE TABLE IF NOT EXISTS recipe_ingredients("
                "ingredient_id INTEGER PRIMARY KEY AUTOINCREMENT, "
                "ingredient TEXT, "
                "amount FLOAT, "
                "inventory_id INTEGER, "
                "recipe_id INTEGER, "
                "FOREIGN KEY(recipe_id) REFERENCES recipes(recipe_id), "
                "FOREIGN KEY(inventory_id) REFERENCES inventory(inventory_id) "
                "ON DELETE CASCADE ON UPDATE CASCADE)";

I'm filling the Master table (QTableWidget):

void MainWindow::FillTable()
{
    loading = true;
    QSqlQuery q(db);

    int num_rows, r, c;
    if (!q.exec("SELECT COUNT(*)FROM recipes")) db.lastError().text();
    q.first();
    num_rows = q.value(0).toInt();

    ui->tw_recipes->setRowCount(num_rows);

    if (!q.exec("SELECT recipe_id, name FROM recipes "
                "ORDER BY name")) db.lastError().text();
    for (r = 0, q.first(); q.isValid(); q.next(), ++r)
    {
        for (c = 0; c < 2; ++c)
        {
            ui->tw_recipes->setItem(r, c, new QTableWidgetItem(q.value(c).toString()));
        }
    }
    loading = false;
}

That works fine. The recipe names names load as expected. But, when I click on a recipe name (activate it), I get nothing in the second QTableWidget..

void MainWindow::on_tw_recipes_cellActivated(int row, int column)
{
    QSqlQuery q(db);
    
    int num_rows, r, c;

    if (!q.exec("SELECT COUNT(*)FROM recipe_ingredients "
                "WHERE recipe_id = :id")) db.lastError().text();
    q.bindValue(":id", ui->tw_recipes->item(row, 0)->text().toInt());
    q.first();
    num_rows = q.value(0).toInt();

    ui->tw_recipeIngredients->setRowCount(num_rows);

    if (!q.exec("SELECT ingredient, amount "
                "FROM recipe_ingredients "
                "WHERE recipe_id = :id")) db.lastError().text();
    q.bindValue(":id", ui->tw_recipes->item(row, 0)->text().toInt());
    for (r = 0, q.first(); q.isValid(); q.next(), ++r)
    {
        for (c = 0; c < 2; ++c)
        {
             ui->tw_recipeIngredients->setItem(r, c, new QTableWidgetItem(q.value(c).toString()));
        }
    }
}

This is my first time doing anything like this with Qt or C++. I know my code is tangled up there, but I don't know enough to untangle it. I could use a little help. Thanks.

JonB

@landslyde
I assume you have checked that on_tw_recipes_cellActivated() is actually called correctly, and is issuing the SQL statements, and gets back the right numbers of rows & columns....?

Try ensuring that tw_recipeIngredients has the correct number of columns via setColumnCount(), in case....?

It won't solve whatever your problem is, but in the long run I wouldn't be issuing a separate, initial SELECT COUNT(*) query to get the number of rows to expect each time. This is not good for a variety of reasons. Just do the second SELECT to get the actual rows, read them while (q.isValid()), don't do the QTableWidget::setRowCount(), just append the rows as needed via http://doc.qt.io/qt-5/qtablewidget.html#insertRow.

? Offline

@JonB - I appreciate your input. I removed the SELECT COUNT(*) and changed to while (q.isValid()). That's a lot cleaner. And the change messed up the recipe names from printing out to the first table, I'll take the time to dig in deeper and see what's wrong with it now and work toward having a solid understanding of what I'm doing. Like I said, this is all new to me. The code I showed was some I got from a youtube vid. So I seriously appreciate you taking the time to send in a better direction. Thanks.

On another note: How do you get the highlighted text? Red on the light red background.

mrjj

@landslyde
Red text

`test`

test

? Offline

@JonB - I changed the first function to this:

void MainWindow::FillTable()
{
    loading = true;
    QSqlQuery q(db);

    int r = 0, c = 0;
    
    qDebug()<< "Before SELECT";
    if (!q.exec("SELECT recipe_id, name FROM recipes "
                "ORDER BY name")) db.lastError().text();
    qDebug()<< "After SELECT";
    qDebug() << r;
    ++r;
    qDebug() << r;
    qDebug() << q.isValid();
    while (q.isValid())  // RETURNS FALSE EACH TIME NOW. THIS TABLE HAS 4 ENTRIES IN IT.
    {
        qDebug() << "Inside WHILE";
       // ++r;
        ui->tw_recipes->insertRow(r);
        for (c = 0; c < 2; ++c)
        {
            qDebug() << c;
            qDebug()<< "Inside FOR";
            ui->tw_recipes->setItem(r, c, new QTableWidgetItem(q.value(c).toString()));
        }
    }
    loading = false;
}

My output is:

09:46:25: Starting /home/slyde/Desktop/Qt Apps/build-Crumbs-Desktop_Qt_5_11_0_GCC_64bit-Debug/Crumbs...
Before SELECT
After SELECT
0
1
false
09:46:34: /home/slyde/Desktop/Qt Apps/build-Crumbs-Desktop_Qt_5_11_0_GCC_64bit-Debug/Crumbs exited with code 0

Can anyone tell me why

while (q.isValid())

is failing? I see nothing wrong with my query. But obviously something's fouled up. Anyone?
alt text
Not sure how to post a pic here. So here's a link to show the table's valid entries: https://imgur.com/L29lGbb

? Offline

@mrjj - Thank you.

mrjj

@landslyde
Hi
Why are you using q.isValid() and not
http://doc.qt.io/qt-5/qsqlquery.html#next

Normally to loop the result is like

   QSqlQuery query("SELECT country FROM artist");
    while (query.next()) {
        QString country = query.value(0).toString();
        ...      
    }

Did i miss something ?

JonB

@landslyde
I copied your use of q.isValid() from your code, I didn't look it up. @mrjj's code is the correct way. Also that might explain your problems...!

? Offline

@mrjj - That was the key to the door. I'm learning. I'm not here to roach code, just to learn. I just didn't know enough to sort that out and get it headed in the right direction. And thank you for the link. Pure gold!

? Offline

@JonB - Your input was appreciated nonetheless. My initial code up there was a hack from a video I watched. While it worked, it wasn't even close to what I know Qt can offer. I hope you guys don't mind my questions. I'm full of them. Really enjoying Qt. Hands down, it's the best!

mrjj

@landslyde
We basically hang around here for questions. ;)
So if you show code,
clearly state what you tried,
what you got and what you expected you will find us
very willing to try to help.

Also the online Docs are excellent.

? Offline

@mrjj - Thank you very much for the welcome. I do have one more question regarding all we've talked abt here. Although my initial post on this was sloppy code, it did fill the TableWidget properly. Now it won't load the four names I have in it.

void MainWindow::FillTable()
{
    loading = true;
    QSqlQuery q(db);

    int r = 0, c = 0;

    if (!q.exec("SELECT recipe_id, name FROM recipes "
                "ORDER BY name")) db.lastError().text();
    while (q.next())
    {
        ++r;
        ui->tw_recipes->insertRow(r);
        for (c = 0; c < 2; ++c)
        {
            qDebug() << r;
            qDebug() << c;
            qDebug() << q.value(c);
            ui->tw_recipes->setItem(r, c, new QTableWidgetItem(q.value(c).toString()));
        }
    }
    loading = false;
}

Output is:

10:55:47: Starting /home/slyde/Desktop/Qt Apps/build-Crumbs-Desktop_Qt_5_11_0_GCC_64bit-Debug/Crumbs...
1
0
QVariant(qlonglong, 3)
1
1
QVariant(QString, "Cat Finger Licks")
2
0
QVariant(qlonglong, 4)
2
1
QVariant(QString, "Chocolate Chip Cookies")
3
0
QVariant(qlonglong, 2)
3
1
QVariant(QString, "Lemon Cookies")
4
0
QVariant(qlonglong, 1)
4
1
QVariant(QString, "Oatmeal Pecan Chewy")

This line of code worked before, but now it doesn't:

ui->tw_recipes->setItem(r, c, new QTableWidgetItem(q.value(c).toString()));

Why wld it not work? Is there something wrong with it? Please advise.

Pics of this: https://imgur.com/MsJ9xac and https://imgur.com/iQ4mGoI

UPDATE
I added a line to it and see that when I insert a row, nothing happens. Look:

void MainWindow::FillTable()
{
    loading = true;
    QSqlQuery q(db);

    int r = 0, c = 0;

    if (!q.exec("SELECT recipe_id, name FROM recipes "
                "ORDER BY name")) db.lastError().text();
    while (q.next())
    {
        ++r;
        ui->tw_recipes->insertRow(r);  // NOTHING HAPPENS HERE
        for (c = 0; c < 2; ++c)
        {
            qDebug() << r;
            qDebug() << c;
            qDebug() << ui->tw_recipes->currentRow();  // I ADDED THIS LINE TO IT
            qDebug() << q.value(c);
            ui->tw_recipes->setItem(r, c, new QTableWidgetItem(q.value(c).toString()));
        }
    }
    loading = false;
}

And the output:

11:18:16: Starting /home/slyde/Desktop/Qt Apps/build-Crumbs-Desktop_Qt_5_11_0_GCC_64bit-Debug/Crumbs...
1
0
-1
QVariant(qlonglong, 3)
1
1
-1
QVariant(QString, "Cat Finger Licks")
2
0
-1
QVariant(qlonglong, 4)
2
1
-1
QVariant(QString, "Chocolate Chip Cookies")
3
0
-1
QVariant(qlonglong, 2)
3
1
-1
QVariant(QString, "Lemon Cookies")
4
0
-1
QVariant(qlonglong, 1)
4
1
-1
QVariant(QString, "Oatmeal Pecan Chewy")

What's making it be -1?

mrjj

Hi
I assume TW is tableWidget ?
Please read this. it shows how its expected to be used.
https://wiki.qt.io/How_to_Use_QTableWidget

You might need to tell it how many to expect/have
m_pTableWidget->setRowCount(10);
m_pTableWidget->setColumnCount(3);

normally to add one new row, you can do
tableWidget->insertRow( tableWidget->rowCount() );

The -1 is most likely
qDebug() << ui->tw_recipes->currentRow();
which means None selected. (since nothing been clicked on)

also
++r; // is 1
ui->tw_recipes->insertRow(r); // ask it to add row at 1 but it dont have row zero so it cant.

? Offline

@mrjj - Finally got it! Thanks for all your help.

void MainWindow::FillTable()
{
    loading = true;
    QSqlQuery q(db);

    int r = 0, c = 0;
    if (!q.exec("SELECT COUNT(*) FROM recipes")) db.lastError().text();
    q.first();
    int rCount = q.value(0).toInt();

    if (!q.exec("SELECT recipe_id, name FROM recipes "
                "ORDER BY name")) db.lastError().text();
    while (q.next())
    {
        ui->tw_recipes->setRowCount(rCount);
        for (c = 0; c < 2; ++c)
        {
            ui->tw_recipes->setItem(r, c, new QTableWidgetItem(q.value(c).toString()));
        }
        ++r;
    }
    loading = false;
}

mrjj

Hi
super.
One note.
should it not be
int rCount = q.value(0).toInt();
ui->tw_recipes->setRowCount(rCount);

instead of inside the while loop ?

JonB

@landslyde
Now be brave and remove the whole SELECT COUNT(*) and the setRowCount(), do it with insertRow() instead. Do it now! You know it makes sense ;-)

Separately, your db.lastError().text(); just returns the text, you won't see anything when your exec()s fail. You will want more like:

qDebug << db.lastError().text();
return ...;

? Offline

@JonB - I cldnt make inserRow() work. I'll mess around with it, but not just yet. Yes, it makes more sense doing it that way. I had the variable r stuck in it: insertRow(r), which seems right to me. But it was DOA each time I ran it. I'll work with it though. And I appreciate all of your help, including the addition that came in while I was responding here, regarding using qDebug for lastError(). Thanks.

UPDATE
Works like a charm! =)

void MainWindow::FillTable()
{
    loading = true;
    QSqlQuery q(db);

    int r = 0, c = 0;

    if (!q.exec("SELECT recipe_id, name FROM recipes "
                "ORDER BY name")) 
        qDebug() << db.lastError().text();
    while (q.next())
    {
        ui->tw_recipes->insertRow(r);
        for (c = 0; c < 2; ++c)
        {
            ui->tw_recipes->setItem(r, c, new QTableWidgetItem(q.value(c).toString()));
        }
        ++r;
    }
    loading = false;
}

? Offline

@mrjj - Both ways work. But one looks cleaner than the other: yours =) Anyway, the ribs are ready to be pulled out of the smoker, and that means I'm out of here! =)

Thank you for all your help. I'll get there...in time.