Problems with Open-Source Downloads read https://www.qt.io/blog/problem-with-open-source-downloads and https://forum.qt.io/post/638946

Sqlite change Row Value



  • Hi! im trying to do an app like Teacher`s assistence and i have one problem when i try to save the state of a checkBox. i need to increment in one the value of the faults but i cant do that.
    Here is my code:

    void MainWindow::saveAssistence()
    {
        QString consult;
        consult.append("UPDATE student SET faults = 2 WHERE id =1");
    
    
        QSqlQuery update;
        update.prepare(consult);
    
        if(update.exec()){
            qDebug()<<"the student is successful update.";
        }else{
            qDebug()<<"the student is NOT successful update.";
            qDebug()<<"ERROR! " << update.lastError();
        }
    
    }
    
    

    and when i check if the checkBox is unchecked to increment the value of "Faults" i can't do that because i change all over the Column of "Faults"

    void MainWindow::chequedState(){
    
        int rowCount = ui->listWidget->count();
        for (int i=0; i<rowCount; i++)
        {
            if(ui->listWidget->item(i)->checkState() == Qt::Unchecked)
               saveAssistence();
        }
    }
    
    void MainWindow::on_pushButton_clicked()
    {
    
        chequedState();
        ShowData();
    }
    

    I don't know how to do the consult so that only cell "Faults" increment the value in just one.
    Thank you!!



  • @Aioria

    1. . Make saveAssistence() accept a faults parameter, which it inserts into the "UPDATE student SET faults = 2 WHERE id =1"; line in place of the 2 you have (or do it via a SQL parameter).

    2. In function chequedState(), do not call saveAssistence() as you iterate through each checkbox. Instead, increment a faults variable, so that at the end of the loop it holds the total number of faults. Then call saveAssistence(faults) once at the end.



  • @JonB Thank you for the answer. Now, how can i do for do it with the parameter in sql. Instead this,

    consult.append("UPDATE student SET faults = 2 WHERE id =1");
    

    do it somehow like this:

    consult.append("UPDATE student SET faults = +faults+");
    

    i try it but i don't find the way to do it well. i want that when i uncheck a checkbox, the faults increments 1 when i save the state.
    Thank you very much!


  • Lifetime Qt Champion

    @Aioria
    Hi
    Please see documentation for using parameters
    http://doc.qt.io/qt-5/qsqlquery.html
    section Approaches to Binding Values



  • Thank you! i can do the UPDATE, but i can't make it work yet.

    void MainWindow::saveAssistence(int faults, int id)
    {
        QString consult;
        consult.append("UPDATE students SET faults = '"+QString::number(faults)+"' WHERE id = '"+QString::number(id)+"'");
        //
    
    
        QSqlQuery update;
        update.prepare(consult);
    
      if(update.exec()){
            qDebug()<<"the student is successful update.";
        }else{
            qDebug()<<"the student is NOT successful update.";
            qDebug()<<"ERROR! " << update.lastError();
        }
    
    }
    
    
    void MainWindow::chequedState(){
        int faults=0;
        int id = 1;
    
        int rowCount = ui->listWidget->count();
        for (int i=0; i<rowCount; i++)
        {
            if(ui->listWidget->item(i)->checkState() == Qt::Unchecked){
                faults++;
                saveAssistence(faults, id);
            }
        }
    }
    

    The thing is that when i want to do the increment to the value, i cant do it. I have 4 students and 4 checkboxes, i want to add 1 value to faults when i uncheck the checkboxes and save the state, in the save button i call chequedState function. But i can't do this work. I tried putting the function out of the for but its the same result. Also i try to use just the parameter "Faults " but don't work.


  • Lifetime Qt Champion

    Hi
    Check the return value of both prepare and exec()
    to see if it reports anything.
    if exec() fails, you can obtain more information with
    http://doc.qt.io/qt-5/qsqlquery.html#lastError



  • @Aioria

    1. You have not followed my/ @mrjj's suggestion of "binding values" to pass the two parameters to the SQL query. You should really read the link he gave you and do it the "proper" way described there.

    2. In the way you do it: Originally you passed the two parameters as numbers
      UPDATE student SET faults = 2 WHERE id =1
      I note that that in the new code you will have ' (single-quote) characters around each number, so it will end up like
      UPDATE student SET faults = '2' WHERE id ='1'
      Depending on your SQL server, that may not work.

    3. You have left the saveAssistence(faults, id); statement inside the for loop. I said to do it once at the end, outside the for loop. Does it not make sense to you to do it like that?



  • Yes, i read it and i use the UPDATE because works to increment the values, if you read what i said, i tried to put the saveAsistance outside of the for but don't do nothing. I don't find anything about UPDATE in the Binding Values, just INSERT



  • @Aioria
    First let's deal with the use of binding variables. The fact that the examples only tend to show it being used with INSERT is not relevant. They can be used with any SQL statement, be that SELECT, INSERT, UPDATE, DELETE or whatever, so long as you go prepare(), then bindValue(), then exec(). It's just a neater way of getting a variable safely into the statement in the right format in the right place than putting it directly into the string via something like QString::number().

    Now your issue. It's not your fault, but I/we find the description of what you are trying to achieve difficult to follow. From the way you want your exec() to be inside the for loop, I now have a feeling you want (potentially) multiple rows to each be updated separately. Does the following describe your situation:

    1. You have some existing rows in the SQL table.
    2. Each row has an id, and a faults counter of its own.
    3. When you go through the checkboxes: if a given row's checkbox is unchecked, you want just that row's faults column to be incremented by 1.

    For example:

    • Row number 2 currently has the value 5 stored in its faults in the database. The teacher does not check its checkbox. So that means you want that row's faults to be incremented to 6.
    • Meanwhile, another row with value 0 also has its checkbox unchecked, so that row wants it incremented to 1.
    • And a third row has its checkbox checked, so you do not want that row's faults incremented at all.

    Is that it?

    In this case, the secret is that you can write your SQL query so that the database side does the incrementing for you, using whatever faults value a given row (specified by id) has currently to add to. Your Qt client does not need to know the number of faults already there. Something like:

    for (int i = 0; i < rowCount; i++)
    {
        if (ui->listWidget->item(i)->checkState() == Qt::Unchecked)
        {
            QSqlQuery update;
            update.prepare("UPDATE students SET faults = faults + 1 WHERE id = :id");
            update.bindValue(":id", i);
            update.exec()
        }
    }
    
    


  • @JonB
    Thank you for the answer. I have another question. I tried that but not made changes. I have to reescribe the entire code about CREATE the table? I do that with QString. Maybe i need to do it with QSqlQuery. Here is the code:

    void MainWindow::CreateStudentTable()
    {
        QString consult;
        consult.append("CREATE TABLE IF NOT EXISTS students("
                        "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                       "name VARCHAR(100),"
                        "lastName VARCHAR(100), "
                        "faults INTEGER NOT NULL"
                       ");");
    
        QSqlQuery create;
        create.prepare(consult);
    
        if(create.exec()){
            qDebug()<<"The table STUDENT exists.";
        }else{
            qDebug()<<"The table STUDENTS doesnt exists";
            qDebug()<<"ERROR! " << create.lastError();
        }
    }
    

  • Lifetime Qt Champion

    @Aioria said in Sqlite change Row Value:

    I tried that but not made changes

    What do you mean? Was the table not created? What does create.lastError() return?



  • @jsulm

    I mean that i do this

    for (int i = 0; i < rowCount; i++)
    {
        if (ui->listWidget->item(i)->checkState() == Qt::Unchecked)
        {
            QSqlQuery update;
            update.prepare("UPDATE students SET faults = faults + 1 WHERE id = :id");
            update.bindValue(":id", i);
            update.exec()
        }
    }
    

    And when i want to save the state, don't increment the faults. I think that the problem is in ID and Faults but i can't make it work



  • @Aioria
    In principle this code looks correct/reasonable. Don't understand what you say is the problem? Does the above not increment correctly. You should check the return result of exec() at least in case there is an error? Print out the value of i and make sure that is the desired id in the table. Otherwise do you have anything like SQL Workbench for SQLite where you can test these statements?



  • @JonB
    Yes, i don't understand why. I do the test and there is no error. I can copy the code and see if can get the error. I don't have Workbench, you say that if i try the statements in the workbench maybe can see the error?

    Here is the code:

    MainWindow::MainWindow(QWidget *parent) :
        QMainWindow(parent),
        ui(new Ui::MainWindow)
    {
        ui->setupUi(this);
    
    
        qDebug()<<"init application...";
    
        QString name;
        name.append("Datbase1.sqlite");
    
        db = QSqlDatabase::addDatabase("QSQLITE");
        db.setDatabaseName(name);
    
        if (!QSqlDatabase::isDriverAvailable("QSQLITE")){
            qDebug()<< "Error: QSQLITE is not available";
        }
    
        if(db.open()) {
            qDebug()<<"Successfull Conected .";
        }else{
                qDebug()<<"ERROR! NOT connected to the database.";
            }
    
        CreateStudentTable();
        int row = ShowData();
        setWindowTitle(QString ("Assistance"));
        QList<QString> items;
        for (int i = 0; i<row;i++)
        {
            items.append("" + QString::number(i));
        }
        QListIterator <QString> itr(items);
        while(itr.hasNext())
        {
            QListWidgetItem *item = new QListWidgetItem(itr.next());
            item->setCheckState(Qt::Unchecked);
            ui->listWidget->addItem(item);
        }
    
        ShowData();
    }
    
    
    void MainWindow::insertStudent()
    {
    
        QSqlQuery consult;
        consult.prepare("INSERT INTO students("
                        "name,"
                         "lastName, "
                         "faults)"
                         "VALUES("
                          "'"+ui->lineEditName->text()+"',"
                          "'"+ui->lineEditLastName->text()+"',"
                          "'"+ui->lineEditFaults->text()+"'"
                         ");");
    
    
       if(consult.exec()){
           qDebug()<<"The Student is added.";
       }else{
           qDebug()<<"The Student IS NOT added";
           qDebug()<<"ERROR! " << consult.lastError();
       }
    }
    
    void MainWindow::deleteStudent()
    {
        QString consult;
        consult.append("DELETE FROM students WHERE name= '"+ui->lineEditName->text()+"'and lastName= '"+ui->lineEditLastName->text()+"'");
    
    
        QSqlQuery deleteS;
        deleteS.prepare(consult);
    
        if(deleteS.exec()){
            qDebug()<<"The student is successfull deleted.";
        }else{
            qDebug()<<"The student IS NOT successfull deleted";
            qDebug()<<"ERROR! " << deleteS.lastError();
        }
    
    }
    
    int MainWindow::ShowData()
    {
        QString consult;
        consult.append("SELECT * FROM students");
    
        QSqlQuery show;
        show.prepare(consult);
    
        if(show.exec()){
            qDebug()<<"The STUDENT has consulted correctly.";
        }else{
            qDebug()<<"The STUDENT HAS NOT consulted correctly";
            qDebug()<<"ERROR! " << show.lastError();
        }
    
        int row = 0;
        ui->tableWidgetData->setRowCount(0);
    
    
        while(show.next()){
            ui->tableWidgetData->insertRow(row);
            ui->tableWidgetData->setItem(row,0,new QTableWidgetItem (show.value(1).toByteArray().constData()));
            ui->tableWidgetData->setItem(row,1,new QTableWidgetItem (show.value(2).toByteArray().constData()));
            ui->tableWidgetData->setItem(row,2,new QTableWidgetItem (show.value(3).toByteArray().constData()));
            row++;
        }
        ui->tableWidgetData->horizontalHeader()->setStretchLastSection(true);
        return row;
    }
    
    
    void MainWindow::chequedState(){
    
        int rowCount = ui->listWidget->count();
    
        for (int i = 0; i < rowCount; i++)
        {
            if (ui->listWidget->item(i)->checkState() == Qt::Unchecked)
            {
                QSqlQuery update;
                update.prepare("UPDATE students SET faults = faults + 1 WHERE id = :id");
                update.bindValue(":id", i);
                update.exec();
            }
        }
    
    }
    
    
    void MainWindow::on_pushButtonAddStudent_clicked()
    {
        insertStudent();
        ShowData();
    }
    
    
    
    void MainWindow::on_pushButtonDeleteStudent_clicked()
    {
        deleteStudent();
        ShowData();
    }
    
    
    
    void MainWindow::on_pushButton_clicked()
    {
    
        chequedState();
        ShowData();
    }
    
    

    Maybe the error is in the Buttons or in the list of the check boxes that are separate from the table.


  • Lifetime Qt Champion

    @Aioria said in Sqlite change Row Value:

    I don't have Workbench

    You can simply use the command line tool delivered together with SQLite.


  • Lifetime Qt Champion

    Hi
    There is also
    https://sqlitebrowser.org/



  • @Aioria

    • If you are saying you are not sure whether your chequedState() is even being hit, you should put in a debug statement to verify you are getting there.

    • You still have not checked the return result of the update.prepare("UPDATE students SET faults = faults + 1 WHERE id = :id"); which you say is not working. [EDIT Sorry, I meant the update.exec() which follows that line.]

    • You can check your statement by typing

    UPDATE students SET faults = faults + 1 WHERE id = 1
    

    into a SQLite command-line/client tool. You can & should get this set up. Once you have done so you can test your proposed SQL statements directly, before you put them into your program. This makes it easier to develop correctly.



  • Quick question: how do you add the checkboxes to ui->listWidget?



  • @JonB
    Yes i did that and dont throw a error and i delete it from the code. I will try to do that in the page of sqlite



  • @VRonin @JonB
    Yes, here i add the checkboxes

    int row = ShowData();
        setWindowTitle(QString ("Assistance"));
        QList<QString> items;
        for (int i = 0; i<row;i++)
        {
            items.append("" + QString::number(i));
        }
        QListIterator <QString> itr(items);
        while(itr.hasNext())
        {
            QListWidgetItem *item = new QListWidgetItem(itr.next());
            item->setCheckState(Qt::Unchecked);
            ui->listWidget->addItem(item);
        }
    
    

    Maybe the error ocurred here in the creation of the checkboxes and i need to do it in other function and other way



  • @JonB I test it and if i do this:

    UPDATE students SET faults = faults + 1 WHERE id = id
    

    then increment by 3 all the faults and if i uncheck one, increment all by 1. So now the thing is in the for loop. im trying to change it



  • @JonB @jsulm
    Can anyone help me with this last thing?

    void MainWindow::chequedState(int i){
    
        if (ui->listWidget->item(i)->checkState() == Qt::Unchecked)
        {
            QSqlQuery update;
            update.bindValue(":id", i);
            update.prepare("UPDATE students SET faults = faults + 1 WHERE id");
            update.exec();
        }
    
    
    }
    /void MainWindow::saveAssistence()
    {
        int rowCount = ui->listWidget->count();
    
        for (int i = 0; i < rowCount; i++)
        {
            chequedState(i);
        }
    
    
    }
    

    I have this 2 functions and increments in 3 instead in 1 each row.

    Thank you!


  • Lifetime Qt Champion

    @Aioria said in Sqlite change Row Value:

    UPDATE students SET faults = faults + 1 WHERE id

    please take a closer look at the WHERE part of your update query...



  • @jsulm
    I tried to put =: id but don't do nothing. I think that the problem is in the for. Maybe another way to do it. This increment all the rows in the quantity of rows that the table have...


  • Lifetime Qt Champion

    @Aioria Are you sure the row number in the list widget is the correct ID in the table?!



  • @jsulm I think with the :id, i , i put the value of i in the id.
    How can i check that and how can i do to make an union between the list and the table?

    CreateStudentTable();
        int row = ShowData();
        setWindowTitle(QString ("Asistencia"));
        QList<QString> items;
        for (int i = 0; i<row;i++)
        {
            items.append("" + QString::number(i));
        }
        QListIterator <QString> itr(items);
        while(itr.hasNext())
        {
            QListWidgetItem *item = new QListWidgetItem(itr.next());
            item->setCheckState(Qt::Unchecked);
            ui->listWidget->addItem(item);
        }
    
        ShowData();
    

    This is the code that creates the list with the checkbox



  • @Aioria please try to not double post. I know, that post is Spanish but you're trying to solve the same issue :-)


  • Lifetime Qt Champion

    @Pablo-J-Rogina it's usually the only case where double posting is allowed since it's in the poster native language and therefor might get an answer that's easier to understand.



  • @SGaist got it, it's learnt since now on forward. Thank you for the clarification.


  • Lifetime Qt Champion

    @Aioria said in Sqlite change Row Value:

    I think with the :id, i , i put the value of i in the id

    Yes, you do and this is a bad idea. The id of an item in the database should not be its row number in some widget in your UI! What happens, for example, if you order/sort the items differently? Then suddenly all your IDs are wrong.
    You can use http://doc.qt.io/qt-5/qlistwidgetitem.html#setData to set the database ID in your items and then get it using http://doc.qt.io/qt-5/qlistwidgetitem.html#data



  • @jsulm
    Thank you! I will try to do that instead the bindvalue!


Log in to reply