Problem with QSqlQuery and duplicate entry

[Pit]

Hi guys:

I'm having a problem with QSqlQuery. I'm trying to insert a record to a database, like this:




QSqlDriver *driver = QSqlDatabase::database().driver();
Q_CHECK_PTR(driver);

QSqlRecord record = driver->record(tableName);
/* Here I fill the record with values */

QString smt = driver->sqlStatement(QSqlDriver::InsertStatement, tableName, record, false);

QSqlQuery query(smt);
if (!query.exec())
{
QMessageBox::critical(this, qApp->applicationName(), tr("Error inserting into database. %1").arg(query.lastError().text()));
return;
}



Ok, the problem is that I get always a duplicate entry error: "Duplicate entry '47552' for key 'id' QMYSQL: Unable the execute query", but I'm sure that value doesn't exist. In fact, the record is inserted when query.exec() is executed (but I get the error anyways). The number differs everytime in +1 ('cause I'm getting it with a max(id)+1 query).

The primary key isn't autoincremental.

Any ideas?

Thanks!!

[spirit]

It's an interesting way to insert values into a table.
Did you try to use bindings (http://qt-project.org/doc/qt-4.8/qsqlquery.html#approaches-to-binding-values) instead?

[Rhayader]

Though your query is highly unconventional the problem arises because the query is executed twice.
Once in the constructor and once with the exec().
This is how to execute just once:

QSqlDriver *driver = QSqlDatabase::database().driver();
Q_CHECK_PTR(driver);

QSqlRecord record = driver->record(tableName);
/* Here I fill the record with values */

QString smt = driver->sqlStatement(QSqlDriver::InsertStatement, tableName, record, false);

QSqlQuery query;
if (!query.exec(smt))
{
QMessageBox::critical(this, qApp->applicationName(), tr("Error inserting into database. %1").arg(query.lastError().text()));
return;
}
or

QSqlQuery query(smt);
if (!query.isValid()) .......

[Pit]

spirit: I want to make it this way to don't have to worry if I change the type of the database :) (it doesn't happen very often, but...)

Rhayader: that's it, thanks! I used this method for long time ago and never have any problem until now, that's why I didn't realize about the constructor...

Thank you guys :)

[spirit]

spirit: I want to make it this way to don't have to worry if I change the type of the database :) (it doesn't happen very often, but...)


Don't get how it can impact when a database is changed?

[Pit]

Don't get how it can impact when a database is changed?

If database changes maybe the INSERT statement changes with it. Like this I don't have to worry about that, I just fill the record and get the insert statement asociated to that record.

Well, is just a way, I'm not saying is the best :)