QT SqlLite SELECT FROM does not update table

[editheraven]

Hello there.

I am building a small application just to learn how qt manages databases. Untill now I managed to display the db, insert new rows in it. Now I want to perform a query to select from that database and display the data in tableview. But after that, the table remains unchanged. Why is that?

The select query is at void MainWindow:n_pushButton_3_clicked().

#include "mainwindow.h"
#include "ui_mainwindow.h"
#include <QFileDialog>
#include <QMessageBox>
#include <QDesktopServices>
#include <QUrl>
#include <QProcess>
#include <QSqlDatabase>
#include <QtSql>
#include <QDebug>
#include <QFileInfo>
#include <QtWidgets>
#include <QLineEdit>
#include "addrecipe.h"
 
 
MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
{
    ui->setupUi(this);
 
 
   //setup database driver in main window
   QSqlDatabase mydb = QSqlDatabase::addDatabase("QSQLITE");
 
 
}
 
MainWindow::~MainWindow()
{
    delete ui;
}
 
void MainWindow::on_pushButton_clicked()
{
 
    //create database connection and show its contents
    QSqlDatabase mydb = QSqlDatabase::addDatabase("QSQLITE");
      mydb.setDatabaseName("C:\\sqlite-tools-win32-x86-3170000\\recipedb.db");
      mydb.open();
      if(mydb.open()){
 
       ui->label->setText("Database Connected");
 
 
              //setup table view
              QSqlQueryModel *model=new QSqlQueryModel();
 
              model->setQuery("SELECT empid, name, title FROM employee");
              model->setHeaderData(0, Qt::Horizontal, QObject::tr("empid"));
              model->setHeaderData(1, Qt::Horizontal, QObject::tr("name"));
              model->setHeaderData(2, Qt::Horizontal, QObject::tr("title"));
 
              ui->tableView->setModel(model);
              ui->tableView->setColumnWidth(0,100);
              ui->tableView->setColumnWidth(1,100);
              ui->tableView->setColumnWidth(2,100);              
              ui->tableView->horizontalHeader()->setStretchLastSection(true);
              ui->tableView->show();
 
 
 
  }
 
}
 
 
 
//creates new database with sqlite and bath file
void MainWindow::on_actionCreate_New_triggered()
{
    QFileInfo cmdFile( "C:\\Windows\\system32\\cmd.exe");
    QProcess *process = new QProcess( this );
    process->execute(cmdFile.absoluteFilePath() + " /c C:\\sqlite-tools-win32-x86-3170000\\recipe.bat");
    process->waitForFinished() ;
    process->kill();
}
 
 
 
//closing database connection
void MainWindow::on_pushButton_2_clicked()
{
    QSqlDatabase mydb = QSqlDatabase::database("QSQLITE");
 
 
    if (mydb.isOpen())
                    mydb.close();
     QSqlDatabase::removeDatabase("C:\\sqlite-tools-win32-x86-3170000\\recipedb.db");
 
     if(!mydb.open()){
 
         ui->label->setText("Database NOT Connected");}
 
}
 
 
//insert into database from input fields
void MainWindow::on_pushButton_4_clicked()
{
    QString empid, name, title;
    empid  = ui->lineEdit->text();
    name = ui->lineEdit_2->text();
    title = ui->lineEdit_3->text();
 
    QSqlQuery query;
    query.prepare("INSERT INTO employee (empid,name,title) VALUES ( :empid, :name, :title)");
    query.bindValue(":empid",empid);
    query.bindValue(":name",name);
    query.bindValue(":title",title);
        query.exec();
}
 
 
 
void MainWindow::on_lineEdit_cursorPositionChanged(int arg1, int arg2)
{
 
}
 
 
//new recipe window
void MainWindow::on_actionAdd_New_triggered()
{
    AddRecipe mDialogAddRecipe;
    mDialogAddRecipe.setModal(true);
    mDialogAddRecipe.exec();
 
}
 
 
//try to get select query
void MainWindow::on_pushButton_3_clicked()
{
 
 
    QSqlDatabase mydb = QSqlDatabase::addDatabase("QSQLITE");
      mydb.setDatabaseName("C:\\sqlite-tools-win32-x86-3170000\\recipedb.db");
            mydb.open();
 
     QSqlQueryModel *model=new QSqlQueryModel();
    QString name;
    name  = ui->lineEdit->text();
   // name = ui->lineEdit_2->text();
   // title = ui->lineEdit_3->text();
 
    QSqlQuery query;
    query.prepare("SELECT * FROM employee WHERE name = (:name)");
    query.bindValue(":name",name);
  //  query.bindValue(":name",name);
   // query.bindValue(":title",title);
    query.exec();
   model->setHeaderData(0, Qt::Horizontal, QObject::tr("empid"));
    model->setHeaderData(1, Qt::Horizontal, QObject::tr("name"));
    model->setHeaderData(2, Qt::Horizontal, QObject::tr("title"));
    ui->tableView->horizontalHeader()->setStretchLastSection(true);
    ui->tableView->show();
 
 
}

