Sqlite change Row Value
-
wrote on 23 Oct 2018, 17:32 last edited by Aioria
@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(); } }
-
@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(); } }
@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?
-
@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?
wrote on 24 Oct 2018, 13:25 last edited byI 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
-
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
wrote on 24 Oct 2018, 13:32 last edited by@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 ofexec()
at least in case there is an error? Print out the value ofi
and make sure that is the desiredid
in the table. Otherwise do you have anything like SQL Workbench for SQLite where you can test these statements? -
@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 ofexec()
at least in case there is an error? Print out the value ofi
and make sure that is the desiredid
in the table. Otherwise do you have anything like SQL Workbench for SQLite where you can test these statements?wrote on 25 Oct 2018, 00:36 last edited by@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.
-
@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.
@Aioria said in Sqlite change Row Value:
I don't have Workbench
You can simply use the command line tool delivered together with SQLite.
-
Hi
There is also
https://sqlitebrowser.org/ -
@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.
wrote on 25 Oct 2018, 07:06 last edited by JonB-
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 theupdate.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.
-
-
wrote on 25 Oct 2018, 08:03 last edited by
Quick question: how do you add the checkboxes to
ui->listWidget
? -
-
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 theupdate.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.
-
-
wrote on 25 Oct 2018, 19:15 last edited by
@VRonin @JonB
Yes, here i add the checkboxesint 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
-
-
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 theupdate.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.
wrote on 26 Oct 2018, 00:37 last edited by Aioria@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
-
-
wrote on 5 Nov 2018, 21:10 last edited by
@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!
-
@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!
@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...
-
@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...@Aioria Are you sure the row number in the list widget is the correct ID in the table?!
-
wrote on 7 Nov 2018, 17:57 last edited by
@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
-
@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
wrote on 7 Nov 2018, 18:36 last edited by -
@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.
-
@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.
wrote on 7 Nov 2018, 20:55 last edited by@SGaist got it, it's learnt since now on forward. Thank you for the clarification.