One to one db form

Panoss

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"));)
alt text

SGaist

Hi,

Aren't you missing mapper->toFirst(); in your code ?

Panoss

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)

Panoss

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

Panoss

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?
With

QSqlRelation("clients", "id", "firstname, lastname")

doesn't seem to work.
Obviously, QSqlRelation returns only one field.

Panoss

@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?

SGaist

@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?
With
QSqlRelation("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.

Panoss

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?

SGaist

Once you call submitAll() it should yes.

Panoss

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?

jsulm

@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)?

Panoss

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.

Panoss

Here you can see: table devices, table clients and the combo in the form:
alt text

In the form, when I select 'Daniel" from the list (which has an id=4), I want number 4 to be assigned to devices.client_id.
(devices.client_id to be stored with value equal to 4)