#include "mainwindow.h"
#include "ui_mainwindow.h"
#include <QFileDialog>
#include <QMessageBox>
#include <QDesktopServices>
#include <QUrl>
#include <QProcess>
#include <QSqlDatabase>
#include <QtSql>
#include <QDebug>
#include <QFileInfo>
#include <QtWidgets>
#include <QLineEdit>
#include "addrecipe.h"


MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
{
    ui->setupUi(this);


   //setup database driver in main window
   QSqlDatabase mydb = QSqlDatabase::addDatabase("QSQLITE");


}

MainWindow::~MainWindow()
{
    delete ui;
}

void MainWindow::on_pushButton_clicked()
{

    //create database connection and show its contents
    QSqlDatabase mydb = QSqlDatabase::addDatabase("QSQLITE");
      mydb.setDatabaseName("C:\\sqlite-tools-win32-x86-3170000\\recipedb.db");
      mydb.open();
      if(mydb.open()){

       ui->label->setText("Database Connected");


              //setup table view
              QSqlQueryModel *model=new QSqlQueryModel();

              model->setQuery("SELECT empid, name, title FROM employee");
              model->setHeaderData(0, Qt::Horizontal, QObject::tr("empid"));
              model->setHeaderData(1, Qt::Horizontal, QObject::tr("name"));
              model->setHeaderData(2, Qt::Horizontal, QObject::tr("title"));

              ui->tableView->setModel(model);
              ui->tableView->setColumnWidth(0,100);
              ui->tableView->setColumnWidth(1,100);
              ui->tableView->setColumnWidth(2,100);              
              ui->tableView->horizontalHeader()->setStretchLastSection(true);
              ui->tableView->show();



  }

}



//creates new database with sqlite and bath file
void MainWindow::on_actionCreate_New_triggered()
{
    QFileInfo cmdFile( "C:\\Windows\\system32\\cmd.exe");
    QProcess *process = new QProcess( this );
    process->execute(cmdFile.absoluteFilePath() + " /c C:\\sqlite-tools-win32-x86-3170000\\recipe.bat");
    process->waitForFinished() ;
    process->kill();
}



//closing database connection
void MainWindow::on_pushButton_2_clicked()
{
    QSqlDatabase mydb = QSqlDatabase::database("QSQLITE");


    if (mydb.isOpen())
                    mydb.close();
     QSqlDatabase::removeDatabase("C:\\sqlite-tools-win32-x86-3170000\\recipedb.db");

     if(!mydb.open()){

         ui->label->setText("Database NOT Connected");}

}


//insert into database from input fields
void MainWindow::on_pushButton_4_clicked()
{
    QString empid, name, title;
    empid  = ui->lineEdit->text();
    name = ui->lineEdit_2->text();
    title = ui->lineEdit_3->text();

    QSqlQuery query;
    query.prepare("INSERT INTO employee (empid,name,title) VALUES ( :empid, :name, :title)");
    query.bindValue(":empid",empid);
    query.bindValue(":name",name);
    query.bindValue(":title",title);
        query.exec();
}



void MainWindow::on_lineEdit_cursorPositionChanged(int arg1, int arg2)
{

}


//new recipe window
void MainWindow::on_actionAdd_New_triggered()
{
    AddRecipe mDialogAddRecipe;
    mDialogAddRecipe.setModal(true);
    mDialogAddRecipe.exec();

}


//try to get select query
void MainWindow::on_pushButton_3_clicked()
{


    QSqlDatabase mydb = QSqlDatabase::addDatabase("QSQLITE");
      mydb.setDatabaseName("C:\\sqlite-tools-win32-x86-3170000\\recipedb.db");
            mydb.open();

     QSqlQueryModel *model=new QSqlQueryModel();
    QString name;
    name  = ui->lineEdit->text();
   // name = ui->lineEdit_2->text();
   // title = ui->lineEdit_3->text();

    QSqlQuery query;
    query.prepare("SELECT * FROM employee WHERE name = (:name)");
    query.bindValue(":name",name);
  //  query.bindValue(":name",name);
   // query.bindValue(":title",title);
    query.exec();
   model->setHeaderData(0, Qt::Horizontal, QObject::tr("empid"));
    model->setHeaderData(1, Qt::Horizontal, QObject::tr("name"));
    model->setHeaderData(2, Qt::Horizontal, QObject::tr("title"));
    ui->tableView->horizontalHeader()->setStretchLastSection(true);
    ui->tableView->show();


}

To copy to clipboard, switch view to plain text mode 

[anda_skoa]

How do you imagine would the model magically know about the query object?
How do you imagine would the view magically know about the model object?

Cheers,
_

[editheraven]

I have managed to get it working with

model->setQuery("SELECT empid, name, title FROM employee where empid = 1");
...
ui->tableView->setModel(model);

model->setQuery("SELECT empid, name, title FROM employee where empid = 1");
...
ui->tableView->setModel(model);

To copy to clipboard, switch view to plain text mode 

But that is plain sql query.
I can't pass values into that. Can I connect the model to query.prepare instead?

[anda_skoa]

Can I connect the model to query.prepare instead?

Yes, pass the query object to the model instead of the query string.

Cheers,
_