[Qt C++] QTableView Model-View simple application P3

On the last part we added to our Table View the function (Model) to comunicate with the database file....

the goal for this part is to add the buttons of the GUI the functionality to add, and modify the records on the database.....so lets get in to it...

First we have to go to our desing manager interface.....we are going to use the SIGNAL/SLOT mechanism to create the functions linked to the Add Button and the Modify Button clicked event, to do this...right clic on the Add Button and select "Go To Slot"

then select the way the slot is going to be triggered, in this case we are going to use the "clicked" event....

NOTE: by the way if you dont know to much about the SIGNAL/SLOT mechanism on Qt, I recomend you to visit this link Qt 5 SIgnal/Slot mechanism

I'm going to do the same process for the "Modify" Push Button....so after that you will have two methods created on your mainwindow.cpp file and with the "private slots" identifier on your mainwindow.h file.

so now i'm going to edit the method's body of the "on_pb_AddRecord_clicked()"....first im going to store the fields values on some variables..

   QString device = ui->le_deviceName->text();
   int quantity = ui->sb_quantity->value();
   QString name = ui->le_name->text();
   QString description = ui->plainT_descriptOut->toPlainText();
   QString dateOut = ui->dateT_dateBorrow->text();

I'm going to check if one of those fields is empty before trying to add a new record to the database because some of the fields created on the database must be NOT NULL....

I'm going to use QMessageBox to show the message so you need to include <QMessageBox> on your mainwindow.h file

if(device.isEmpty()){
QMessageBox::information(this,tr("Error"),tr("fill device name"));
}else if(quantity == 0){
QMessageBox::information(this,tr("Error"),tr("fill quantity"));
   }else if(name.isEmpty()){
   QMessageBox::information(this,tr("Error"),tr("fill name"));
}

if the key fields are not empty I can write that dat to the database, for this I'm going to create a QSqlQuery, the prepare an INSERT query with the values of the variables....if you like to know more about the INSERT function for SQLite please follow this link....SQlite INSERT

   QSqlQuery qry;
   qry.prepare("INSERT INTO borrow (device, quantity, name, descriptionout, dateout, state)"    "VALUES(?,?,?,?,?,?);");
   qry.bindValue(0, device);
   qry.bindValue(1, quantity);
   qry.bindValue(2, name);
   qry.bindValue(3, description);
   qry.bindValue(4, dateOut);
qry.bindValue(5, "Borrowed");

this is a simple binding using QSqlQuery.....if you want to learn more about al the types of binding that can be used with QSqlQuery you can check....QSqlQuery details

now that I have prepared the query I need to execute it...we can do this by executing the "exec" function, also I'm going to clean the fields if the process is correct or throw a message if there is any error, and also, show what ckind of error is by using "lastError" method..

   if(qry.exec()){
ui->le_deviceName->clear();
ui->sb_quantity->setValue(0);
   ui->le_name->clear();
   ui->plainT_descriptOut->clear();
   ui->dateT_dateBorrow->setDateTime(QDateTime::currentDateTime());
   QMessageBox::information(this,tr("Add Device"),tr("Device Adding Success"));
borrowModel->select();
      } else QMessageBox::critical(this,tr("Error"),qry.lastError().text());

it is important that you call the "select" method of the model, so that the data on the table view gets updated...

now you can test you code by adding some new record and see how it gets updated..

I think it is too much work to search the date from 2000 to 2018 so I'm going to create both datetimes with the current date....this piece of codes goes in the creator of the class...

   ui->dateT_dateBorrow->setDateTime(QDateTime::currentDateTime());
   ui->dateT_dateReturn->setDateTime(QDateTime::currentDateTime());

now I'm going to fill the body of the "on_pb_modRecord_clicked()" method.....the function for this button is to write de state of the device as returned with its date and description.....

to do an UPDATE I need to know which record I'm going to update, so I need to identify by its unique identifier...the "id"...

