Cannot delete an item in MySQL from a model

[pcheng]

I have a qtableview which shows the values of a QSqlModel. Adding an item works fine. When I try to delete an item I get the error "QSqlQuery::value: not positioned on a valid record".

The itemModel is instantiated as follows:

// Setup the Order Items View
    itemModel = new QSqlRelationalTableModel(this);
    itemModel->setTable("orderitem");
    itemModel->setRelation(2, QSqlRelation("products", "ProductID", "ProductName"));
    ui->itemsTableView->setModel(itemModel);
    ui->itemsTableView->setItemDelegate(new QSqlRelationalDelegate(itemModel));
    ui->itemsTableView->setSelectionMode(QAbstractItemView::SingleSelection);
    ui->itemsTableView->setSelectionBehavior(QAbstractItemView::SelectRows);
    ui->itemsTableView->setColumnHidden(1, true);
    ui->itemsTableView->setAlternatingRowColors(true);

// Setup the Order Items View
    itemModel = new QSqlRelationalTableModel(this);
    itemModel->setTable("orderitem");
    itemModel->setRelation(2, QSqlRelation("products", "ProductID", "ProductName"));
    ui->itemsTableView->setModel(itemModel);
    ui->itemsTableView->setItemDelegate(new QSqlRelationalDelegate(itemModel));
    ui->itemsTableView->setSelectionMode(QAbstractItemView::SingleSelection);
    ui->itemsTableView->setSelectionBehavior(QAbstractItemView::SelectRows);
    ui->itemsTableView->setColumnHidden(1, true);
    ui->itemsTableView->setAlternatingRowColors(true);

To copy to clipboard, switch view to plain text mode 

The delete button code is as follows:

void addOrder::on_deleteItemPushButton_clicked()
{
    // Delete the selected item
    int index = ui->itemsTableView->currentIndex().row();
    if (!itemModel->removeRow(index))
        qDebug() << itemModel->lastError();
    ui->savePushButton->setEnabled(true);
}

void addOrder::on_deleteItemPushButton_clicked()
{
    // Delete the selected item
    int index = ui->itemsTableView->currentIndex().row();
    if (!itemModel->removeRow(index))
        qDebug() << itemModel->lastError();
    ui->savePushButton->setEnabled(true);
}

To copy to clipboard, switch view to plain text mode 

Any ideas on why the delete does not work?

I am thinking that maybe the problem lies in the fact that the table in MySQL is actually a combined primary key rather than a single key. It takes the ProductID and the OrderID as a combo primary key. I am considering this problem because on another class which has a single key parameter the same code works fine. Is it possible to send two values to delete a row?

Thanks,

Pericles

[kito]

itemModel in the first function is not the same as the one in the second function, so the second itemModel has no data.

[ChrisW67]

What is the value of index in the slot? If there is no current index then this will be -1, an invalid row number. The current index and the selection are not the same thing.

[pcheng]

Kito. The itemModel is the same one since it was declared in the header file as a private. In the add function it works fine so I think that is not the problem. I tried printing an item from the itemModel and it prints out correctly.

Chris. Index returns the correct index of 1 if I select the second item in the qtableview.

void addOrder::on_deleteItemPushButton_clicked()
{
    // Delete the selected item
    int index = ui->itemsTableView->currentIndex().row();
    //QModelIndex index2 = ui->itemsTableView->currentIndex();
    qDebug() << "Value of Item at 0,1" << itemModel->data(itemModel->index(0,1), Qt::DisplayRole).toString();
    qDebug() << "Value of index" << index;
    qDebug() << "Primary Key" << itemModel->primaryKey();
 
    if (!itemModel->removeRow(index))
        qDebug() << itemModel->lastError();
    ui->savePushButton->setEnabled(true);
}

void addOrder::on_deleteItemPushButton_clicked()
{
    // Delete the selected item
    int index = ui->itemsTableView->currentIndex().row();
    //QModelIndex index2 = ui->itemsTableView->currentIndex();
    qDebug() << "Value of Item at 0,1" << itemModel->data(itemModel->index(0,1), Qt::DisplayRole).toString();
    qDebug() << "Value of index" << index;
    qDebug() << "Primary Key" << itemModel->primaryKey();

    if (!itemModel->removeRow(index))
        qDebug() << itemModel->lastError();
    ui->savePushButton->setEnabled(true);
}

To copy to clipboard, switch view to plain text mode 

Value of Item at 0,1 "5" 
Value of index 1 
Primary Key QSqlRecord( 2 ) 
 " 0:" QSqlField("Orders_OrderID", int, length: 11, precision: 0, required: yes, generated: yes, typeID: 3) "" 
 " 1:" QSqlField("Products_ProductID", int, length: 11, precision: 0, required: yes, generated: yes, typeID: 3) "" 
QSqlQuery::value: not positioned on a valid record

Value of Item at 0,1 "5" 
Value of index 1 
Primary Key QSqlRecord( 2 ) 
 " 0:" QSqlField("Orders_OrderID", int, length: 11, precision: 0, required: yes, generated: yes, typeID: 3) "" 
 " 1:" QSqlField("Products_ProductID", int, length: 11, precision: 0, required: yes, generated: yes, typeID: 3) "" 
QSqlQuery::value: not positioned on a valid record

To copy to clipboard, switch view to plain text mode 

I have seen some other posts that have a problem with composite keys. Any ideas?

---

Added after 1 29 minutes:

I think I have pinpointed my problem.

itemModel->setRelation(2, QSqlRelation("products", "ProductID", "ProductName"));

itemModel->setRelation(2, QSqlRelation("products", "ProductID", "ProductName"));

To copy to clipboard, switch view to plain text mode 

I am setting the relationship of the product id with the product name so in the itemTableView I have the names of the products and not the ids. If I remove this line the delete happens correctly. Now to find out how to put the names and then use the name to get the ID to use in the delete function.

Is there a way to get the actual ProductID instead of the ProductName which substitutes it in the qtableview?

Thanks,

Pericles

[kito]

What happens when you use QSqlQueryModel instead?

[pcheng]

I didn't use QSqlQueryModel but what I did was I created a new primary key and removed the composite primary key from my table. Now the whole thing works fine. The only problem will arise if the user enters the same product in the same order which should not be allowed. Its a small sacrifice that I will need to live with.

Thanks for your help.