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::on_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();


}

[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);

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,
_