Results 1 to 4 of 4

Thread: Database issue with adding record using QSqlRelationalTable and QDataWidgetMapper

  1. #1
    Join Date
    Aug 2008
    Posts
    38
    Thanks
    15
    Qt products
    Qt4
    Platforms
    Windows

    Default Database issue with adding record using QSqlRelationalTable and QDataWidgetMapper

    Post 1 of 2 due to length... Question in part 2 of 2...
    Hello,
    I am using Qt Creator 1.2.1, with Qt 4.5.2 on Windows Vista x32. I have a MySQL database created with MySQL Workbench v5.2.16.

    I have been trying to get a cpp class completed to access 1 of the tables in the db and have run into an issue with the add record algorithm. Basically, I can successfully navigate the records in the table, but on adding a record, if I navigate away the model submits a copy of the record I nav'd to. For example:

    tEmployees:
    simple table with 2 related fields for department and status
    Qt Code:
    1. CREATE TABLE IF NOT EXISTS `timeDB`.`tEmployees` (
    2. `idtEmployees` INT NOT NULL AUTO_INCREMENT ,
    3. `empNameCode` VARCHAR(45) NOT NULL ,
    4. `empFirstName` VARCHAR(50) NULL ,
    5. `empLastName` VARCHAR(80) NULL ,
    6. `tDepartment_idtDepartment` INT NOT NULL ,
    7. `tStatus_idtStatus` INT NOT NULL ,
    8. `empStartDate` DATETIME NOT NULL ,
    9. PRIMARY KEY (`idtEmployees`) ,
    10. INDEX `fk_tEmployees_tDepartment` (`tDepartment_idtDepartment` ASC) ,
    11. INDEX `fk_tEmployees_tStatus` (`tStatus_idtStatus` ASC) ,
    12. CONSTRAINT `fk_tEmployees_tDepartment`
    13. FOREIGN KEY (`tDepartment_idtDepartment` )
    14. REFERENCES `timeDB`.`tDepartment` (`idtDepartment` )
    15. ON DELETE NO ACTION
    16. ON UPDATE CASCADE,
    17. CONSTRAINT `fk_tEmployees_tStatus`
    18. FOREIGN KEY (`tStatus_idtStatus` )
    19. REFERENCES `timeDB`.`tStatus` (`idtStatus` )
    20. ON DELETE NO ACTION
    21. ON UPDATE CASCADE)
    22. ENGINE = InnoDB
    To copy to clipboard, switch view to plain text mode 

    tDepartment:
    (contains fk links for dept id in employee table)
    Qt Code:
    1. CREATE TABLE IF NOT EXISTS `timeDB`.`tDepartment` (
    2. `idtDepartment` INT NOT NULL AUTO_INCREMENT ,
    3. `DepartmentCode` VARCHAR(10) NULL ,
    4. `DepartmentName` VARCHAR(45) NULL ,
    5. `DepartmentSupervisor` VARCHAR(45) NULL ,
    6. `tStatus_idtStatus` INT NOT NULL ,
    7. PRIMARY KEY (`idtDepartment`) ,
    8. INDEX `fk_tDepartment_tStatus` (`tStatus_idtStatus` ASC) ,
    9. CONSTRAINT `fk_tDepartment_tStatus`
    10. FOREIGN KEY (`tStatus_idtStatus` )
    11. REFERENCES `timeDB`.`tStatus` (`idtStatus` )
    12. ON DELETE NO ACTION
    13. ON UPDATE CASCADE)
    14. ENGINE = InnoDB
    To copy to clipboard, switch view to plain text mode 

    tStatus:
    (contains fk for statusid (i.e. active/inactive))
    Qt Code:
    1. CREATE TABLE IF NOT EXISTS `timeDB`.`tStatus` (
    2. `idtStatus` INT NOT NULL AUTO_INCREMENT ,
    3. `StatusDescription` VARCHAR(45) NOT NULL ,
    4. PRIMARY KEY (`idtStatus`) )
    5. ENGINE = InnoDB
    To copy to clipboard, switch view to plain text mode 

    tEmployee - Data:
    Qt Code:
    1. 1 QQ QQ QQ 1 1 2010-05-05 00:00:00
    2. 2 qq qw ww 1 1 2010-05-05 00:00:00
    3. 17 test 2 1 2010-08-03 00:00:00
    4. 18 asdgd asd asd 1 1 2010-07-30 00:00:00
    5. 19 QQ QQ QQ 1 1 2010-05-05 00:00:00
    6. 20 QQ QQ QQ 1 1 2010-05-05 00:00:00
    To copy to clipboard, switch view to plain text mode 
    tDepartment - Data:
    Qt Code:
    1. 1 t t1 T 1
    2. 2 R R2 R 1
    To copy to clipboard, switch view to plain text mode 

    tStatus - Data:
    Qt Code:
    1. 1 Active
    2. 2 InActive
    To copy to clipboard, switch view to plain text mode 

    ok, so now when I add an employee to the table, I basically implement the following:

  2. #2
    Join Date
    Aug 2008
    Posts
    38
    Thanks
    15
    Qt products
    Qt4
    Platforms
    Windows

    Default Re: Database issue with adding record using QSqlRelationalTable and QDataWidgetMapper

    employeeForm.cpp
    Qt Code:
    1. #include "employeeform.h"
    2.  
    3. employeeForm::employeeForm(int id, QWidget *parent)
    4. :QDialog(parent)
    5. {
    6. leNameCode = new QLineEdit;
    7. lblNameCode = new QLabel(tr("Alias"));
    8. lblNameCode->setBuddy(leNameCode);
    9. QHBoxLayout *hblNameCode = new QHBoxLayout();
    10. hblNameCode->addWidget(lblNameCode);
    11. hblNameCode->addWidget(leNameCode);
    12.  
    13. cbDept = new QComboBox;
    14. lblDept = new QLabel(tr("Department"));
    15. lblDept->setBuddy(cbDept);
    16. QHBoxLayout *hblDept = new QHBoxLayout();
    17. hblDept->addWidget(lblDept);
    18. hblDept->addWidget(cbDept);
    19.  
    20. leFName = new QLineEdit;
    21. lblFName = new QLabel(tr("First Name:"));
    22. lblFName->setBuddy(leFName);
    23. QHBoxLayout *hblName = new QHBoxLayout();
    24. hblName->addWidget(lblFName);
    25. hblName->addWidget(leFName);
    26. hblName->addSpacing(20);
    27.  
    28. leLName = new QLineEdit;
    29. lblLName = new QLabel(tr("Last Name:"));
    30. lblLName->setBuddy(leLName);
    31. hblName->addWidget(lblLName);
    32. hblName->addWidget(leLName);
    33.  
    34. cbStatus = new QComboBox;
    35. lblStatus = new QLabel(tr("Employment Status:"));
    36. lblStatus->setBuddy(cbStatus);
    37. QHBoxLayout *hblStatus = new QHBoxLayout();
    38. hblStatus->addWidget(lblStatus);
    39. hblStatus->addWidget(cbStatus);
    40.  
    41. deStartDate = new QDateEdit;
    42. deStartDate->setCalendarPopup(true);
    43. QDate today = QDate::currentDate();
    44. deStartDate->setDateRange(today.addDays(-90),today.addDays(90));
    45. deStartDate->setDate(today);
    46. lblStartDate = new QLabel(tr("Start Date:"));
    47. lblStartDate->setBuddy(deStartDate);
    48. QHBoxLayout *hblStartDate = new QHBoxLayout();
    49. hblStartDate->addWidget(lblStartDate);
    50. hblStartDate->addWidget(deStartDate);
    51.  
    52. bClose = new QPushButton("&Close");
    53.  
    54. //finally lets set up the model
    55. tableModel = new QSqlRelationalTableModel(this);
    56. tableModel->setTable("tEmployees");
    57. tableModel->setRelation(tEmployee_DepartmentId, QSqlRelation("tDepartment","idtDepartment","DepartmentName"));
    58. tableModel->setRelation(tEmployee_StatusId,QSqlRelation("tStatus","idtStatus","StatusDescription"));
    59. //tableModel->setSort(tEmployee_NameCode,Qt::AscendingOrder);
    60. tableModel->select();
    61.  
    62. //uses the relation set earlier when setRelation() was called.
    63. //because of this, we cannot just use the table enums to get the model row, they may be different
    64.  
    65. QSqlTableModel *relationModel = tableModel->relationModel(tEmployee_DepartmentId);
    66. cbDept->setModel(relationModel);
    67. cbDept->setModelColumn(relationModel->fieldIndex("DepartmentName"));//cbDept->setModelColumn(tDepartment_Name);
    68. QSqlTableModel *relationModel_status = tableModel->relationModel(tEmployee_StatusId);
    69. cbStatus->setModel(relationModel_status);
    70. cbStatus->setModelColumn(relationModel_status->fieldIndex("StatusDescription"));//cbStatus->setModelColumn(tStatus_Desc);
    71.  
    72. //now we can map the records from the database into the form...
    73. mapper = new QDataWidgetMapper(this);
    74. mapper->setSubmitPolicy(QDataWidgetMapper::ManualSubmit);
    75. mapper->setModel(tableModel);
    76. mapper->setItemDelegate(new QSqlRelationalDelegate(this));
    77. mapper->addMapping(leNameCode, tEmployee_NameCode);
    78. mapper->addMapping(leFName,tEmployee_FName);
    79. mapper->addMapping(leLName,tEmployee_LName);
    80. mapper->addMapping(cbDept, tEmployee_DepartmentId);
    81. mapper->addMapping(deStartDate,tEmployee_StartDate);
    82. mapper->addMapping(cbStatus,tEmployee_StatusId);
    83.  
    84. //finally now if the form was called with a valid id, then populated with that Ids records...
    85. //otherwise just use the first record ...
    86. if(id != -1)
    87. {
    88. for(int row = 0; row < tableModel->rowCount(); ++row)
    89. {
    90. QSqlRecord record = tableModel->record(row);
    91. if(record.value(tEmployee_Id).toInt() == id)
    92. {
    93. mapper->setCurrentIndex(row);
    94. break;
    95. }
    96. }
    97. }
    98. else
    99. mapper->toFirst();
    100.  
    101. nav = new dbrecordnav(this);
    102. nav->setCurrentRec(mapper->currentIndex()+1);
    103. nav->setTotalRec(tableModel->rowCount());
    104.  
    105. QVBoxLayout *vblMain = new QVBoxLayout();
    106. vblMain->addLayout(hblNameCode);
    107. vblMain->addLayout(hblDept);
    108. vblMain->addLayout(hblName);
    109. vblMain->addLayout(hblStartDate);
    110. vblMain->addLayout(hblStatus);
    111. vblMain->addWidget(nav);
    112. setLayout(vblMain);
    113.  
    114. //finally connect statements
    115. connect(nav,SIGNAL(first()),this,SLOT(go2First()));
    116. connect(nav,SIGNAL(prev()),this,SLOT(go2Prev()));
    117. connect(nav,SIGNAL(next()),this,SLOT(go2Next()));
    118. connect(nav,SIGNAL(last()),this,SLOT(go2Last()));
    119. connect(nav,SIGNAL(newrec()),this,SLOT(addEmployee()));
    120. connect(nav,SIGNAL(deleterec()),this,SLOT(deleteEmployee()));
    121.  
    122. connect(bClose,SIGNAL(clicked()),this,SLOT(close()));
    123. }
    124. void employeeForm::go2First()
    125. {
    126. int row = mapper->currentIndex();
    127. mapper->submit();
    128. mapper->setCurrentIndex(row);
    129. mapper->toFirst();
    130. nav->setCurrentRec(mapper->currentIndex()+1);
    131. nav->setTotalRec(tableModel->rowCount());
    132. }
    133. void employeeForm::go2Prev()
    134. {
    135. int row = mapper->currentIndex();
    136. mapper->submit();
    137. mapper->setCurrentIndex(row);
    138. mapper->toPrevious();
    139. nav->setCurrentRec(mapper->currentIndex()+1);
    140. nav->setTotalRec(tableModel->rowCount());
    141. }
    142. void employeeForm::go2Next()
    143. {
    144. int row = mapper->currentIndex();
    145. mapper->submit();
    146. mapper->setCurrentIndex(row);
    147. mapper->toNext();
    148. nav->setCurrentRec(mapper->currentIndex()+1);
    149. nav->setTotalRec(tableModel->rowCount());
    150. }
    151. void employeeForm::go2Last()
    152. {
    153. int row = mapper->currentIndex();
    154. mapper->submit();
    155. mapper->setCurrentIndex(row);
    156. mapper->toLast();
    157. nav->setCurrentRec(mapper->currentIndex()+1);
    158. nav->setTotalRec(tableModel->rowCount());
    159. }
    160. void employeeForm::deleteEmployee()
    161. {
    162. int row = mapper->currentIndex();
    163. tableModel->removeRow(row);
    164. mapper->submit();
    165. mapper->setCurrentIndex(qMin(row,tableModel->rowCount()-1));
    166. nav->setCurrentRec(mapper->currentIndex()+1);
    167. nav->setTotalRec(tableModel->rowCount());
    168. }
    To copy to clipboard, switch view to plain text mode 
    Qt Code:
    1. void employeeForm::addEmployee()
    2. {
    3. //add records at the end of the list
    4. mapper->toLast();
    5. int row = mapper->currentIndex();
    6. mapper->submit();
    7. tableModel->insertRow(row+1);
    8. mapper->setCurrentIndex(row+1);
    9. //set the nav counts up 1
    10. nav->setCurrentRec(mapper->currentIndex()+1);
    11. nav->setTotalRec(tableModel->rowCount());
    12. leNameCode->clear();
    13. leFName->clear();
    14. leLName->clear();
    15. cbDept->clear();
    16. cbStatus->clear();
    17. deStartDate->setDate(QDate::currentDate());
    18. leNameCode->setFocus();
    19. }
    To copy to clipboard, switch view to plain text mode 

    As you can see, I nav to the last record, call submit on the mapper which I thought saved any started record edits (and seems to work), then call insertRow() clear the data fields and set the focus at the control ready to receive the latest information.

    If I cancel after the add, without doing any other navigations, the record is not added as it does not meet the null field criterion, i.e. tDepartment_idtDepartment and tStatus_idtStatus are null, therefore can't be added to the database.

    If I navigate back to a previous or the first record, however, it basically copies the record data of the record navigated to and saves it as the new record. I am confused by this as on a navigation, the first thing it should do is "submit" the record, which I would have thought that it would not save due to null value constraints...

    Can someone please explain how I should handle this navigation issue once the record is added?
    Thanks for the help!
    AlphaWolfXV

  3. #3
    Join Date
    Aug 2008
    Posts
    38
    Thanks
    15
    Qt products
    Qt4
    Platforms
    Windows

    Default Re: Database issue with adding record using QSqlRelationalTable and QDataWidgetMapper

    Ok, so I have kind of an update, when I added a qDebug() statement in each of the go2prev(), go2Next(), go2First(), and go2Last() where I look for the last error...

    Modifications:
    Qt Code:
    1. int row = mapper->currentIndex();
    2. mapper->submit();
    3. qDebug()<<"lasterror:"<<tableModel->lastError().text();
    To copy to clipboard, switch view to plain text mode 

    Shows the following when I add a new record with no new information and press previous...
    lasterror: "Field 'tDepartment_idtDepartment' doesn't have a default value QMYSQL3: Unable to execute statement"
    This is perfect, as it shouldn't be able to store a record with part of the fk's missing information. If I close the program at this point, the database is not modified and the information is ignored... However, when I click any other navigation button, it will place the copy of the first previous record as the new record...

    It appears as though since the submit failed, it is waiting for the next submit, which uses the current rowindex (a valid record) data, then re-submits and this time accepts because the valid record has valid fk's.

    Maybe a more general question: How is adding a record normally done in a form, should I be using a signal/slot or am I just missing a validation check? Or is there a way I should change to OnManualSubmit, and then how would I still be able to validate the data prior to submit? Any thoughts!
    Thanks,
    AlphaWolfXV
    Last edited by AlphaWolfXV; 4th August 2010 at 12:23.

  4. #4
    Join Date
    Aug 2008
    Posts
    38
    Thanks
    15
    Qt products
    Qt4
    Platforms
    Windows

    Cool SOLVED DB issue with adding record using QSqlRelationalTable and QDataWidgetMapper

    Ok, figured it out... For anyone else who might be experiencing similar issues the fix that I am using is:
    Qt Code:
    1. void employeeForm::go2First()
    2. {
    3. int row = mapper->currentIndex();
    4. if(! mapper->submit())
    5. {
    6. qDebug()<<"lasterror:"<<tableModel->lastError().text();
    7. tableModel->revertAll();
    8. }
    9. mapper->toFirst();
    10. nav->setCurrentRec(mapper->currentIndex()+1);
    11. nav->setTotalRec(tableModel->rowCount());
    12. }
    13. void employeeForm::go2Prev()
    14. {
    15. int row = mapper->currentIndex();
    16. if(! mapper->submit())
    17. {
    18. qDebug()<<"lasterror:"<<tableModel->lastError().text();
    19. tableModel->revertRow(row);
    20. }
    21. if(row > 0)
    22. row--;
    23. mapper->setCurrentIndex(row);
    24.  
    25. nav->setCurrentRec(mapper->currentIndex()+1);
    26. nav->setTotalRec(tableModel->rowCount());
    27. }
    28. void employeeForm::go2Next()
    29. {
    30. int row = mapper->currentIndex();
    31. if(!mapper->submit())
    32. {
    33. qDebug()<<"lasterror:"<<tableModel->lastError().text();
    34. tableModel->revertRow(row);
    35. row--;
    36. }
    37.  
    38. mapper->setCurrentIndex(row);
    39. mapper->toNext();
    40. nav->setCurrentRec(mapper->currentIndex()+1);
    41. nav->setTotalRec(tableModel->rowCount());
    42. }
    43. void employeeForm::go2Last()
    44. {
    45. int row = mapper->currentIndex();
    46. if(!mapper->submit())
    47. {
    48. qDebug()<<"lasterror:"<<tableModel->lastError().text();
    49. tableModel->revertRow(row);
    50. row--;
    51. }
    52. mapper->setCurrentIndex(row);
    53. mapper->toLast();
    54. nav->setCurrentRec(mapper->currentIndex()+1);
    55. nav->setTotalRec(tableModel->rowCount());
    56. }
    To copy to clipboard, switch view to plain text mode 

    Basically, just check if the submit failed, if it did, revert the row changes and update the index counter to match... Then just handle the mapper and anything else!
    AlphaWolfXV
    Last edited by AlphaWolfXV; 4th August 2010 at 19:52. Reason: SOLVED:

Similar Threads

  1. Replies: 2
    Last Post: 13th April 2010, 17:50
  2. Replies: 3
    Last Post: 26th March 2010, 05:32
  3. QDataWidgetMapper issue
    By vitaly in forum Qt Programming
    Replies: 1
    Last Post: 3rd June 2007, 04:28
  4. rollback issue with database
    By Djony in forum Qt Programming
    Replies: 8
    Last Post: 19th February 2007, 16:58
  5. Database access issue
    By Gayathri in forum Newbie
    Replies: 3
    Last Post: 23rd November 2006, 08:41

Tags for this Thread

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.