QSqlQuery Update doesn't work

gabor53

Hi,
I use the following code to save a change made in a QStandardItemModel to a db:

            model->setData (index,dateEdit->date ().toString ("MM/dd/yyyy"));
            QVariant d(index.data(Qt::EditRole));
            qDebug() << "QVariant: " << d;
            QString modDate;
            modDate = d.toString ();
            qDebug() << "modDate: " << modDate;

            fixquery.prepare("UPDATE Items SET AdoptDate = :AdoptDate WHERE ID = :fixID");
            fixquery.bindValue (":AdoptDate", modDate);
            fixquery.bindValue (":fixID",fixID);
            fixquery.exec ();

There is no error message, and according debug the code is executed. In output:
modDate: "05/19/2015" which is correct. Any idea why it is not saved in the database? Thank you.

SGaist

Hi,

Please add error checking to your code and print the error.

gabor53

Hi @SGaist
I added

qDebug()  << "Updating date error: " << fixquery.lastError ();

after

fixquery.exec ();

It printed the following error message:
Updating date error: QSqlError("", "", "").

SGaist

You should print the text of lastError.

gabor53

@SGaist
This was the text it printed in output.

gabor53

@gabor53
When I change

fixquery.bindValue (":AdoptDate", modDate);

to

fixquery.bindValue (":AdoptDate", "11/25/2007");

it works correctly and adds the date to the db. Looks like it doesn't like QString modDate I just can't figure why.

SGaist

Print the executed query, that will give you some additional clues.

gabor53

@SGaist
Please tell me how to print it. Thank you.

mrjj

@gabor53

I think this one can do it.
QString QSqlQuery::lastQuery() const

Returns the text of the current query being used, or an empty string if there is no current query text.

and maybe
QSqlQuery QSqlQueryModel::query() const

so something like

if (model->query())
QString str=model->query()->lastQuery() ?

Disclaimer: Didn't check the actual output.

gabor53

@mrjj
I added the following after fixquery.exec():

       qDebug()  << "Updating date error: " << fixquery.lastError ();
            QString last;
            last = fixquery.lastQuery ();
            qDebug() << "Last query: " << last;
            break;

I've got the following output in Application Output:
QVariant: QVariant(QString, "11/26/2015")
modDate: "11/26/2015"
Updating date error: QSqlError("", "", "")
Last query: "UPDATE Items SET AdoptDate = :AdoptDate WHERE ID = :fixID"

This looks normal to me....

Paul Colby

Hi @gabor53,

What does fixquery.exec(); return? true or false?
What does fixquery.numRowsAffected(); return?
What type is fixID, how is it set, and what guarantee do you have that the database actually contains a row with that ID?

Cheers.

gabor53

Hi @Paul-Colby,

Last query: "UPDATE Items SET AdoptDate = :AdoptDate WHERE ID = :fixID"
Fixquery.exec() returns: Fixqueryexec is false
Rows affected: -1.

fixID is set like this:

            QDateEdit *dateEdit = qobject_cast<QDateEdit*>(editor);
            QModelIndex updateIndex(index.model ()->index(index.row (),0,index.parent ()));

            QString fixID;
            QVariant v(updateIndex.data (Qt::DisplayRole));
            fixID = v.toString ();
            qDebug() << "FixID: " << fixID;

FixID is a QString which will give the value to the ID column in the database.The ID column does exist in the db.

gabor53

I finally figured out what the problem was. In the switch statement where this query is I made a typo:
instead of

case 7:

I had

case 7:;

After deleting the ;i t works fine.

SGaist

Ok... So completely unrelated... Can't have a nicer bug :D

Glad you found out and thanks for sharing.

Since you have it working now, please mark the thread as solved using the "Topic Tools" button so that other forum users may know a solution has been found :)