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
Qt Code: 
  1. productModel->setData(productModel->index(row, basecurrency_id), "USD");
To copy to clipboard, switch view to plain text mode 
. 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:
Qt Code: 
  1. CREATE TABLE currencies (
  2. id            SMALLINT UNSIGNED AUTO_INCREMENT,
  3. currency      char(3) NOT NULL,
  4. symbol        varchar(1) NOT NULL,
  5. PRIMARY KEY (id)
  6. );
  7.  
  8. INSERT INTO currencies VALUES (0, "USD", "$");
  9. INSERT INTO currencies VALUES (0, "EUR", "E");
  10. INSERT INTO currencies VALUES (0, "CAD", "$");
  11.  
  12. #
  13. # products:
  14. #
  15. CREATE TABLE products (
  16. id                 INT UNSIGNED AUTO_INCREMENT,
  17. product            varchar(64) NOT NULL,
  18. vendor_id          SMALLINT UNSIGNED NOT NULL,
  19. description        text,
  20. baseprice          DECIMAL(15,2) DEFAULT 0.00,
  21. basecurrency_id    SMALLINT UNSIGNED NOT NULL,
  22. price              DECIMAL(15,2) DEFAULT 0.00,
  23. PRIMARY KEY (id)
  24. );
To copy to clipboard, switch view to plain text mode 

Code to set QSqlRelationalTableModel and mapper
Qt Code: 
  1. // Initialize model
  2. 	productModel = new QSqlRelationalTableModel(productTableView);
  3. 	productModel->setTable("products");
  4. 	productModel->setSort(2, Qt::AscendingOrder);
  5. 	productModel->setEditStrategy(QSqlTableModel::OnFieldChange);
  6.  
  7.  
  8.     // Remeber the indexes of the columns
  9.     vendor_id = productModel->fieldIndex("vendor_id");
  10.     basecurrency_id = productModel->fieldIndex("basecurrency_id");
  11.  
  12.     // Set the relations to the other database tables
  13.     productModel->setRelation(vendor_id, QSqlRelation("vendors", "id", "company"));
  14.     productModel->setRelation(basecurrency_id, QSqlRelation("currencies", "id", "currency"));
  15.  
  16. 	// set table header 
  17. 	productModel->setHeaderData(productModel->fieldIndex("product"), Qt::Horizontal, tr("Name"));
  18. 	productModel->setHeaderData(vendor_id, Qt::Horizontal, tr("Vendor"));
  19. 	productModel->setHeaderData(productModel->fieldIndex("baseprice"), Qt::Horizontal, tr("Base Price"));
  20. 	productModel->setHeaderData(basecurrency_id, Qt::Horizontal, tr("Base Currency"));
  21. 	productModel->setHeaderData(productModel->fieldIndex("price"), Qt::Horizontal, tr("Price"));
  22.  
  23.    // Populate the model
  24.     if(!productModel->select()) {
  25.         showError(productModel->lastError());
  26.         return;
  27.     }
  28.  
  29. 	// Initialize table view
  30. 	productTableView->setModel(productModel);
  31. 	productTableView->resizeColumnsToContents();
  32.    	productTableView->setSelectionMode(QAbstractItemView::SingleSelection);
  33.  
  34. 	// hide fields in table
  35. 	// productTableView->setColumnHidden(productModel->fieldIndex("id"), true);
  36. 	productTableView->setColumnHidden(productModel->fieldIndex("description"), true);
  37.  
  38. 	// set delegate
  39. 	// productTableView->setItemDelegate(new QSqlRelationalDelegate(productTableView)); 	
  40.  
  41. 	// Initialize the combo boxes
  42.     productBaseCurrencyComboBox->setModel(productModel->relationModel(basecurrency_id));
  43.     productBaseCurrencyComboBox->setModelColumn(productModel->relationModel(basecurrency_id)->fieldIndex("currency"));
  44. //	productBaseCurrencyComboBox->setCurrentIndex(0);
  45.  
  46.     productVendorComboBox->setModel(productModel->relationModel(vendor_id));
  47.     productVendorComboBox->setModelColumn(productModel->relationModel(vendor_id)->fieldIndex("company"));
  48. //	productVendorComboBox->setCurrentIndex(0);
  49.  
  50. 	// Initialize widget mapper
  51.    	mapper = new QDataWidgetMapper(this);
  52.     mapper->setModel(productModel);
  53.     mapper->setItemDelegate(new QSqlRelationalDelegate(productTableView));
  54.     mapper->addMapping(productNameLineEdit, productModel->fieldIndex("product"));
  55.     mapper->addMapping(productVendorComboBox, vendor_id);
  56.     mapper->addMapping(productDescriptionTextEdit, productModel->fieldIndex("description"));
  57.     mapper->addMapping(productBasepriceLineEdit, productModel->fieldIndex("baseprice"));
  58.  	mapper->addMapping(productBaseCurrencyComboBox, basecurrency_id);
  59.     mapper->addMapping(productPriceLineEdit, productModel->fieldIndex("price"));
  60.  
  61.     connect(productTableView->selectionModel(), SIGNAL(currentRowChanged(QModelIndex,QModelIndex)),
  62.             mapper, SLOT(setCurrentModelIndex(QModelIndex)));
  63.  
  64. 	mapper->setSubmitPolicy(QDataWidgetMapper::AutoSubmit);
  65. 	mapper->toPrevious(); 
  66. 	productTableView->setCurrentIndex(productModel->index(0, 0));
To copy to clipboard, switch view to plain text mode 


Code to insert a new row into the model
Qt Code: 
  1. productModel->insertRows(row, 1);
  2. 		productModel->setData(productModel->index(row, productModel->fieldIndex("product")), "");		
  3. 		productModel->setData(productModel->index(row, productModel->fieldIndex("description")), "");		
  4. 		productModel->setData(productModel->index(row, productModel->fieldIndex("baseprice")), "0.00");		
  5. 		productModel->setData(productModel->index(row, basecurrency_id), "USD");		
  6. 		productModel->setData(productModel->index(row, productModel->fieldIndex("price")), "0.00");
  7.  
  8. 		productTableView->setCurrentIndex(productModel->index(row, 0));
To copy to clipboard, switch view to plain text mode