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