One to one db form
-
wrote on 6 Feb 2017, 13:11 last edited by
I 'm developing a db application.
I have two tables, devices and clients.
Every device (from table devices) matches with one client (from table clients).
I populate textbox like this:model = new QSqlRelationalTableModel(this); model->setTable("devices"); model->setEditStrategy(QSqlTableModel::OnManualSubmit); model->select(); mapper = new QDataWidgetMapper(this); mapper->setModel(model); mapper->setItemDelegate(new QSqlRelationalDelegate(this)); mapper->addMapping(ui.brand_txt, model->fieldIndex("brand")); mapper->addMapping(ui.model_txt, model->fieldIndex("model"));
How do we populate the two text boxes of the client? (Firt name and Last name)
How do we insert a new record?(to populate a combo box from devices(not seen in picture) I use model->setRelation(typeIndex, QSqlRelation("types", "id", "name"));)
-
Hi,
Aren't you missing
mapper->toFirst();
in your code ? -
wrote on 6 Feb 2017, 15:43 last edited by Panoss 2 Jun 2017, 15:46
Hi SGaist.
I include it in the 'real' code.I tried this to get firstname and lastname from clients:
model->setRelation(clientFirstnameIndex, QSqlRelation("clients", "id", "firstname")); model->setRelation(clientLastnameIndex, QSqlRelation("clients", "id", "lastname"));
But it's returning the last name, I suppose we can't have two relation on one table? (clients)
-
wrote on 6 Feb 2017, 16:07 last edited by Panoss 2 Jun 2017, 16:24
I tried this:
('client_id' is the foreign key of 'clients' in table 'dvices')clientIndex = model->fieldIndex("client_id"); mapper->addMapping(ui.client_id_cbo, clientIndex); model->setRelation(clientIndex, QSqlRelation("clients", "id", "firstname, lastname"));
But the combobox (client_id_cbo) doesn't update when I move to another record even though it's mapped:
QSqlTableModel *relClientModel = model->relationModel(clientIndex); ui.client_id_cbo->setModel(relClientModel); ui.client_id_cbo->setModelColumn(relClientModel->fieldIndex("lastname")); mapper->addMapping(ui.client_id_cbo, clientIndex);
-
wrote on 6 Feb 2017, 16:38 last edited by Panoss 2 Jun 2017, 17:04
I removed firstname from QSqlRelation("clients", "id", "firstname, lastname"):
model->setRelation(clientIndex, QSqlRelation("clients", "id", "lastname"));
The combo works fine.
But how do I populate the firstname textbox?
WithQSqlRelation("clients", "id", "firstname, lastname")
doesn't seem to work.
Obviously, QSqlRelation returns only one field. -
I 'm developing a db application.
I have two tables, devices and clients.
Every device (from table devices) matches with one client (from table clients).
I populate textbox like this:model = new QSqlRelationalTableModel(this); model->setTable("devices"); model->setEditStrategy(QSqlTableModel::OnManualSubmit); model->select(); mapper = new QDataWidgetMapper(this); mapper->setModel(model); mapper->setItemDelegate(new QSqlRelationalDelegate(this)); mapper->addMapping(ui.brand_txt, model->fieldIndex("brand")); mapper->addMapping(ui.model_txt, model->fieldIndex("model"));
How do we populate the two text boxes of the client? (Firt name and Last name)
How do we insert a new record?(to populate a combo box from devices(not seen in picture) I use model->setRelation(typeIndex, QSqlRelation("types", "id", "name"));)
wrote on 6 Feb 2017, 17:28 last edited by Panoss 2 Jun 2017, 18:12@Panoss said in One to one db form:
How do we insert a new record?
(Answering to myself :) )
To insert a record into a database table, we call insertRow() to create a new empty row (record), and we use setData() to set the values of each column (field):QSqlTableModel model; model->setTable("cd"); int row = 0; model->insertRows(row, 1); model->setData(model->index(row, 0), 113); model->setData(model->index(row, 1), "Shanghai My Heart"); model->setData(model->index(row, 2), 224); model->setData(model->index(row, 3), 2003); model->submitAll();
I replaced QSqlTableModel with QSqlRelationalTableModel, but it doesn't work.
Doesn't db get updated through the QSqlRelationalTableModel? -
I removed firstname from QSqlRelation("clients", "id", "firstname, lastname"):
model->setRelation(clientIndex, QSqlRelation("clients", "id", "lastname"));
The combo works fine.
But how do I populate the firstname textbox?
WithQSqlRelation("clients", "id", "firstname, lastname")
doesn't seem to work.
Obviously, QSqlRelation returns only one field.@Panoss said in One to one db form:
I removed firstname from QSqlRelation("clients", "id", "firstname, lastname"):
model->setRelation(clientIndex, QSqlRelation("clients", "id", "lastname"));
The combo works fine.
But how do I populate the firstname textbox?
WithQSqlRelation("clients", "id", "firstname, lastname")
doesn't seem to work.
Obviously, QSqlRelation returns only one field.Indeed, a QSqlRelation is to map one column. It's not a query.
-
wrote on 6 Feb 2017, 21:04 last edited by Panoss 2 Jun 2017, 21:06
When I insert a record in the model, does a record get inserted in database too?
Is this the 'Qt way' of inserting a row in db? -
Once you call submitAll() it should yes.
-
wrote on 7 Feb 2017, 18:35 last edited by Panoss 2 Jul 2017, 21:10
Yes it works. The problem was that I was giving Null value for a field that could not take Null.
I have another problem, can't save value from combo box:
model->setData(model->index(row, typeIndex), ui.type_cbo->currentIndex());
Inserts an empty value in the database for the field 'type'.
I tried and with UserRole:
model->setData(model->index(row, typeIndex), ui.type_cbo->currentIndex(), Qt::UserRole);
The same result.
EDIT: I tried EditRole:
model->setData(model->index(row, typeIndex), ui.type_cbo->currentIndex(), Qt::EditRole);
And I get the CurrentIndex.
I need the bound field value, the id.This is how I populate the combo's list:
QSqlTableModel *relTypeModel = model->relationModel(typeIndex); ui.type_cbo->setModel(relTypeModel); ui.type_cbo->setModelColumn(relTypeModel->fieldIndex("name"));
The db table is table types, with fields: id, name.
I want when I save the record, the value from combo to be the id (coming from the db table) and not the currentIndex.This is the setData from QSqlRelationalTableModel class:
bool QSqlRelationalTableModel::setData(const QModelIndex &index, const QVariant &value, int role) { Q_D(QSqlRelationalTableModel); if ( role == Qt::EditRole && index.column() > 0 && index.column() < d->relations.count() && d->relations.value(index.column()).isValid()) { QRelation &relation = d->relations[index.column()]; if (!relation.isDictionaryInitialized()) relation.populateDictionary(); if (!relation.dictionary.contains(value.toString())) return false; } return QSqlTableModel::setData(index, value, role); }
So, it has to be Qt::EditRole, right?
-
Yes it works. The problem was that I was giving Null value for a field that could not take Null.
I have another problem, can't save value from combo box:
model->setData(model->index(row, typeIndex), ui.type_cbo->currentIndex());
Inserts an empty value in the database for the field 'type'.
I tried and with UserRole:
model->setData(model->index(row, typeIndex), ui.type_cbo->currentIndex(), Qt::UserRole);
The same result.
EDIT: I tried EditRole:
model->setData(model->index(row, typeIndex), ui.type_cbo->currentIndex(), Qt::EditRole);
And I get the CurrentIndex.
I need the bound field value, the id.This is how I populate the combo's list:
QSqlTableModel *relTypeModel = model->relationModel(typeIndex); ui.type_cbo->setModel(relTypeModel); ui.type_cbo->setModelColumn(relTypeModel->fieldIndex("name"));
The db table is table types, with fields: id, name.
I want when I save the record, the value from combo to be the id (coming from the db table) and not the currentIndex.This is the setData from QSqlRelationalTableModel class:
bool QSqlRelationalTableModel::setData(const QModelIndex &index, const QVariant &value, int role) { Q_D(QSqlRelationalTableModel); if ( role == Qt::EditRole && index.column() > 0 && index.column() < d->relations.count() && d->relations.value(index.column()).isValid()) { QRelation &relation = d->relations[index.column()]; if (!relation.isDictionaryInitialized()) relation.populateDictionary(); if (!relation.dictionary.contains(value.toString())) return false; } return QSqlTableModel::setData(index, value, role); }
So, it has to be Qt::EditRole, right?
@Panoss Are you aware that ui.type_cbo->currentIndex() returns the index of current item - a number not the text of the current item? Do you want to store index or the text value (it is unclear from your description)?
-
wrote on 8 Feb 2017, 07:21 last edited by Panoss 2 Aug 2017, 07:49
Sorry for confusing you guys but I am confused myself.
No, I don't want to store the index of the current item of the combo.
Nor the combo 's text.
But the boundcolumn 's value (this is an MS Access termin, just trying to make it clear if someone has experience with ms Access).Let me explain:
I want the combo 's list to take two fields, columns(from table clients).
First column, the bound column, to be the field named 'id'.
The second column, to be the field named 'name'.From the combo 's list I select an item. I want the value of the id (the bound column) to be stored in the db.
I read from here:
"For the combo box, we pass an extra argument to tell the widget mapper which property to relate to values from the model. As a result, the user is able to select an item from the combo box, and the corresponding value stored in the widget's currentIndex property will be stored in the model."So, with:
mapper->addMapping(typeComboBox, 2, "currentIndex");
...I 'll be storing the currentIndex of the combo box.
But I don't want the current index to be stored, but the 'Bound column' value.
-
wrote on 8 Feb 2017, 10:24 last edited by Panoss 2 Aug 2017, 10:34
1/13