qsqlquery UPDATE how to?

[unix7777]

I read all the documentation of QSqlQuery and all topics in the net.Unfortunately there are examples only with INSERT or SELECT statement.
I have the code below working:


QSqlQuery query;
query.prepare("INSERT INTO clients (ClientName, ClientCity, ClientAddress, ClientMol, ClientEik, ClientVat, ClientTel, ClientMail)"
"VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
query.addBindValue(ui->lineEdit_name->text());
query.addBindValue(ui->lineEdit_city->text());
query.addBindValue(ui->lineEdit_address->text());
query.addBindValue(ui->lineEdit_mol->text());
query.addBindValue(ui->lineEdit_eik->text());
query.addBindValue(ui->lineEdit_vat->text());
query.addBindValue(ui->lineEdit_tel->text());
query.addBindValue(ui->lineEdit_mail->text());
query.exec();

What i need is the same but with UPDATE and as condition WHERE ROWID=rowid
I don't know where to put this WHERE and where the value of rowid.I have tried a lot.

[Seishin]

Just use
query.prepare("UPDATE ..."); with
query.bindValue();
Or use
QSqlTableModel::SetData();
This is written in "C++ GUI Programming with Qt 4".

[unix7777]

I tried with this:


QSqlQuery query;
query.prepare("UPDATE SET clients ClientName=:name, ClientCity=:city, ClientAddress=:address, ClientMol=:mol, ClientEik=:eik, ClientVat=:vat, ClientTel=:tel, ClientMail=:mail WHERE ROWID=:rowid");
query.bindValue(":name", ui->lineEdit_name->text());
query.bindValue(":city", ui->lineEdit_city->text());
query.bindValue(":address", ui->lineEdit_address->text());
query.bindValue(":mol", ui->lineEdit_mol->text());
query.bindValue(":eik", ui->lineEdit_eik->text());
query.bindValue(":vat", ui->lineEdit_vat->text());
query.bindValue(":tel", ui->lineEdit_tel->text());
query.bindValue(":mail", ui->lineEdit_mail->text());
query.bindValue(":rowid", QString::number(rowid));
query.exec();

Still doesn't work.query.lastError() returns Parameter count mismatch

[Seishin]

The grammar looks right. Maybe you should try to add your database to the query declaration so the query knows which is the default database?


QSqlQuery query(db);

[mardi]

Hi Unix777,

I make a sample using postgresql, hope this can help you


QString user = ui->lineEdit_user->text();
QString pass = ui->lineEdit_pass->text();


if (!db.isOpen())
db.open();

QSqlQuery query(QString("UPDATE mnp_userlist SET (userlist_pass) = (\'%0\') WHERE userlist_user = \'%1\';").arg(pass).arg(user), db);
db.close();


Regards,
Mardi

[ChrisW67]

As Mardi's example shows,


query.prepare("UPDATE SET clients ClientName=:name, ClientCity=:city, ClientAddress=:address, ClientMol=:mol, ClientEik=:eik, ClientVat=:vat, ClientTel=:tel, ClientMail=:mail WHERE ROWID=:rowid");

is not a valid SQL statement, it should read :


UPDATE clients SET ...


Do NOT build SQL by pasting strings as Mardi suggests. Using bindValue() is, by far, the better and safer option.

[unix7777]

At first thank you to both of you!

I've changed the code to:

QSqlQuery query;
query.prepare("UPDATE clients SET ClientName=:name, ClientCity=:city, ClientAddress=:address, ClientMol=:mol, ClientEik=:eik, ClientVat=:vat, ClientTel=:tel, ClientMail=:mail WHERE ROWID=:rowid");
query.bindValue(":name", ui->lineEdit_name->text());
query.bindValue(":city", ui->lineEdit_city->text());
query.bindValue(":address", ui->lineEdit_address->text());
query.bindValue(":mol", ui->lineEdit_mol->text());
query.bindValue(":eik", ui->lineEdit_eik->text());
query.bindValue(":vat", ui->lineEdit_vat->text());
query.bindValue(":tel", ui->lineEdit_tel->text());
query.bindValue(":mail", ui->lineEdit_mail->text());
query.bindValue(":rowid", QString::number(rowid));
query.exec();

But still nothing happens.The db is declared in main function and this is not the problem, because INSERT function works on same way!
Another suggestions?

[Lesiok]

Because the glass ball is now free :
1. Why are binding to :rowid text value not number ? What is a type of rowid column in database ?
2. What is a result of query.exec() (true or false).
3. If false what is a result of query.lastError().

[unix7777]

With this code i got "Success!" but nothing happens again!
In accordance with sqlite documentation ROWID should be a int.

QSqlQuery query;
query.prepare("UPDATE clients SET ClientName=:name, ClientCity=:city, ClientAddress=:address, ClientMol=:mol, ClientEik=:eik, ClientVat=:vat, ClientTel=:tel, ClientMail=:mail WHERE ROWID=:rowid");
query.bindValue(":name", ui->lineEdit_name->text());
query.bindValue(":city", ui->lineEdit_city->text());
query.bindValue(":address", ui->lineEdit_address->text());
query.bindValue(":mol", ui->lineEdit_mol->text());
query.bindValue(":eik", ui->lineEdit_eik->text());
query.bindValue(":vat", ui->lineEdit_vat->text());
query.bindValue(":tel", ui->lineEdit_tel->text());
query.bindValue(":mail", ui->lineEdit_mail->text());
query.bindValue(":rowid", rowid);
if (query.exec())
{
emit updateTable();
QMessageBox::information(this,"", "Success!");
}
else
{
QMessageBox::information(this,"", query.lastError().text());

}
this->close();

[Lesiok]

It just means that the SQL statement is correct formally. What is the value of the variable rowid ? There has to be a record in the table with the corresponding rowid ?

[unix7777]

The SQLite editor i use SQLiteStudio doesn't show ROWID. In the program row id is an int.But in another parts i use QString to insert it in the query.

[Lesiok]

Where did you get the value of rowid?

[unix7777]

there is a member function that set it.And i pass it after creating the object.The rowid int works i test it with QMessage, it display exactly what i select.

[ChrisW67]

If "nothing is happening" then either;

the rowid is does not correspond to any row in the table, or
the row already contained the data you were updating it to and therefore has no net change.


ROWID is an internal row identifier provided by Sqlite. It is a 64-bit integer allocated by Sqlite (not you). The only reasonable source for a rowid is from an existing row in the same Sqlite table.

[unix7777]

I have changed everything.In accordance with documentation here http://stackoverflow.com/questions/508627/auto-increment-in-sqlite-problem-with-python
i add id int and i give NULL for every record.On this way it auto-increment every next record.What happens - everything work except the UPDATE statement.
It gives me: " Parameter count mismatch"!


QString row=QString::number(rowid);
QSqlQuery query;
query.prepare("UPDATE clients SET (ClientName, ClientCity, ClientAddress, ClientMol, ClientEik, ClientVat, ClientTel, ClientMail) WHERE id="+row+
"VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
query.addBindValue(ui->lineEdit_name->text());
query.addBindValue(ui->lineEdit_city->text());
query.addBindValue(ui->lineEdit_address->text());
query.addBindValue(ui->lineEdit_mol->text());
query.addBindValue(ui->lineEdit_eik->text());
query.addBindValue(ui->lineEdit_vat->text());
query.addBindValue(ui->lineEdit_tel->text());
query.addBindValue(ui->lineEdit_mail->text());

this->close();
if (query.exec())
{
emit updateTable();
}
else
{
QMessageBox::information(this,"", query.lastError().text());
}

this->close();

I would like to test it with some exact number of row, for example i want to update row 1 only to see whether it works.Unfortunately i don't know the syntax to do it.

[ChrisW67]

You have had the correct syntax for at least the past few posts. I have no idea why you are still thrashing around with this.

Here is a canned example that updates the columns of a row:


#include <QtCore>
#include <QtSql>
#include <QDebug>

int main(int argc, char *argv[])
{
QCoreApplication app(argc, argv);

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName("test.db");
if (db.open()) {
QSqlQuery query;

// Create a test table
query.exec(
"create table clients ("
"id int, "
"ClientName VARCHAR(10),"
"ClientCity VARCHAR(10),"
"ClientAddress VARCHAR(10),"
"ClientMol VARCHAR(10),"
"ClientEik VARCHAR(10),"
"ClientVat VARCHAR(10),"
"ClientTel VARCHAR(10),"
"ClientMail VARCHAR(10) )" );
query.exec("insert into clients values(1, 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H')" );
query.exec("insert into clients values(2, 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H')" );

// Your Code
query.prepare("UPDATE clients SET "
"ClientName=:name, ClientCity=:city, ClientAddress=:address, ClientMol=:mol, "
"ClientEik=:eik, ClientVat=:vat, ClientTel=:tel, ClientMail=:mail WHERE id=:id");
query.bindValue(":name", "Z");
query.bindValue(":city", "Z") ;
query.bindValue(":address", "Z");
query.bindValue(":mol", "Z");
query.bindValue(":eik", "Z");
query.bindValue(":vat", "Z");
query.bindValue(":tel", "Z");
query.bindValue(":mail", "Z");
query.bindValue(":id", 1);
if (query.exec())
qDebug() << "Done OK";
else
qDebug() << "Huh!";

// Dump the table id and ClientName
query.exec("SELECT * FROM clients");
while (query.next()) {
qDebug() << query.value(0).toInt() << query.value(1).toString();
}
}


return 0;
}

[unix7777]

Now everything works!
THANKS TO ALL OF YOU!

QSqlQuery query;
query.prepare("UPDATE clients SET ClientName=:name, ClientCity=:city, ClientAddress=:address, ClientMol=:mol, ClientEik=:eik, ClientVat=:vat, ClientTel=:tel, ClientMail=:mail WHERE id=:id ");
query.bindValue(":name", ui->lineEdit_name->text());
query.bindValue(":city", ui->lineEdit_city->text());
query.bindValue(":address", ui->lineEdit_address->text());
query.bindValue(":mol", ui->lineEdit_mol->text());
query.bindValue(":eik", ui->lineEdit_eik->text());
query.bindValue(":vat", ui->lineEdit_vat->text());
query.bindValue(":tel", ui->lineEdit_tel->text());
query.bindValue(":mail", ui->lineEdit_mail->text());
query.bindValue(":id", clientid);
QMessageBox::information(this,"", clientid);
this->close();
if (query.exec())
{
emit updateTable();
}
else
{
QMessageBox::information(this,"", query.lastError().text());
}

this->close();