Editing QSqlRelationalTableModel - but not through the usual way

[vr]

Hi,

in my app. i use a qtableview with a qsqlrelationaltablemodel. User can not edit data directly in the view but via a "form" below the view. For example the "foreign" fields can be set using a ComboBox (just as if i would use the view's delegate).
The model is set to OnManualSumbit mode, so on update first i call setRecord and than submitAll(). And submitAll drops an error - incorrect integer value: "theselectedforeignname" for column groupid.
Here's two pieces of code:

[Init]

mView = new QTableView;
mRelationalModel = new SqlRelationalTableModel(this,QSqlDatabase::database());
mRelationalModel->setTable("table");
mRelationalModel->setEditStrategy(QSqlTableModel::OnManualSubmit);
mRelationalModel->setRelation(5, QSqlRelation("othertable","id","name"));
if ( ! mRelationalModel->select() )	{
        QMessageBox::information(this,"Error",mRelationalModel->lastError().text());		
	return false;
}
mView->setModel(mRelationalModel);
mView->setItemDelegate(new QSqlRelationalDelegate(mView));
mView->setSelectionBehavior(QAbstractItemView::SelectRows);
mView->setSelectionMode(QAbstractItemView::SingleSelection);
mView->setEditTriggers(QAbstractItemView::NoEditTriggers);
mView->setSortingEnabled(true);
mView->sortByColumn(0,Qt::AscendingOrder);
mView->verticalHeader()->hide();
mView->show();

mView = new QTableView;
mRelationalModel = new SqlRelationalTableModel(this,QSqlDatabase::database());
mRelationalModel->setTable("table");
mRelationalModel->setEditStrategy(QSqlTableModel::OnManualSubmit);
mRelationalModel->setRelation(5, QSqlRelation("othertable","id","name"));
if ( ! mRelationalModel->select() )	{
        QMessageBox::information(this,"Error",mRelationalModel->lastError().text());		
	return false;
}
mView->setModel(mRelationalModel);
mView->setItemDelegate(new QSqlRelationalDelegate(mView));
mView->setSelectionBehavior(QAbstractItemView::SelectRows);
mView->setSelectionMode(QAbstractItemView::SingleSelection);
mView->setEditTriggers(QAbstractItemView::NoEditTriggers);
mView->setSortingEnabled(true);
mView->sortByColumn(0,Qt::AscendingOrder);
mView->verticalHeader()->hide();
mView->show();

To copy to clipboard, switch view to plain text mode 

[The update code]

if ( ! mView->currentIndex().isValid() )	return;
int row = mView->currentIndex().row();
QSqlRecord record = mRelationalModel->record(row);
record.setValue("field1",field1Editor->text());
record.setValue("field2",field2Editor->text());
//more fields....
//and this is the foreign field
record.setValue("name",foreignfieldCombo->currentText());
 
if ( mRelationalModel->setRecord(row, record) ) {	
        //on second try this two lines were added and record.setValue("name"...was commented out
	//QModelIndex idx = mRelationalModel->index(row,5);
	//mRelationalModel->setData(idx, foreignfieldCombo->currentText(), Qt::EditRole);
	if ( ! mRelationalModel->submitAll() )	{
		QMessageBox::critical(this, "Error", mRelationalModel->lastError().text());
		mRelationalModel->revertRow(row);	
	}
}

if ( ! mView->currentIndex().isValid() )	return;
int row = mView->currentIndex().row();
QSqlRecord record = mRelationalModel->record(row);
record.setValue("field1",field1Editor->text());
record.setValue("field2",field2Editor->text());
//more fields....
//and this is the foreign field
record.setValue("name",foreignfieldCombo->currentText());

if ( mRelationalModel->setRecord(row, record) ) {	
        //on second try this two lines were added and record.setValue("name"...was commented out
	//QModelIndex idx = mRelationalModel->index(row,5);
	//mRelationalModel->setData(idx, foreignfieldCombo->currentText(), Qt::EditRole);
	if ( ! mRelationalModel->submitAll() )	{
		QMessageBox::critical(this, "Error", mRelationalModel->lastError().text());
		mRelationalModel->revertRow(row);	
	}
}

To copy to clipboard, switch view to plain text mode 

As you can see i tried to set foreign data with record.setValue and also with this two lines of code:

QModelIndex idx = mRelationalModel->index(row,5);
mRelationalModel->setData(idx, foreignfieldCombo->currentText(), Qt::EditRole);

QModelIndex idx = mRelationalModel->index(row,5);
mRelationalModel->setData(idx, foreignfieldCombo->currentText(), Qt::EditRole);

To copy to clipboard, switch view to plain text mode 

The first method did nothing and submitAll returned false
The second method can succesfully update the foreign field data in the view, but submitAll still couldn't write record back to db.

So, how should i do this correctly?
Should i "translate" the selected name into the corresponding id value myself?

Thanks:
vr

[patrik08]

How you connect to db... ? driver ?

if ( ! mRelationalModel->submitAll() )  {
        QMessageBox::critical(this, "Error", mRelationalModel->lastError().text());
        mRelationalModel->revertRow(row);   
    }

if ( ! mRelationalModel->submitAll() )  {
        QMessageBox::critical(this, "Error", mRelationalModel->lastError().text());
        mRelationalModel->revertRow(row);   
    }

To copy to clipboard, switch view to plain text mode 

error here waht is mRelationalModel->lastError().text() on save ....

if You use mysql check the model from http://www.qtcentre.org/forum/f-qt-s...dmin-6807.html

and set the now allow to edit field... run on all table wo having index...

[vr]

How you connect to db... ? driver ?



error here waht is mRelationalModel->lastError().text() on save ....

if You use mysql check the model from http://www.qtcentre.org/forum/f-qt-s...dmin-6807.html

and set the now allow to edit field... run on all table wo having index...

This is a mysql5 db, and submitAll drops the following error:

drivertext = unable to execute statement
databasetext = Incorrect integer value: 'theselectedname' for column 'groupid' at row 1
error number = 1366 which is ER_TRUNCATED_WRONG_VALUE_FOR_FIELD


db connection? the simple way

QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
db.setDatabaseName("dbname");
db.setUserName("username");
db.setPassword("pswd");
db.setPort(dbPort);
if ( db.open() )

QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
db.setDatabaseName("dbname");
db.setUserName("username");
db.setPassword("pswd");
db.setPort(dbPort);
if ( db.open() )

To copy to clipboard, switch view to plain text mode 

...etc, etc...

If i enable direct editing via the qsqlrelationaldelegate - everything works fine.

[patrik08]

This is a mysql5 db, and submitAll drops the following error:
If i enable direct editing via the qsqlrelationaldelegate - everything works fine.

O yes model the model is correct if a field is int open and save as int..... string ecc...

if you assign other format to column 'groupid' ..... sql error emit error ...


debug here ...

QModelIndex idx = mRelationalModel->index(row,5);
      mRelationalModel->setData(idx, foreignfieldCombo->currentText(), Qt::EditRole);

QModelIndex idx = mRelationalModel->index(row,5);
      mRelationalModel->setData(idx, foreignfieldCombo->currentText(), Qt::EditRole);

To copy to clipboard, switch view to plain text mode 

idx->isValid() ?

you can use

qDebug() << "### idx->isValid() -> " << idx->isValid();

to see live the result ..

append
CONFIG += console
on pro file...

post the result of

SHOW COLUMNS FROM tablename

to know column 'groupid' format....



try this model ...

http://ppk.ciz.ch/qt_c++/mysql_model...YSQL_Model.zip

unzip cd dir
qmake && make...

[vr]

hi patrik08,

i'm afraid you misunderstood me. I know this is wrong this way, but don't understand why.

If i edit the model directly via the view's relationaldelegate, everything is OK. If i set the data myself, it is not...

Simple example:
main table: int id, varchar name, int foreignid
foreign table: int id, varchar name
QSqlRelationalModel displays data in the following way (after you set a QSqlRelation(2,"id","name"))
View shows the following: id, name, foreignname.
If you edit foreignname delegate creates a combobox filled with names from foreigntable.
After you choose one of them, on save the correct foreignid will be written back to the main table - it is correct, isn't it.
Of course if you check the model's record the fileds are the following:
id, name, foreignname.

So my question is: what more does the delegate or the model than me when set the foreign data back to the model.

If i call record.setValue("foreignname","choosenname") is not enough.
If i call the two lines

//yes, the index is valid!
QModelIndex idx = mRelationalModel->index(row,5);
mRelationalModel->setData(idx, "choosenname", Qt::DisplayRole);

//yes, the index is valid!
QModelIndex idx = mRelationalModel->index(row,5);
mRelationalModel->setData(idx, "choosenname", Qt::DisplayRole);

To copy to clipboard, switch view to plain text mode 

the view is updated correctly, but data cannot be written back to db - that means foreignname is not mapped back to the correct id value.

Of course i can write a simple update statement instead, but i'm sure it must work using only the model's public functions.

vr

[vr]

OK, i solved the problem but it is not a clear solution.

I found this in qt doc:

bool QSqlRelationalTableModel::setData ( const QModelIndex & index, const QVariant & value, int role = Qt::EditRole ) [virtual]
Sets the data for the role in the item with the specified index to the value given. Depending on the edit strategy, the value might be applied to the database at once, or it may be cached in the model.
Returns true if the value could be set, or false on error (for example, if index is out of bounds).
For relational columns, value must be the index, not the display value.

So i search for the id value of the selected foreign name and call setData with this id value instead of the combo's current text and it works.

But normally neither the model, the view nor the delegate allow me to get the selected foreign columns mapped id value.
Obviously the delegate finds it itself internally and calls setData with the found index value.
I thought i don't have to do it myself