Results 1 to 16 of 16

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

  1. #1
    Join Date
    Jan 2011
    Location
    Richmond, VA
    Posts
    94
    Thanks
    14
    Qt products
    Qt4
    Platforms
    MacOS X Windows

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

    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)

  2. #2
    Join Date
    Aug 2009
    Location
    coimbatore,India
    Posts
    314
    Thanks
    37
    Thanked 47 Times in 43 Posts
    Qt products
    Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows

    Default Re: QSqlTableModel insert works fine, delete not so much...

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

    Bala

  3. #3
    Join Date
    Jan 2011
    Location
    Richmond, VA
    Posts
    94
    Thanks
    14
    Qt products
    Qt4
    Platforms
    MacOS X Windows

    Default Re: QSqlTableModel insert works fine, delete not so much...

    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...

    Qt Code:
    1. void MainWindow::on_AdminPB_clicked()
    2. {
    3. //*********************************************
    4. //* Retrieve data from the USER table for managing... *
    5. //*********************************************
    6. usertablemodel = new QSqlTableModel(this);
    7.  
    8. usertablemodel->setTable ("user");
    9. usertablemodel->setEditStrategy (QSqlTableModel::OnManualSubmit);
    10. usertablemodel->select ();
    11.  
    12. usertablemodel->setHeaderData (0, Qt::Horizontal,
    13. QObject::tr("Username"));
    14. usertablemodel->setHeaderData (1, Qt::Horizontal,
    15. QObject::tr("Password"));
    16. ....
    17.  
    18. ui->ManageUsersTV->setModel (usertablemodel);
    19. ui->ManageUsersTV->show ();
    20.  
    21. ui->ManageUsersTV->setEditTriggers (QAbstractItemView::AnyKeyPressed |
    22. QAbstractItemView::DoubleClicked);
    23. }
    24.  
    25. void MainWindow::on_InsertUserPB_clicked()
    26. {
    27.  
    28. int row = usertablemodel->rowCount ();
    29. usertablemodel->insertRows(row,1);
    30.  
    31. }
    32.  
    33. void MainWindow::on_DeleteUserPB_clicked()
    34. {
    35. usertablemodel->removeRows(ui->ManageUsersTV->currentIndex().row(), 1);
    36. }
    37.  
    38. void MainWindow::on_SaveUserPB_clicked()
    39. {
    40. if (!usertablemodel->submitAll ())
    41. qDebug() << usertablemodel->lastError ();
    42. }
    To copy to clipboard, switch view to plain text mode 
    Last edited by wysota; 8th June 2013 at 08:47. Reason: changed [qtclass] to [code]

  4. #4
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,734
    Thanks
    13
    Thanked 1,610 Times in 1,537 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: QSqlTableModel insert works fine, delete not so much...

    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.

  5. The following user says thank you to ChrisW67 for this useful post:

    scott_hollen (16th February 2011)

  6. #5
    Join Date
    Jan 2011
    Location
    Richmond, VA
    Posts
    94
    Thanks
    14
    Qt products
    Qt4
    Platforms
    MacOS X Windows

    Default Re: QSqlTableModel insert works fine, delete not so much...

    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

    Qt Code:
    1. void MainWindow::on_AdminPB_clicked()
    2. {
    3. //*********************************************
    4. //* Retrieve data from the USER table for managing... *
    5. //*********************************************
    6. usertablemodel = new QSqlTableModel(this);
    7.  
    8. usertablemodel->setTable ("user");
    9. usertablemodel->setEditStrategy (QSqlTableModel::OnManualSubmit);
    10. usertablemodel->select ();
    11.  
    12. usertablemodel->setHeaderData (0, Qt::Horizontal,
    13. QObject::tr("Username"));
    14. usertablemodel->setHeaderData (1, Qt::Horizontal,
    15. QObject::tr("Password"));
    16. ....
    17.  
    18. ui->ManageUsersTV->setModel (usertablemodel);
    19. ui->ManageUsersTV->show ();
    20.  
    21. ui->ManageUsersTV->setEditTriggers (QAbstractItemView::AnyKeyPressed |
    22. QAbstractItemView::DoubleClicked);
    23. }
    24.  
    25. void MainWindow::on_InsertUserPB_clicked()
    26. {
    27.  
    28. int row = usertablemodel->rowCount ();
    29. usertablemodel->insertRows(row,1);
    30.  
    31. }
    32.  
    33. void MainWindow::on_DeleteUserPB_clicked()
    34. {
    35. usertablemodel->removeRows(ui->ManageUsersTV->currentIndex().row(), 1);
    36. }
    37.  
    38. void MainWindow::on_SaveUserPB_clicked()
    39. {
    40. if (!usertablemodel->submitAll ())
    41. qDebug() << usertablemodel->lastError ();
    42. }
    To copy to clipboard, switch view to plain text mode 

  7. #6
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,734
    Thanks
    13
    Thanked 1,610 Times in 1,537 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: QSqlTableModel insert works fine, delete not so much...

    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.

  8. #7
    Join Date
    Jan 2011
    Location
    Richmond, VA
    Posts
    94
    Thanks
    14
    Qt products
    Qt4
    Platforms
    MacOS X Windows

    Default Re: QSqlTableModel insert works fine, delete not so much...

    Quote Originally Posted by ChrisW67 View Post
    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....

  9. #8
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,734
    Thanks
    13
    Thanked 1,610 Times in 1,537 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: QSqlTableModel insert works fine, delete not so much...

    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?
    Last edited by ChrisW67; 18th February 2011 at 08:28.

  10. #9
    Join Date
    Jan 2011
    Location
    Richmond, VA
    Posts
    94
    Thanks
    14
    Qt products
    Qt4
    Platforms
    MacOS X Windows

    Default Re: QSqlTableModel insert works fine, delete not so much...

    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...

    Qt Code:
    1. //*********************************************************
    2. //* First, create the table holding user information... *
    3. //*********************************************************
    4. QString sql_command = "create table user (user_id text, ";
    5. sql_command.append ("user_password text, user_full_name text, ");
    6. sql_command.append ("user_status text, user_access_level integer, ");
    7. sql_command.append ("user_lock_date date, ");
    8. sql_command.append ("user_activity_date date)");
    9.  
    10. query.exec (sql_command);
    To copy to clipboard, switch view to plain text mode 

  11. #10
    Join Date
    Dec 2009
    Posts
    47
    Thanks
    11
    Thanked 1 Time in 1 Post
    Qt products
    Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android

    Default Re: QSqlTableModel insert works fine, delete not so much...

    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_

  12. #11
    Join Date
    Jan 2011
    Location
    Richmond, VA
    Posts
    94
    Thanks
    14
    Qt products
    Qt4
    Platforms
    MacOS X Windows

    Default Re: QSqlTableModel insert works fine, delete not so much...

    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

  13. #12
    Join Date
    Mar 2011
    Posts
    1
    Thanked 1 Time in 1 Post
    Qt products
    Qt4
    Platforms
    Unix/X11

    Default Re: QSqlTableModel insert works fine, delete not so much...

    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:

    Qt Code:
    1. QSqlQuery insert(m_tableModel->database());
    2. QSqlRecord record = m_tableModel->database().record(m_tableModel->tableName());
    3. if(record.count())
    4. {
    5. QString query = "INSERT INTO " + m_tableModel->tableName() +
    6. " (\"" + record.field(0).name() + "\"";
    7.  
    8. for(int i=1; i<record.count(); ++i)
    9. query += ",\"" + record.field(i).name() + "\"";
    10.  
    11. query += ") VALUES (\"" + record.value(0).toString() + "\"";
    12.  
    13. for(int i=1; i<record.count(); ++i)
    14. query += ",\"" + record.value(i).toString() + "\"";
    15.  
    16. query += ")";
    17.  
    18. LOG4CXX_DEBUG(logger, "Insert: " + query);
    19.  
    20. if (!insert.exec(query))
    21. {LOG4CXX_DEBUG(logger, "Updating database failed!!");}
    22. else{
    23. m_tableModel->submitAll();
    24. m_tableModel->select();
    25. scrollToBottom();
    26. }
    27. }
    To copy to clipboard, switch view to plain text mode 

    hope that helps

  14. The following user says thank you to docmouse for this useful post:

    scott_hollen (3rd March 2011)

  15. #13
    Join Date
    Jan 2011
    Location
    Richmond, VA
    Posts
    94
    Thanks
    14
    Qt products
    Qt4
    Platforms
    MacOS X Windows

    Default Re: QSqlTableModel insert works fine, delete not so much...

    That might very well help -- thanks!


    scott

  16. #14
    Join Date
    Dec 2009
    Posts
    47
    Thanks
    11
    Thanked 1 Time in 1 Post
    Qt products
    Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android

    Default Re: QSqlTableModel insert works fine, delete not so much...

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

    Al_

  17. The following user says thank you to Al_ for this useful post:

    scott_hollen (4th March 2011)

  18. #15
    Join Date
    Jun 2013
    Posts
    1
    Qt products
    Qt4
    Platforms
    Unix/X11

    Default Re: QSqlTableModel insert works fine, delete not so much...

    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?

  19. #16
    Join Date
    Dec 2009
    Posts
    47
    Thanks
    11
    Thanked 1 Time in 1 Post
    Qt products
    Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android

    Default Re: QSqlTableModel insert works fine, delete not so much...

    Quote Originally Posted by pavithrap View Post
    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(){ })?

Similar Threads

  1. Replies: 1
    Last Post: 31st December 2010, 16:35
  2. How to know not insert or delete query?
    By ramazangirgin in forum Qt Programming
    Replies: 2
    Last Post: 29th June 2010, 13:43
  3. Replies: 3
    Last Post: 11th December 2009, 17:13
  4. Replies: 1
    Last Post: 30th March 2009, 22:25
  5. update() works fine sometime but not everytime..!!
    By salmanmanekia in forum Qt Programming
    Replies: 19
    Last Post: 22nd August 2008, 09:11

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.