Help in this code for selecting data from db

doforumda · wrote on 19 Oct 2010, 12:41

hi now i am trying to display db data. the problem here is it is not displaying any error but it also does not display data from database.
here is my code
dvd_sw.h
@
#ifndef DVD_SW_H
#define DVD_SW_H
#include "addDialog.h"

#include <QDialog>
/*
namespace Ui {
class dvd_sw;
}
*/
class QPushButton;
class QLineEdit;
class QTableView;
class QLabel;

class dvd_sw : public QDialog
{
Q_OBJECT

public:
dvd_sw(QWidget *parent = 0);
void select_dvds();
void connect_db();

public slots:
void add_dvd();

private:
QLabel *dvd_name_label;
QLabel *dvd_num_label;
QLineEdit *dvd_name_line;
QLineEdit *dvd_num_line;
QPushButton *add_button;
QTableView *data_table;
adddialog *dialog;
};

#endif // DVD_SW_H

@

dvd_sw.cpp
@
#include <QtGui>
#include <QtSql>
#include "dvd_sw.h"
#include "ui_dvd_sw.h"

dvd_sw::dvd_sw(QWidget *parent) :
QDialog(parent)
{
data_table = new QTableView;

 add_button = new QPushButton(tr("&Add DVD"));

 dialog = new adddialog;

 connect(add_button, SIGNAL(clicked()), this, SLOT(add_dvd()));

 QGridLayout *mainLayout = new QGridLayout;
 mainLayout->addWidget(data_table, 0, 0);
 mainLayout->addWidget(add_button, 1, 0);
 setLayout(mainLayout);
 setWindowTitle(tr("DVD Software"));

}

void dvd_sw::add_dvd()
{
dialog->show();

if(dialog->exec&#40;&#41; == QDialog::Accepted&#41; {
    QString getDvdName = dialog->nameOfDvd();
}

}

void dvd_sw::select_dvds()
{
connect_db();
QSqlTableModel *model = new QSqlTableModel;
model->setTable("dvdTable");
model->select();
model->setHeaderData(0, Qt::Horizontal, "First Name");
model->setHeaderData(1, Qt::Horizontal, "Last Name");

//QTableView *view = new QTableView;
data_table->setModel(model);
data_table->setEditTriggers(QAbstractItemView::NoEditTriggers);

}

void dvd_sw::connect_db()
{
QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
db.setHostName("localhost");
db.setDatabaseName("dvd");
db.setUserName("root");
db.setPassword("xxxxxx");

bool ok = db.open();
if(ok) {
    qDebug() << "Database opened";
}
else {
    qDebug() << "Database not opened";
}

}
@

main.cpp
@
#include <QtGui/QApplication>
#include "dvd_sw.h"

int main(int argc, char *argv[])
{
QApplication a(argc, argv);
dvd_sw w;
w.show();

return a.exec(&#41;;

}
@

how can i solve this problem? help please

DenisKormalev · wrote on 19 Oct 2010, 13:04

Hm, looks like you never execute select_dvds() function that fetches and shows data.

doforumda · wrote on 19 Oct 2010, 13:07

yep got it. newbie mistake :)

doforumda · wrote on 19 Oct 2010, 13:22

now i changed my dvd_sw.cpp code and add insert query to this but it does not work.
@
#include <QtGui>
#include <QtSql>
#include "dvd_sw.h"
#include "ui_dvd_sw.h"

dvd_sw::dvd_sw(QWidget *parent) :
QDialog(parent)
{
data_table = new QTableView;

 add_button = new QPushButton(tr("&Add DVD"));

 dialog = new adddialog;

 select_dvds();

 connect(add_button, SIGNAL(clicked()), this, SLOT(add_dvd()));

 QGridLayout *mainLayout = new QGridLayout;
 mainLayout->addWidget(data_table, 0, 0);
 mainLayout->addWidget(add_button, 1, 0);
 setLayout(mainLayout);
 setWindowTitle(tr("DVD Software"));

}

void dvd_sw::add_dvd()
{
dialog->show();

if(dialog->exec&#40;&#41; == QDialog::Accepted) {
    QString getDvdName = dialog->nameOfDvd();

    connect_db();
    QSqlQuery query;
    bool q = query.exec&#40;"INSERT INTO dvdTable(id, dvdName&#41; VALUES (NULL, getDvdName)");
    if(q) {
        QMessageBox::information(this, tr("Data Added"),
                                 tr("\"%1\" has been added").arg(getDvdName));
        select_dvds();
    }
    else {
        QMessageBox::information(this, tr("Data add Failed"),
                                 tr("\"%1\" could not be added").arg(getDvdName));
    }
}

}

void dvd_sw::select_dvds()
{
connect_db();
QSqlTableModel *model = new QSqlTableModel;
model->setTable("dvdTable");
model->select();
model->setHeaderData(0, Qt::Horizontal, "First Name");
model->setHeaderData(1, Qt::Horizontal, "Last Name");

//QTableView *view = new QTableView;
data_table->setModel(model);
data_table->setEditTriggers(QAbstractItemView::NoEditTriggers);

}

void dvd_sw::connect_db()
{
QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
db.setHostName("localhost");
db.setDatabaseName("dvd");
db.setUserName("root");
db.setPassword("123456");

bool ok = db.open();
if(ok) {
    qDebug() << "Database opened";
}
else {
    qDebug() << "Database not opened";
}

}

@

ZapB · wrote on 19 Oct 2010, 13:25

It is because you are never actually calling your dvd_sw::select_dvds() function anywhere.

Change your main() function to:

