qsqlquery UPDATE how to?

**Lesiok** · 30th August 2012, 11:50

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** · 30th August 2012, 13:10

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

Qt Code:

Switch view

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** · 30th August 2012, 15:04

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** · 30th August 2012, 15:22

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** · 30th August 2012, 17:42

Where did you get the value of rowid?

**unix7777** · 30th August 2012, 18:58

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** · 31st August 2012, 01:39

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** · 31st August 2012, 07:50

I have changed everything.In accordance with documentation here http://stackoverflow.com/questions/5...em-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"!

Qt Code:

Switch view

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

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

To copy to clipboard, switch view to plain text mode

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** · 31st August 2012, 08:19

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:

Qt Code:

Switch view

#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;
}

#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;
}

To copy to clipboard, switch view to plain text mode

**unix7777** · 31st August 2012, 08:47

Now everything works!
THANKS TO ALL OF YOU!

Qt Code:

Switch view

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

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

To copy to clipboard, switch view to plain text mode

Thread: qsqlquery UPDATE how to?

Thread Tools

Search Thread

Display

Hybrid View

Re: qsqlquery UPDATE how to?

Re: qsqlquery UPDATE how to?

Re: qsqlquery UPDATE how to?

Re: qsqlquery UPDATE how to?

Re: qsqlquery UPDATE how to?

Re: qsqlquery UPDATE how to?

Re: qsqlquery UPDATE how to?

Re: qsqlquery UPDATE how to?

Re: qsqlquery UPDATE how to?

Re: qsqlquery UPDATE how to?

Similar Threads

QFileSystemModel - Incremental update/pre-emptive update

QSqlQuery Memory issues. QSqlQuery::exec() and QSqlDatabase::open()/close();

Subclassing QPropertyAnimation update/setCurrentTime wont update currentValue

Batch update fails with QSqlQuery

Problems with QSqlQuery update

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions