QSqlTableModel insert works fine, delete not so much...

[scott_hollen]

Evening everyone --

I've created a simple QSqltablemodel that's fed a SQLite table holding user info (username, password, etc). I've connected an INSERT slot to a pushbutton which works fine, with new users being added to my table, and all new rows are correctly committed when I activate the SAVE slot (via pushbutton and a call to submitALL() ), but when I try to delete a row -- despite the row number being correct -- I'm getting a "QSqlError(-1, "Parameter count mismatch", "") " error...

Has anyone else encountered this? I read on a thread earlier today that I can't find now that someone encountered an issue with saving some data if the DB was created from within QT, which is what I'm doing...

Just curious - thanks!


scott (obviously, still a newbie)

[BalaQT]

hi,
i think you made some error in bindings? can you post your code containing delete?

Bala

[scott_hollen]

Sure...The exclamation point appears properly beside the row that I'm attempting to delete, and I've displayed the row number at the moment of delete, and that's fine too...I'm sure it's something simple that I'm overlooking...I was using a sample code for a StringListModel but I'm using a SqlTableModel which may be the key...

void MainWindow::on_AdminPB_clicked()
{
    //*********************************************
    //*  Retrieve data from the USER table for managing...  *
    //*********************************************
    usertablemodel = new QSqlTableModel(this);
 
    usertablemodel->setTable ("user");
    usertablemodel->setEditStrategy (QSqlTableModel::OnManualSubmit);
    usertablemodel->select ();
 
    usertablemodel->setHeaderData (0, Qt::Horizontal,
                                   QObject::tr("Username"));
    usertablemodel->setHeaderData (1, Qt::Horizontal,
                                   QObject::tr("Password"));
    ....
 
    ui->ManageUsersTV->setModel (usertablemodel);
    ui->ManageUsersTV->show ();
 
    ui->ManageUsersTV->setEditTriggers (QAbstractItemView::AnyKeyPressed |
                                        QAbstractItemView::DoubleClicked);
}
 
void MainWindow::on_InsertUserPB_clicked()
{
 
    int row = usertablemodel->rowCount ();
    usertablemodel->insertRows(row,1);
 
}
 
void MainWindow::on_DeleteUserPB_clicked()
{
    usertablemodel->removeRows(ui->ManageUsersTV->currentIndex().row(), 1);
}
 
void MainWindow::on_SaveUserPB_clicked()
{
    if (!usertablemodel->submitAll ())
       qDebug() << usertablemodel->lastError ();
}

void MainWindow::on_AdminPB_clicked()
{
    //*********************************************
    //*  Retrieve data from the USER table for managing...  *
    //*********************************************
    usertablemodel = new QSqlTableModel(this);

    usertablemodel->setTable ("user");
    usertablemodel->setEditStrategy (QSqlTableModel::OnManualSubmit);
    usertablemodel->select ();

    usertablemodel->setHeaderData (0, Qt::Horizontal,
                                   QObject::tr("Username"));
    usertablemodel->setHeaderData (1, Qt::Horizontal,
                                   QObject::tr("Password"));
    ....

    ui->ManageUsersTV->setModel (usertablemodel);
    ui->ManageUsersTV->show ();

    ui->ManageUsersTV->setEditTriggers (QAbstractItemView::AnyKeyPressed |
                                        QAbstractItemView::DoubleClicked);
}

void MainWindow::on_InsertUserPB_clicked()
{

    int row = usertablemodel->rowCount ();
    usertablemodel->insertRows(row,1);

}

void MainWindow::on_DeleteUserPB_clicked()
{
    usertablemodel->removeRows(ui->ManageUsersTV->currentIndex().row(), 1);
}

void MainWindow::on_SaveUserPB_clicked()
{
    if (!usertablemodel->submitAll ())
       qDebug() << usertablemodel->lastError ();
}

To copy to clipboard, switch view to plain text mode 

[ChrisW67]

Please use [code] tags around code snippets. Edit your post to add them if you can.

