Solved update qsqlite and qtableview
-
Hi,
I'm creating a database (qsqlite) and i want to show the data with a qtableview.
creating the database and to show them via qtableview works so far, but at the point to update it I have problems.
It does not work like I want. The new data are at the wrong place or some data are missing.
The sice of the database remains constant, only the data changes incomming from a tcpsocket.
I hope someone can help me
If I have some bad code please tell me.
I'm a beginner and want to learn how to do it corectly.creating the database
db = QSqlDatabase::addDatabase("QSQLITE"); db.setDatabaseName(":memory:"); if (!db.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; }
queryToolChangeWarnings = new QSqlQuery; queryToolChangeWarnings->exec("CREATE TABLE ToolChangeWarnings (COLOR varchar(20)," "TOOLNR int, TIME int, POCKETSTATUS int)"); queryToolChangeWarnings->prepare("INSERT INTO ToolChangeWarnings (COLOR, TOOLNR, TIME, POCKETSTATUS) " "VALUES (:COLOR, :TOOLNR, :TIME, :POCKETSTATUS)"); queryToolChangeWarnings->bindValue(":COLOR", ""); this->close(); myTableToolChangeWarnings = new TableToolChangeWarnings(this); myTableToolChangeWarnings->move(spaceBetweenTables, spaceBetweenTables); myTableToolChangeWarnings->setMinimumHeight(tableHeight); myTableToolChangeWarnings->setMinimumWidth(tableWidth); myTableToolChangeWarnings->show(); queryManipulationToolChangeWarnings = new QSqlTableModel(this); queryManipulationToolChangeWarnings->setTable("ToolChangeWarnings"); queryManipulationToolChangeWarnings->select(); queryManipulationToolChangeWarnings->setEditStrategy(QSqlTableModel::OnFieldChange);
the new data for the database
void detailScreen::receiveVectorTool_W(QVector<int> vector) { if (vector.length() == 30) { for(qint8 i = 0; i<30; i++) { arrayToolChangeWarnings[i] = vector[i]; } if (firstFill == false) { firstFillDatabaseToolChangeWarnings(); emit fillTableNewToolChangeWarnings(); firstFill = true; } else { emit fillTableNewToolChangeWarnings(); } } }
first fill of the database
void detailScreen::firstFillDatabaseToolChangeWarnings() { for (int i = 0; i < 10; i++) { queryToolChangeWarnings->bindValue(":TOOLNR", arrayToolChangeWarnings[i * 3 + 0]); queryToolChangeWarnings->bindValue(":TIME", arrayToolChangeWarnings[i * 3 + 1]); queryToolChangeWarnings->bindValue(":POCKETSTATUS", arrayToolChangeWarnings[i * 3 + 2]); queryToolChangeWarnings->exec(); } }
update the data
void detailScreen::fillDatabaseToolChangeWarnings() { qint8 i = 0; for (qint8 indexRow = 0; indexRow < 10; indexRow ++) { for (qint8 indexColumn = 1; indexColumn < 4; indexColumn ++) { queryManipulationToolChangeWarnings->setData(queryManipulationToolChangeWarnings-> index(indexRow,indexColumn), arrayToolChangeWarnings[i]); queryManipulationToolChangeWarnings->submit(); i++; } } emit refreshTableToolChangeWarnings(); }
in my table clase I try to update the data via signal & slot
void TableToolChangeWarnings::updateTable() { myModelToolChangeWarnings->setQuery("SELECT * FROM ToolChangeWarnings"); };
If you need more informations please ask
Thank you in advance
regards -
Hi,
@Mogli123 said in update qsqlite and qtableview:
myModelToolChangeWarnings
What is that class ?
Can you explain more precisely what you mean by "at the wrong place" ?
-
Hi, thank you for answering
myModelToolChangeWarnings inherits from QSqlQueryModelSGaist:"Can you explain more precisely what you mean by "at the wrong place" ?"
To say: "they are at the wrong place" is wrong. Sorry for that.
I think I'm overiting the old data wrong in the database.
To write the data the first time to the database works but updating doesn't work.
I don't know how exactly to do that.Is that the write way to update the database or is there a better solution?
-
@Mogli123 To update existing data you use an UPDATE SQL query. How does your UPDATE query look like?
-
@jsulm
I tried it like this wayvoid detailScreen::fillDatabaseToolChangeWarnings() { qint8 i = 0; for (qint8 indexRow = 0; indexRow < 10; indexRow ++) { for (qint8 indexColumn = 1; indexColumn < 4; indexColumn ++) { queryManipulationToolChangeWarnings->setData(queryManipulationToolChangeWarnings-> index(indexRow,indexColumn), arrayToolChangeWarnings[i]); queryManipulationToolChangeWarnings->submit(); i++; } } emit refreshTableToolChangeWarnings(); }
but it seems to be wrong
Yesterday i tried it like this
void detailScreen::fillDatabaseToolChangeWarnings() { queryToolChangeWarnings->prepare("UPDATE ToolChangeWarnings (COLOR, TOOLNR, TIME, POCKETSTATUS) " "VALUES (:COLOR, :TOOLNR, :TIME, :POCKETSTATUS)"); for (int i = 0; i < 10; i++) { queryToolChangeWarnings->bindValue(":TOOLNR", arrayToolChangeWarnings[i * 3 + 0]); queryToolChangeWarnings->bindValue(":TIME", arrayToolChangeWarnings[i * 3 + 1]); queryToolChangeWarnings->bindValue(":POCKETSTATUS", arrayToolChangeWarnings[i * 3 + 2]); queryToolChangeWarnings->exec(); } emit refreshTableToolChangeWarnings(); }
but it does not work too.
can you please tell me to do it corectly?
-
@Mogli123 There is no WHERE in your query, so you're updating everything. If you, for example, want to update a data record with a specific ID you can do it like this:
UPDATE ToolChangeWarnings (COLOR, TOOLNR, TIME, POCKETSTATUS) " "VALUES (:COLOR, :TOOLNR, :TIME, :POCKETSTATUS)" "WHERE ID = :ID"
-
@jsulm
to updating everything it's ok, because the old data are not necessary after updating -
@Mogli123 But then I don't understand what the problem is.
Or maybe you misunderstood what I wrote? Without WHERE you're updating all data records in the table. So, if you have 10 records in that table all 10 will be updated. -
@jsulm
Yes that is that wat I want because I don't know which data had changed therefore I want to override all data.
Or is this a bad solution?
My problem is that it does not work and I don't know why.
after writing the data at first time it works fine and the qtableview is displaying them.
If I try to update them the qtableview does nothing
Furthermore I don't know if updating the database works because the qtableview shows no new data -
This post is deleted! -
if I try something like this
queryToolChangeWarnings->prepare("UPDATE ToolChangeWarnings SET TOOLNR = :TOOLNR"); queryToolChangeWarnings->bindValue(":TOOLNR", arrayToolChangeWarnings[0]);
The updating is working but complete column TOOLNR geht the int value from the arrayToolChangeWarnings[0]
How can I only update for example the first value from the column not the complete column
and so on -
Because your query asks exactly for that. Use the
WHERE
keyword and state precisely where you want the update to happen.See the documentation which also contain a big warning about missing the
WHERE
part.[edit: added link to documentation SGaist]
-
@SGaist
Sorry your right
If I try it lik this it worksqueryToolChangeWarnings->prepare("UPDATE ToolChangeWarnings SET TOOLNR = :TOOLNR WHERE ID = 0"); queryToolChangeWarnings->bindValue(":TOOLNR", arrayToolChangeWarnings[0]); queryToolChangeWarnings->exec();
It looks like that it is very impractical to do this for all cells
Do you know a solution to do this in a more efficient way -
Then use another bind value for
ID
and pass it also to your query. You can do that in a loop. -
@Mogli123
Provided I understand you right:It looks like that it is very impractical to do this for all cells
I believe you're saying you have some array of data (from a TCP socket). You want to update the database to change the
TOOLNR
value of a bunch of rows to a variety of values in your input data,It cannot be helped that this requires a distinct SQL statement to be sent to the database for each input value (array element). If you have a 100 rows of data you will be executing 100 SQL statements. That's how it is.
At the code side, as @SGaist says you do not have to write 100 lines of code, one for each row. You use a loop to produce the statements, like:
for (int i = 0; i < arrayToolChangeWarnings.size(); i++ { queryToolChangeWarnings->prepare("UPDATE ToolChangeWarnings SET TOOLNR = :TOOLNR WHERE ID = " + QString::number(i)); queryToolChangeWarnings->bindValue(":TOOLNR", arrayToolChangeWarnings[i]); queryToolChangeWarnings->exec(); }
There may be a way of sending the multiple statements as a single batch efficiently, I don't know (but then you'd have to deal with the bindings somehow).
-
-
Great !
Then please mark the thread as solved using the "Topic Tools" button so that other forum uses may know a solution has been found :)