SubmitAll() fails on QSqlRelationalTableModel

Question

'Hi All,

I have a form with a tableview representing a QSqlRelationalTableModel and widgets linked to the same model via QDataWidgetMapper.

Here is the table structure:

queryString = QString("CREATE TABLE t100000_Stations ("
                      "RecordID     INTEGER PRIMARY KEY AUTOINCREMENT,"
                      "StationID    TEXT UNIQUE, "
                      "ProjectID    TEXT, "
                      "LocalName    TEXT, "
                      "StationType  TEXT REFERENCES t911000_StationTypes(StationType), " // We have to link station type to a table (maybe parameters)
                      "Easting      DOUBLE NOT NULL, "
                      "Northing     DOUBLE NOT NULL, "
                      "Latitude     DOUBLE, "
                      "Longitude    DOUBLE, "
                      "LatLonDatum  TEXT, "
                      "CoordinateSystem  TEXT, "
                      "Elevation    DOUBLE, "
                      "ElevationType  INTEGER REFERENCES t908000_ParameterTypes(ParameterID), " // Same here
                      "ElevationDatum TEXT, "
                      "SurveyMethod TEXT, "
                      "Surveyor     TEXT, "
                      "SurveyDate   DATETIME, "
                      "LandTenure   TEXT, "
                      "Region       TEXT, "
                      "MiningLease  TEXT, "
                      "Comments     TEXT,"
                      "Source       TEXT NOT NULL, "
                      "SourceDate   DATETIME NOT NULL, "
                      "EntryAuthor  TEXT NOT NULL, "
                      "EntryDate    DATETIME NOT NULL)"
                      );

The form shows the records and relations flawlessly, including combo boxes and table view delegates for the relation fields. My problem is that any edits in the table values are not passed back to the database.

After much trying, I end up changing the setEditStrategy to OnManualSubmit. I added a submitAll() call in the form destructor to update the database, but it returns false and give me the following error message:

“near “.”: syntax error Unable to execute statement”

I saw in somepage that it could be related to the primary key. I did the following command in debug mode:

QString tempString = tableModelStations->primaryKey().name();

but the string came empty, which make me believe that the problem indeed was in the primary key.

I then subclassed QSqlRelationalTableModel so that I could implement the setPrimaryKey() function and do it manually, but no luck with that either.

Could anyone shed some light on:

1. Why submitAll() isn’t working

2. How to access the statement used by submitAll()

Answer 1

1. Why submitAll() isn’t working it doesnt work only in your way, it seems not so good making submit in destructor; 2.How to access the statement used by submitAll() you dont need this;

You should try:

1. Not to show primary key in your view, not to allow to change it (this will emit db error on dublicate primary keys);
2. After changing View use SIGNAL dataChange(QModelIndex,QModelIndex) to adress your SLOT where you use submitAll;
3. On inserting row on TableModel use: yourModel->insertRow(yourModel->rowCount(QModelIndex()))

4. On inserting row on RelationalTableModel use:

   QSqlRecord record; // prepare your record, not touching primary key, it's autoinc yourRelModel->insertRecord(yourRelModel->rowCount(QModelIndex()),record);