This is normal behaviour for deleted rows in the QTableView when it is using a QSqlTableModel and the changes are not submitted immediately. If you have only the one view on this model (i.e. you don't need the deleted row to disappear from view elsewhere) then you can just setRowHidden() in the view at the time you "delete" it. If you have other views that can see the "deleted" row then you really have no choice but to submitAll() (and deal with the loss of the selection) or somehow filter on the undocumented "!" value in the vertical header.

[scott_hollen]

Thanks for the reply -- I was trying to figure out the tags but the only one I saw was the "Qt" one and that do exactly what it was I wanted...I see the # sign now...

Thanks for suggestion...I don't need to use this data outside of this model and I'll try to hide it, but what's concerning me is that the row isn't actually being removed from the database...I thought the submitALL() would take care of that...

Obviously I'm still coming up to speed in a stupidly short amount of time...I might try to use a different approach all together tonight -- it just occurred to me that I need to timestamp a field before it gets written out to the DB...Either that or I find a way to add that into the model when a row is either created or modified...

'Preciate the guidance!

scott

Reposted with tags

void MainWindow::on_AdminPB_clicked()
{
//*********************************************
//* Retrieve data from the USER table for managing... *
//*********************************************
usertablemodel = new QSqlTableModel(this);
 
usertablemodel->setTable ("user");
usertablemodel->setEditStrategy (QSqlTableModel::OnManualSubmit);
usertablemodel->select ();
 
usertablemodel->setHeaderData (0, Qt::Horizontal,
QObject::tr("Username"));
usertablemodel->setHeaderData (1, Qt::Horizontal,
QObject::tr("Password"));
....
 
ui->ManageUsersTV->setModel (usertablemodel);
ui->ManageUsersTV->show ();
 
ui->ManageUsersTV->setEditTriggers (QAbstractItemView::AnyKeyPressed |
QAbstractItemView::DoubleClicked);
}
 
void MainWindow::on_InsertUserPB_clicked()
{
 
int row = usertablemodel->rowCount ();
usertablemodel->insertRows(row,1);
 
}
 
void MainWindow::on_DeleteUserPB_clicked()
{
usertablemodel->removeRows(ui->ManageUsersTV->currentIndex().row(), 1);
}
 
void MainWindow::on_SaveUserPB_clicked()
{
if (!usertablemodel->submitAll ())
qDebug() << usertablemodel->lastError ();
}

void MainWindow::on_AdminPB_clicked()
{
//*********************************************
//* Retrieve data from the USER table for managing... *
//*********************************************
usertablemodel = new QSqlTableModel(this);

usertablemodel->setTable ("user");
usertablemodel->setEditStrategy (QSqlTableModel::OnManualSubmit);
usertablemodel->select ();

usertablemodel->setHeaderData (0, Qt::Horizontal,
QObject::tr("Username"));
usertablemodel->setHeaderData (1, Qt::Horizontal,
QObject::tr("Password"));
....

ui->ManageUsersTV->setModel (usertablemodel);
ui->ManageUsersTV->show ();

ui->ManageUsersTV->setEditTriggers (QAbstractItemView::AnyKeyPressed |
QAbstractItemView::DoubleClicked);
}

void MainWindow::on_InsertUserPB_clicked()
{

int row = usertablemodel->rowCount ();
usertablemodel->insertRows(row,1);

}

void MainWindow::on_DeleteUserPB_clicked()
{
usertablemodel->removeRows(ui->ManageUsersTV->currentIndex().row(), 1);
}

void MainWindow::on_SaveUserPB_clicked()
{
if (!usertablemodel->submitAll ())
qDebug() << usertablemodel->lastError ();
}

To copy to clipboard, switch view to plain text mode 

[ChrisW67]

If you have used removeRows() to remove the row then it is either committed to the underlying database immediately (OnFieldChange, OnRowChange) or only when you issue submitAll() against the model (OnManualSubmit). Until it is committed to the database the "deleted" row is still visible in the model (not the database), and any view connected to the model, but its row header is set to "!". In your case, any changes to the model content including updates and adds are not committed to the database until the user presses the SaveUserPB and submitAll() is called.

[scott_hollen]

If you have used removeRows() to remove the row then it is either committed to the underlying database immediately (OnFieldChange, OnRowChange) or only when you issue submitAll() against the model (OnManualSubmit). Until it is committed to the database the "deleted" row is still visible in the model (not the database), and any view connected to the model, but its row header is set to "!". In your case, any changes to the model content including updates and adds are not committed to the database until the user presses the SaveUserPB and submitAll() is called.

I understand that the submitALL is what's required to commit the data to the database -- the problem is that I'm getting the SQL error when the submitALL is executed, so NO data is getting saved on a delete...Updates and Inserts are committing fine -- Deletes aren't...I'm not so much worried about whether or not the deleted row is visable in the model, I can deal with that, I just can't figure out why the submitALL is generating the error and my data isn't being deleted.

What's *really* frustrating is for some reason it worked once tonight, never before and never again...I've tried clicking on the row after I hit my delete button, tabbing, all sorts of combinations, but when I hit the SAVE button which calls submitALL, I get the error and the row remains in the DB...

The error message itself isn't telling me much which also is frustrating...


scott

Oh, and I am doing OnManualSubmit which I know requires the submitALL, which is know is firing off as expected and generating the error (tracked it, which is how I know the index is correct, at least previous to the submitALL)..

Errrr....

[ChrisW67]

Does your table have a primary key?
Are there constraints on the table that might violated by delete operations?
Are there triggers on the table?

[scott_hollen]

Nope, nope, and nope Very vanilla...My first thought was since the inserts and updates were getting committed fine that maybe my index was getting lost on the delete but that doesn't appear to be the case...I'm going to do some experimenting later where I execute an actual delete statement via query.exec to see if maybe it's something outside of the model...I can't imagine permissions but after 22 years of getting paid to program, I wouldn't be surprised by anything...

//*********************************************************
//*  First, create the table holding user information...  *
//*********************************************************
QString sql_command = "create table user (user_id text, ";
sql_command.append ("user_password text, user_full_name text, ");
sql_command.append ("user_status text, user_access_level integer, ");
sql_command.append ("user_lock_date date, ");
sql_command.append ("user_activity_date date)");
 
query.exec (sql_command);

//*********************************************************
//*  First, create the table holding user information...  *
//*********************************************************
QString sql_command = "create table user (user_id text, ";
sql_command.append ("user_password text, user_full_name text, ");
sql_command.append ("user_status text, user_access_level integer, ");
sql_command.append ("user_lock_date date, ");
sql_command.append ("user_activity_date date)");

query.exec (sql_command);

To copy to clipboard, switch view to plain text mode 

[Al_]

Hi Scott

Did you find a solution? I am interested since I have a similar issue but with with insertRows(). See my reply to http://www.qtcentre.org/threads/1608...to-update-quot

Al_

[scott_hollen]

Hey Al --

No, I didn't -- call me lazy but we realized that we didn't actually *want* the user removing anything, so I quit trying to figure it out...Sorry!


scott

[docmouse]

I had the same problem with removing rows. The reason was that there were fields in the records, which hadn't been set properly. In my case the problem appeared after i added new fields to an existing and already filled database. The problem also appeared when i used 'insertRows' and didn't set each field value explicitly. My solution for the insertion problem was not to use insertRows, but to create a QSqlQuery and execute and "INSERT INTO ... " statement in which i set each field value, like i do in this snipplet:

QSqlQuery insert(m_tableModel->database());
    QSqlRecord record = m_tableModel->database().record(m_tableModel->tableName());
    if(record.count())
    {
        QString query = "INSERT INTO " + m_tableModel->tableName() +
                        " (\"" + record.field(0).name() + "\"";
 
        for(int i=1; i<record.count(); ++i)
            query += ",\"" + record.field(i).name() + "\"";
 
        query += ") VALUES (\"" + record.value(0).toString() + "\"";
 
        for(int i=1; i<record.count(); ++i)
            query += ",\"" + record.value(i).toString() + "\"";
 
        query += ")";
 
        LOG4CXX_DEBUG(logger, "Insert: " + query);
 
        if (!insert.exec(query))
            {LOG4CXX_DEBUG(logger, "Updating database failed!!");}
        else{
            m_tableModel->submitAll();
            m_tableModel->select();
            scrollToBottom();
        }
    }

QSqlQuery insert(m_tableModel->database());
    QSqlRecord record = m_tableModel->database().record(m_tableModel->tableName());
    if(record.count())
    {
        QString query = "INSERT INTO " + m_tableModel->tableName() +
                        " (\"" + record.field(0).name() + "\"";

        for(int i=1; i<record.count(); ++i)
            query += ",\"" + record.field(i).name() + "\"";

        query += ") VALUES (\"" + record.value(0).toString() + "\"";

        for(int i=1; i<record.count(); ++i)
            query += ",\"" + record.value(i).toString() + "\"";

        query += ")";

        LOG4CXX_DEBUG(logger, "Insert: " + query);

        if (!insert.exec(query))
            {LOG4CXX_DEBUG(logger, "Updating database failed!!");}
        else{
            m_tableModel->submitAll();
            m_tableModel->select();
            scrollToBottom();
        }
    }

To copy to clipboard, switch view to plain text mode 

hope that helps

[scott_hollen]

That might very well help -- thanks!


scott

[Al_]

You may also want to have a look at that thread: http://www.qtcentre.org/threads/1608...-quot?p=179842

Al_

[pavithrap]

The row does not get deleted when removerow is called after setdata.
If setdata is not called delete row works perfect.
I am using qsqltablemodel here.
Can you please help me with this problem?

[Al_]

The row does not get deleted when removerow is called after setdata.
If setdata is not called delete row works perfect.
I am using qsqltablemodel here.
Can you please help me with this problem?

I am still interested in this topic, thus also in your post. One of my issues was that deleted rows were still visible until commited; I solved this by filtering rows out that have the exclamaition sign as deleted-but-not-yet-commited flag.
Please provide more detail. When does your application commit data (manually, rowchange, fieldchange)? What do you mean by 'Row does not get deleted'? Is that row flagged as having been deleted (by the exclamation sign in the row header)? Do you have an example program (optimally fully working mian(){ })?