Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

QSqlRelationalTableModel && removeRow() [Solved]



  • Hi all,
    in my application I can't remove a row from a QSqlRelationalTableModel.
    This is the situation:
    @
    ...
    ...
    m_applicazioniTableModel = new QSqlRelationalTableModel(this, QSqlDatabase::database("db_vs_manager"));
    m_applicazioniTableModel->setTable("vs_permessi");
    m_applicazioniTableModel->setRelation(1,QSqlRelation("vs_applicazioni","id","descrizione"));
    m_applicazioniTableModel->setEditStrategy(QSqlRelationalTableModel::OnManualSubmit);
    m_applicazioniTableModel->select();
    ...
    ...
    @

    now if I call:
    @
    m_applicazioniTableModel->removeRow(5);
    @
    I get from debug:
    @
    QSqlQuery::value: not positioned on a valid record
    @
    but my model has 53 rows .

    How can I solve this problem?



  • Is Select() work fine?



  • [quote author="kunashir" date="1310101354"]Is Select() work fine? [/quote]

    Yes, I get a populated QTableVew.





  • I was used it example - and all work fine:

    @ #include <QtGui>
    #include <QtSql>

    int main(int argc, char *argv[]) {
    QApplication app(argc, argv);
    QTextCodec *codec = QTextCodec::codecForName("CP1251");
    QTextCodec::setCodecForTr(codec);

    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("mydb1");
    db.setUserName("...");
    db.setPassword("...");
    db.open();
    
    // create and fill table
    QSqlQuery query;
    query.exec&#40;"create table employee( "
               " id int primary key, "
               " name varchar(20&#41;, "
               " department int, "
               " position int&#41;" );
    query.exec&#40;QObject::tr("insert into employee values(1, 'Ivanov A.K.', 1, 3&#41;"&#41;);
    query.exec&#40;QObject::tr("insert into employee values(2, 'Petorv I.E.', 3, 2&#41;"&#41;);
    query.exec&#40;QObject::tr("insert into employee values(3, 'Mihailov N.P.', 2, 1&#41;"&#41;);
    
    query.exec&#40;"create table department(id int, name varchar(20&#41;&#41;");
    query.exec&#40;QObject::tr("insert into department values(1, 'Administration'&#41;"&#41;);
    query.exec&#40;QObject::tr("insert into department values(2, 'Accounting'&#41;"&#41;);
    query.exec&#40;QObject::tr("insert into department values(3, 'Planning Department&#41;"&#41;);
    
    query.exec&#40;"create table position(id int, name varchar(20&#41;&#41;");
    query.exec&#40;QObject::tr("insert into position values(1, 'Operator'&#41;"&#41;);
    query.exec&#40;QObject::tr("insert into position values(2, 'Economist'&#41;"&#41;);
    query.exec&#40;QObject::tr("insert into position values(3, 'Boss'&#41;"&#41;);
    
    QSqlRelationalTableModel model;
    model.setTable("employee");
    model.setSort(1, Qt::AscendingOrder);
    
    model.setEditStrategy(QSqlTableModel::OnFieldchange);//but this other stategy
    
    // set relation
    model.setRelation(2, QSqlRelation("department", "id", "name"));
    model.setRelation(3, QSqlRelation("position", "id", "name"));
    
    model.setHeaderData(0, Qt::Horizontal, QObject::tr("Number"));
    model.setHeaderData(1, Qt::Horizontal, QObject::tr("Name"));
    model.setHeaderData(2, Qt::Horizontal, QObject::tr("department"));
    model.setHeaderData(3, Qt::Horizontal, QObject::tr("Post"));
    
    model.select();
    
    
    // create view
    QTableView view;
    view.setModel(&model);
    
    // create delegate
    view.setItemDelegate(new QSqlRelationalDelegate(&view));
    
    view.setWindowTitle(QObject::tr("Relation table"));
    view.show();
    model.removeRow(1);
    return app.exec&#40;&#41;;
    

    }@

    Can You show scheme of DB?



  • I have this situation:
    @
    CREATE TABLE vs_applicazioni (
    id int(11) NOT NULL,
    nome varchar(100) NOT NULL,
    descrizione varchar(250) NOT NULL,
    menu varchar(20) NOT NULL,
    funzione_di_avvio varchar(200) DEFAULT NULL,
    hidden tinyint(1) DEFAULT '0',
    PRIMARY KEY (id),
    KEY menu (menu),
    CONSTRAINT vs_applicazioni_ibfk_1 FOREIGN KEY (menu) REFERENCES vs_menu (nome)
    )

    CREATE TABLE vs_permessi (
    id_gruppo int(11) NOT NULL,
    id_applicazione int(11) NOT NULL,
    PRIMARY KEY (id_gruppo,id_applicazione),
    KEY id_applicazione (id_applicazione),
    CONSTRAINT vs_permessi_ibfk_1 FOREIGN KEY (id_applicazione) REFERENCES vs_applicazioni (id),
    CONSTRAINT vs_permessi_ibfk_2 FOREIGN KEY (id_gruppo) REFERENCES vs_gruppi_utenti (id)
    )
    @

    And I'm showing in a QTableView the vs_permessi table replacing id_applicazione column with vs_applicazioni.descrizione column . It show the correct data.



  • I suppose, that the complex primary key for QSqlRelationalTableModel is a problem.
    Maybe, easier to create your own model?



  • [quote author="kunashir" date="1310112233"]I suppose, that the complex primary key for QSqlRelationalTableModel is a problem.
    Maybe, easier to create your own model?[/quote]

    I also tried with this tables:
    @
    CREATE TABLE vs_applicazioni (
    id int(11) NOT NULL,
    nome varchar(100) NOT NULL,
    descrizione varchar(250) NOT NULL,
    menu varchar(20) NOT NULL,
    funzione_di_avvio varchar(200) DEFAULT NULL,
    hidden tinyint(1) DEFAULT '0',
    PRIMARY KEY (id),
    KEY menu (menu)
    )

    CREATE TABLE vs_permessi (
    id_gruppo int(11) NOT NULL,
    id_applicazione int(11) NOT NULL,
    PRIMARY KEY (id_gruppo,id_applicazione),
    KEY id_applicazione (id_applicazione)
    )
    @

    with the same result...



  • I also tried your example with my table (the versione without foregn key):
    @
    #include <QtGui>
    #include <QtSql>

    int main(int argc, char *argv[]) {
    QApplication app(argc, argv);
    QTextCodec *codec = QTextCodec::codecForName("CP1251");
    QTextCodec::setCodecForTr(codec);

    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setDatabaseName("vs_manager");
    db.setUserName("luca");
    db.setPassword("luca123");
    
    if(!db.open())
    {
        qDebug() << db.lastError().number();
        
    }
    
    QSqlRelationalTableModel model;
    model.setTable("vs_permessi");
    model.setSort(1, Qt::AscendingOrder);
    
    model.setEditStrategy(QSqlTableModel::OnFieldChange);//but this other stategy
    
    // set relation
    model.setRelation(1,QSqlRelation("vs_applicazioni","id","descrizione"));
    
    model.select();
    
    
    // create view
    QTableView view;
    view.setModel(&model);
    
    // create delegate
    view.setItemDelegate(new QSqlRelationalDelegate(&view));
    
    view.setWindowTitle(QObject::tr("Relation table"));
    view.show();
    model.removeRow(1);
    return app.exec();
    

    }

    @

    but I get the same error:
    @
    QSqlQuery::value: not positioned on a valid record
    @

    Should it be a mysql driver problem?



  • I don't understand why:
    @
    m_applicazioniTableModel->setRelation(1,QSqlRelation("vs_applicazioni","id","descrizione"));
    @

    at the same time, table vs_permessi don't have column "descrizione".
    If I rightly understand Your scheme.



  • this should mean that I want "replace" the column 1 of vs_permessi table with the column descrizione of vs_applicazioni table linking it with vs_applicazioni.id .
    The table view show the correct data.



  • Now I tried to compile the example in Windows using ODBC to connect to the MySql DB with the same result...



  • Hmm....
    Query of removed is not correct
    "DELETE FROM vs_permessi WHERE id_gruppo = ? AND id_applicazione IS NULL"
    but I'm don't know why....
    Sorry, his have also some value...



  • I found the cause.
    The table vs_permessi:
    @
    CREATE TABLE vs_permessi (
    id_gruppo int(11) NOT NULL,
    id_applicazione int(11) NOT NULL,
    PRIMARY KEY (id_gruppo,id_applicazione),
    KEY id_applicazione (id_applicazione)
    )
    @
    has the column id_applicazione in its primary key and this seems the problem.

    If I modify the table this way:
    @
    CREATE TABLE vs_permessi (
    id_gruppo int(11) NOT NULL,
    id_applicazione int(11) NOT NULL,
    PRIMARY KEY (id_gruppo)
    )
    @

    the problem disappears...

    Very thanks for you time and your help...


Log in to reply