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();

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();

To copy to clipboard, switch view to plain text mode 

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 ...");

query.prepare("UPDATE ...");

To copy to clipboard, switch view to plain text mode 

with

query.bindValue();

query.bindValue();

To copy to clipboard, switch view to plain text mode 

Or use

QSqlTableModel::SetData();

QSqlTableModel::SetData();

To copy to clipboard, switch view to plain text mode 

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();

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();

To copy to clipboard, switch view to plain text mode 

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);

QSqlQuery query(db);

To copy to clipboard, switch view to plain text mode 

[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();

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();

To copy to clipboard, switch view to plain text mode 

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");

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

To copy to clipboard, switch view to plain text mode 

is not a valid SQL statement, it should read :

UPDATE clients SET ...

UPDATE clients SET ...

To copy to clipboard, switch view to plain text mode 

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();

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();

To copy to clipboard, switch view to plain text mode 

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();

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();

To copy to clipboard, switch view to plain text mode 

[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.