@int main(int argc, char *argv[])
{
QApplication a(argc, argv);
dvd_sw w;
w.select_dvds()
w.show();

return a.exec&#40;&#41;;

}@

or simply call select_dvds() from your constructor.

Another problem is that you are also creating a model every time you can call the dvd_sw::select_dvds() function. Instead just add the model as a member variable and create it in the constructor along with the view.

doforumda · wrote on 19 Oct 2010, 13:42

now i changed my code to something this which as follow. now it does not even display data from database.
dvd_sw.h
@
#ifndef DVD_SW_H
#define DVD_SW_H
#include "addDialog.h"

#include <QDialog>
#include <QtSql>
/*
namespace Ui {
class dvd_sw;
}
*/
class QPushButton;
class QLineEdit;
class QTableView;
class QSqlTableModel;
class QLabel;

class dvd_sw : public QDialog
{
Q_OBJECT

public:
dvd_sw(QWidget *parent = 0);
void select_dvds();
void connect_db();

public slots:
void add_dvd();

private:
QLabel *dvd_name_label;
QLabel *dvd_num_label;
QLineEdit *dvd_name_line;
QLineEdit *dvd_num_line;
QPushButton *add_button;
QTableView *data_table;
QSqlTableModel *model;
adddialog *dialog;
};

#endif // DVD_SW_H
@

dvd_sw.cpp
@
#include <QtGui>
//#include <QtSql>
#include "dvd_sw.h"
#include "ui_dvd_sw.h"

dvd_sw::dvd_sw(QWidget *parent) :
QDialog(parent)
{
data_table = new QTableView;
model = new QSqlTableModel;

 add_button = new QPushButton(tr("&Add DVD"));

 dialog = new adddialog;

 //select_dvds();

 connect(add_button, SIGNAL(clicked()), this, SLOT(add_dvd()));

 QGridLayout *mainLayout = new QGridLayout;
 mainLayout->addWidget(data_table, 0, 0);
 mainLayout->addWidget(add_button, 1, 0);
 setLayout(mainLayout);
 setWindowTitle(tr("DVD Software"));

}

void dvd_sw::add_dvd()
{
dialog->show();

if(dialog->exec&#40;&#41; == QDialog::Accepted) {
    QString getDvdName = dialog->nameOfDvd();

    connect_db();
    QSqlQuery query;
    bool q = query.exec&#40;"INSERT INTO dvdTable(id, dvdName&#41; VALUES (NULL, getDvdName)");
    if(q) {
        QMessageBox::information(this, tr("Data Added"),
                                 tr("\"%1\" has been added").arg(getDvdName));
        //select_dvds();
    }
    else {
        QMessageBox::information(this, tr("Data add Failed"),
                                 tr("\"%1\" could not be added").arg(getDvdName));
    }
}

}

void dvd_sw::select_dvds()
{
connect_db();
//QSqlTableModel *model = new QSqlTableModel;
model->setTable("dvdTable");
model->select();
model->setHeaderData(0, Qt::Horizontal, "ID");
model->setHeaderData(1, Qt::Horizontal, "DVD Name");

//QTableView *view = new QTableView;
data_table->setModel(model);
data_table->setEditTriggers(QAbstractItemView::NoEditTriggers);

}

void dvd_sw::connect_db()
{
QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
db.setHostName("localhost");
db.setDatabaseName("dvd");
db.setUserName("root");
db.setPassword("xxxxxx");

bool ok = db.open();
if(ok) {
    qDebug() << "Database opened";
}
else {
    qDebug() << "Database not opened";
}

}
@

main.cpp
@
#include <QtGui/QApplication>
#include "dvd_sw.h"

int main(int argc, char *argv[])
{
QApplication a(argc, argv);
dvd_sw w;
w.select_dvds();
w.show();

return a.exec&#40;&#41;;

}
@

ZapB · wrote on 19 Oct 2010, 13:48

Does your database actually contain any data? Are you sure you have the connection parameters correct? Is the table name correct? What is the output of running a simple "SELECT * FROM dvdTable" query?

Do you get any output form your application at all? Do you see the "Database opened" or "Database not opened" messages from connect_db()?

doforumda · wrote on 19 Oct 2010, 13:52

well when i ran the code which is given at the start of this post it displayed data when i call select_db() function after line 15 which is now commented which is in database table.everything is correct here. it displays database opened whenever i execute this code.
i dont know why it is not displaying data.

Eltharan · wrote on 23 Oct 2010, 17:29

Hi
I've got the same problem. In my case, model->select returns false. Db connection works, normal queries work, there is no database error after creating table. Code is almost copied from qtdoc and... it doesn't work.

goetz · wrote on 23 Oct 2010, 19:44

Does select() return true or false?
If true - the select was ok - can you get some values from the result via
@model.record(0).value("fieldname").toString();
@

If false - get the error via model->lastError().

Regarding the insert problem:
You must put the value of getDvdName into the query. In your version you try to put the literal string getDvdName as name into your database, which will fail, since it is not enclosed in single quotes (').

It would work this way:

@
QString sql = QString("INSERT INTO table (id, name) VALUES (NULL, '%1')").arg(getDvdName);
@

But this is suboptimal, since it allows for SQL injection. Better use:

@
QString sql = "INSERT INTO table (id, name) VALUES (NULL, :DvdName)";
QSqlQuery query;
query.prepare(sql);
query.bindValue(":DvdName", getDvdName);
@

See the "docs of QSqlQuery":http://doc.trolltech.com/4.7/qsqlquery.html#approaches-to-binding-values for further details.