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
 

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