the id is easy to get using its model index....the steps are....first identify the current selected row, then extract the id number from the model using the index of the current selected row....

   QModelIndex current=ui->tableView->currentIndex();
   int row=current.row();
   int currentIndex=ui->tableView->model()->data(ui->tableView->model()->index
   (row,borrowModel->fieldIndex("id"))).toInt();

the logic after that is pretty much the same....I'm going to store the data in some variables and then using an UPDATE query to update the record with this new data....if you want more info about UPDATE on SQLite you can follow this link....SQLite UPDATE

so your on_pb_modRecord_clicked() should look like this...I hope...

void MainWindow::on_pb_modRecord_clicked()

{
QModelIndex current=ui->tableView->currentIndex();
   int row=current.row();
   int currentIndex=ui->tableView->model()->data(ui->tableView->model()->index
   (row,borrowModel->fieldIndex("id"))).toInt();

   QString dateIn = ui->dateT_dateReturn->text();
   QString descripcionIn = ui->plainT_descriptIn->toPlainText();

QSqlQuery qry; qry.prepare("UPDATE borrow SET state=? , datein=? , descriptionin=?
   WHERE id=?");
qry.bindValue(0, "returned");
qry.bindValue(1, dateIn);
qry.bindValue(2, descripcionIn);
   qry.bindValue(3, currentIndex);

if(qry.exec()){
QMessageBox::information(this,tr("Modify State"),tr("Data modify success"));
borrowModel->select();
} else QMessageBox::critical(this,tr("Error modifying data"),qry.lastError().text());

}

I'm going to add this function the ability to fill the fields like the device name, the name, the date and the description to have like a tip of the info that was recorded when the device was borrowed.....I'm going to use the Model Index I've used to get the index to also get the other fields and fill that info...

first I'm going to create a method that clears all the fields..

void MainWindow::clearFields() {
ui->le_deviceName->clear();
ui->le_name->clear();
ui->plainT_descriptIn->clear();
ui->plainT_descriptOut->clear();
ui->sb_quantity->setValue(0);
ui->dateT_dateBorrow->setDateTime(QDateTime::currentDateTime());
ui->dateT_dateReturn->setDateTime(QDateTime::currentDateTime());
}

now I'm going to create a new slot using the clicked signal provided by the table...

now I'm going to clear the values first, then check if the index is valid, and if so is because there is some data on that field...so I'm going to retrieve that data and fill the fields...

void MainWindow::on_tableView_clicked(const QModelIndex &index){
clearFields();
if(index.isValid()){
int row=index.row();
ui->le_deviceName->setText(ui->tableView->model()->data(ui->tableView->model()
   ->index(row,borrowModel->fieldIndex("device"))).toString());

ui->le_name->setText(ui->tableView->model()->data(ui->tableView->model()->index
   (row,borrowModel->fieldIndex("name"))).toString());

ui->sb_quantity->setValue(ui->tableView->model()->data(ui->tableView->model()
   ->index(row,borrowModel->fieldIndex("quantity"))).toInt());

ui->plainT_descriptOut->document()->setPlainText(ui->tableView->model()->data
(ui->tableView->model()->index(row,borrowModel->fieldIndex
("descriptionout"))).toString());

   ui->dateT_dateBorrow->setDateTime(QDateTime::fromString(ui->tableView->model()
->data(ui->tableView->model()->index
   (row,borrowModel->fieldIndex("dateout"))).toString(),"yyyy/M/d HH:mm:ss"));

ui->plainT_descriptIn->document()->setPlainText(ui->tableView->model()->data
(ui->tableView->model()->index
   (row,borrowModel->fieldIndex("descriptionout"))).toString());

   ui->dateT_dateReturn->setDateTime(QDateTime::fromString(ui->tableView->model()
->data(ui->tableView->model()->index
(row,borrowModel->fieldIndex("dateout"))).toString(),"yyyy/M/d HH:mm:ss"));
   }
}

so in this point we have added some useful functions to our application....just the basics.....in the next part I'm going to implement the search function using a filter proxy model, populate the filter combo box and also add some mouse function menu.....