larry104
23rd July 2007, 16:55
Hi,
I have a problem using a QDataWidgetMapper together with a QSqlRelationalTableModel.
The db-table products has the following fields: product, vendor_id, description, baseprice, basecurrency_id, price. vendor_id and basecurrency_id are id's to different tables in the db. I created a QDataWidgetMapper adding various lineEdits and ComboBoxes into it. The comboxes are for the vendor_id and basecurrency_id which show the real value instead of just the index (standard releational db stuff). Having a table view showing all products allows me now to edit the data nicely. The problem starts when I add a new record into the database. I preset basecurrency_id using
productModel->setData(productModel->index(row, basecurrency_id), "USD");. When I now edit the other fields, but don't touch the basecurrency_id combobox and try to submit the record I get an sql error saying something like incorrect integer value "USD". So, it seems that 'USD' gets written into the db instead of the index (=1). When I manually set it to 'USD' using the combobox everything works fine.
My question is - how do I set a value in the model which is tied to a relation? Or is there something else I'm doing wrong.
Thanks.
DB:
CREATE TABLE currencies (
id SMALLINT UNSIGNED AUTO_INCREMENT,
currency char(3) NOT NULL,
symbol varchar(1) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO currencies VALUES (0, "USD", "$");
INSERT INTO currencies VALUES (0, "EUR", "E");
INSERT INTO currencies VALUES (0, "CAD", "$");
#
# products:
#
CREATE TABLE products (
id INT UNSIGNED AUTO_INCREMENT,
product varchar(64) NOT NULL,
vendor_id SMALLINT UNSIGNED NOT NULL,
description text,
baseprice DECIMAL(15,2) DEFAULT 0.00,
basecurrency_id SMALLINT UNSIGNED NOT NULL,
price DECIMAL(15,2) DEFAULT 0.00,
PRIMARY KEY (id)
);
Code to set QSqlRelationalTableModel and mapper
// Initialize model
productModel = new QSqlRelationalTableModel(productTableView);
productModel->setTable("products");
productModel->setSort(2, Qt::AscendingOrder);
productModel->setEditStrategy(QSqlTableModel::OnFieldChange);
// Remeber the indexes of the columns
vendor_id = productModel->fieldIndex("vendor_id");
basecurrency_id = productModel->fieldIndex("basecurrency_id");
// Set the relations to the other database tables
productModel->setRelation(vendor_id, QSqlRelation("vendors", "id", "company"));
productModel->setRelation(basecurrency_id, QSqlRelation("currencies", "id", "currency"));
// set table header
productModel->setHeaderData(productModel->fieldIndex("product"), Qt::Horizontal, tr("Name"));
productModel->setHeaderData(vendor_id, Qt::Horizontal, tr("Vendor"));
productModel->setHeaderData(productModel->fieldIndex("baseprice"), Qt::Horizontal, tr("Base Price"));
productModel->setHeaderData(basecurrency_id, Qt::Horizontal, tr("Base Currency"));
productModel->setHeaderData(productModel->fieldIndex("price"), Qt::Horizontal, tr("Price"));
// Populate the model
if(!productModel->select()) {
showError(productModel->lastError());
return;
}
// Initialize table view
productTableView->setModel(productModel);
productTableView->resizeColumnsToContents();
productTableView->setSelectionMode(QAbstractItemView::SingleSelectio n);
// hide fields in table
// productTableView->setColumnHidden(productModel->fieldIndex("id"), true);
productTableView->setColumnHidden(productModel->fieldIndex("description"), true);
// set delegate
// productTableView->setItemDelegate(new QSqlRelationalDelegate(productTableView));
// Initialize the combo boxes
productBaseCurrencyComboBox->setModel(productModel->relationModel(basecurrency_id));
productBaseCurrencyComboBox->setModelColumn(productModel->relationModel(basecurrency_id)->fieldIndex("currency"));
// productBaseCurrencyComboBox->setCurrentIndex(0);
productVendorComboBox->setModel(productModel->relationModel(vendor_id));
productVendorComboBox->setModelColumn(productModel->relationModel(vendor_id)->fieldIndex("company"));
// productVendorComboBox->setCurrentIndex(0);
// Initialize widget mapper
mapper = new QDataWidgetMapper(this);
mapper->setModel(productModel);
mapper->setItemDelegate(new QSqlRelationalDelegate(productTableView));
mapper->addMapping(productNameLineEdit, productModel->fieldIndex("product"));
mapper->addMapping(productVendorComboBox, vendor_id);
mapper->addMapping(productDescriptionTextEdit, productModel->fieldIndex("description"));
mapper->addMapping(productBasepriceLineEdit, productModel->fieldIndex("baseprice"));
mapper->addMapping(productBaseCurrencyComboBox, basecurrency_id);
mapper->addMapping(productPriceLineEdit, productModel->fieldIndex("price"));
connect(productTableView->selectionModel(), SIGNAL(currentRowChanged(QModelIndex,QModelIndex)) ,
mapper, SLOT(setCurrentModelIndex(QModelIndex)));
mapper->setSubmitPolicy(QDataWidgetMapper::AutoSubmit);
mapper->toPrevious();
productTableView->setCurrentIndex(productModel->index(0, 0));
Code to insert a new row into the model
productModel->insertRows(row, 1);
productModel->setData(productModel->index(row, productModel->fieldIndex("product")), "");
productModel->setData(productModel->index(row, productModel->fieldIndex("description")), "");
productModel->setData(productModel->index(row, productModel->fieldIndex("baseprice")), "0.00");
productModel->setData(productModel->index(row, basecurrency_id), "USD");
productModel->setData(productModel->index(row, productModel->fieldIndex("price")), "0.00");
productTableView->setCurrentIndex(productModel->index(row, 0));
I have a problem using a QDataWidgetMapper together with a QSqlRelationalTableModel.
The db-table products has the following fields: product, vendor_id, description, baseprice, basecurrency_id, price. vendor_id and basecurrency_id are id's to different tables in the db. I created a QDataWidgetMapper adding various lineEdits and ComboBoxes into it. The comboxes are for the vendor_id and basecurrency_id which show the real value instead of just the index (standard releational db stuff). Having a table view showing all products allows me now to edit the data nicely. The problem starts when I add a new record into the database. I preset basecurrency_id using
productModel->setData(productModel->index(row, basecurrency_id), "USD");. When I now edit the other fields, but don't touch the basecurrency_id combobox and try to submit the record I get an sql error saying something like incorrect integer value "USD". So, it seems that 'USD' gets written into the db instead of the index (=1). When I manually set it to 'USD' using the combobox everything works fine.
My question is - how do I set a value in the model which is tied to a relation? Or is there something else I'm doing wrong.
Thanks.
DB:
CREATE TABLE currencies (
id SMALLINT UNSIGNED AUTO_INCREMENT,
currency char(3) NOT NULL,
symbol varchar(1) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO currencies VALUES (0, "USD", "$");
INSERT INTO currencies VALUES (0, "EUR", "E");
INSERT INTO currencies VALUES (0, "CAD", "$");
#
# products:
#
CREATE TABLE products (
id INT UNSIGNED AUTO_INCREMENT,
product varchar(64) NOT NULL,
vendor_id SMALLINT UNSIGNED NOT NULL,
description text,
baseprice DECIMAL(15,2) DEFAULT 0.00,
basecurrency_id SMALLINT UNSIGNED NOT NULL,
price DECIMAL(15,2) DEFAULT 0.00,
PRIMARY KEY (id)
);
Code to set QSqlRelationalTableModel and mapper
// Initialize model
productModel = new QSqlRelationalTableModel(productTableView);
productModel->setTable("products");
productModel->setSort(2, Qt::AscendingOrder);
productModel->setEditStrategy(QSqlTableModel::OnFieldChange);
// Remeber the indexes of the columns
vendor_id = productModel->fieldIndex("vendor_id");
basecurrency_id = productModel->fieldIndex("basecurrency_id");
// Set the relations to the other database tables
productModel->setRelation(vendor_id, QSqlRelation("vendors", "id", "company"));
productModel->setRelation(basecurrency_id, QSqlRelation("currencies", "id", "currency"));
// set table header
productModel->setHeaderData(productModel->fieldIndex("product"), Qt::Horizontal, tr("Name"));
productModel->setHeaderData(vendor_id, Qt::Horizontal, tr("Vendor"));
productModel->setHeaderData(productModel->fieldIndex("baseprice"), Qt::Horizontal, tr("Base Price"));
productModel->setHeaderData(basecurrency_id, Qt::Horizontal, tr("Base Currency"));
productModel->setHeaderData(productModel->fieldIndex("price"), Qt::Horizontal, tr("Price"));
// Populate the model
if(!productModel->select()) {
showError(productModel->lastError());
return;
}
// Initialize table view
productTableView->setModel(productModel);
productTableView->resizeColumnsToContents();
productTableView->setSelectionMode(QAbstractItemView::SingleSelectio n);
// hide fields in table
// productTableView->setColumnHidden(productModel->fieldIndex("id"), true);
productTableView->setColumnHidden(productModel->fieldIndex("description"), true);
// set delegate
// productTableView->setItemDelegate(new QSqlRelationalDelegate(productTableView));
// Initialize the combo boxes
productBaseCurrencyComboBox->setModel(productModel->relationModel(basecurrency_id));
productBaseCurrencyComboBox->setModelColumn(productModel->relationModel(basecurrency_id)->fieldIndex("currency"));
// productBaseCurrencyComboBox->setCurrentIndex(0);
productVendorComboBox->setModel(productModel->relationModel(vendor_id));
productVendorComboBox->setModelColumn(productModel->relationModel(vendor_id)->fieldIndex("company"));
// productVendorComboBox->setCurrentIndex(0);
// Initialize widget mapper
mapper = new QDataWidgetMapper(this);
mapper->setModel(productModel);
mapper->setItemDelegate(new QSqlRelationalDelegate(productTableView));
mapper->addMapping(productNameLineEdit, productModel->fieldIndex("product"));
mapper->addMapping(productVendorComboBox, vendor_id);
mapper->addMapping(productDescriptionTextEdit, productModel->fieldIndex("description"));
mapper->addMapping(productBasepriceLineEdit, productModel->fieldIndex("baseprice"));
mapper->addMapping(productBaseCurrencyComboBox, basecurrency_id);
mapper->addMapping(productPriceLineEdit, productModel->fieldIndex("price"));
connect(productTableView->selectionModel(), SIGNAL(currentRowChanged(QModelIndex,QModelIndex)) ,
mapper, SLOT(setCurrentModelIndex(QModelIndex)));
mapper->setSubmitPolicy(QDataWidgetMapper::AutoSubmit);
mapper->toPrevious();
productTableView->setCurrentIndex(productModel->index(0, 0));
Code to insert a new row into the model
productModel->insertRows(row, 1);
productModel->setData(productModel->index(row, productModel->fieldIndex("product")), "");
productModel->setData(productModel->index(row, productModel->fieldIndex("description")), "");
productModel->setData(productModel->index(row, productModel->fieldIndex("baseprice")), "0.00");
productModel->setData(productModel->index(row, basecurrency_id), "USD");
productModel->setData(productModel->index(row, productModel->fieldIndex("price")), "0.00");
productTableView->setCurrentIndex(productModel->index(row, 0));