Inserting a record in a MySQL table with foreign key



  • Hello!
    In my main window I have a table view with a QSqlRelationalTableModel which presents records to the user.
    The user can insert new records, so I create a dialog prompting data and then when "Submit" is clicked, the dialog should insert a new record in the model. The choice of the foreign key is made with a combo box, so there's no risk of incorrect value.
    When the dialog has inserted the record and closed itself, the table view should update and show the record just created. Why this doesn't happen? I have checked with a manual query in my db, and the record is there, in the table. Do I have to manually update something?
    Thank you for replies, here's the code for the insertion of the row:
    @void VehicleDialog::submit()
    {
    int id = generateId();
    QString brand = brandLineEdit->text();
    QString model = modelLineEdit->text();
    QString owner = ownerComboBox->currentText();
    QString licensePlate = licensePlateLineEdit->text();
    QDate purchaseDate = purchaseDateEdit->date();
    int purchasePrice = purchasePriceEdit->text().toInt();
    int ownerId;

    QSqlQuery ownerQuery;
    ownerQuery.prepare("select * from driver where fullname = :owner");
    ownerQuery.bindValue(":owner", owner);
    
    if (!ownerQuery.exec()) {
        QMessageBox::warning(this, tr("Error"), tr("Unable to execute query:"
                                                   "\n%1").arg(ownerQuery.
                                                               lastError().
                                                               text()),
                             QMessageBox::Close);
    }
    
    int fieldNo = ownerQuery.record().indexOf("id");
    if (ownerQuery.next()) {
        ownerId = ownerQuery.value(fieldNo).toInt();
    }
    
    qDebug() << "Slot submit called:\n" << brand <<
                "\n" << model << "\n" <<
                owner << "\n" << licensePlate;
    
    QSqlRecord record = this->model->record();
    record.setValue("id", id);
    record.setValue("brand", brand);
    record.setValue("model", model);
    record.setValue("owner", ownerId);
    record.setValue("licenseplate", licensePlate);
    record.setValue("purchasedate", purchaseDate);
    record.setValue("purchaseprice", purchasePrice);
    
    int row = this->model->rowCount();
    
    if (!this->model->insertRecord(row, record)) {
        qDebug() << "Error inserting record";
    }
    
    accept();
    

    }@



  • QSqlTableModel selects a static result set from the database. That means that the results you see are not live data, but a snapshot of the data that was generated when you called select().

    In order to sync your model with the actual table contents, call "select()":http://doc.qt.nokia.com/4.7/qsqlrelationaltablemodel.html#select



  • No, it doen't work... I call select after the dialog.exec().
    There's a bit strange behaviour, if I don't call model->submitAll() the row is shown in the view, but without foreign key, and isn't present in db... If I call model->submitAll() the record is in the db, but the view doesn't show it, even after a select from the model... I wonder why it's happening this and if there's a way to solve...



  • I solved it by using a manual insert statement with placeholders, thank you for the "selection" hint :)


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.