[SOLVED] SQLite Connected to db but doesn't execute query

nHx4U

[SOLVED] Hello all. I am still very new to programming - especially Qt, C++

I've created a simple ui application. Extracted from a YouTube Tutorial. I can get Qt to find and connect to a SQLite database BUT it doesn't execute the query properly. I do not get any error messages but the query comes back with "incorrect entry". Using SQLite Management Studio or Valentina Studio work properly!
My code is attached. I appreciate all the help I can get. Thank you.

SQLite_Login.PRO
#-------------------------------------------------

Project created by QtCreator 2013-12-09T18:18:23

#-------------------------------------------------

QT += core gui sql

greaterThan(QT_MAJOR_VERSION, 4): QT += widgets

TARGET = SQLite_Login
TEMPLATE = app

SOURCES += main.cpp
login.cpp

HEADERS += login.h

FORMS += login.ui

Login.h

#ifndef LOGIN_H
#define LOGIN_H

#include <QMainWindow>
#include<QtSql>
#include<QDebug>
#include<QFileInfo>

namespace Ui {
class Login;
}

class Login : public QMainWindow
{
Q_OBJECT

public:
explicit Login(QWidget *parent = 0);
~Login();

private slots:
void on_pushButton_clicked();

private:
Ui::Login *ui;
QSqlDatabase mydb;

};

#endif // LOGIN_H

main.cpp

#include "login.h"
#include <QApplication>

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

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

}

Login.cpp

#include "login.h"
#include "ui_login.h"

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

 mydb=QSqlDatabase::addDatabase("QSQLITE");
 
 mydb.setDatabaseName("C:/Users/Steve/Desktop/Qt_TEST_db.db3");

if (!mydb.open())
    ui->label->setText("CONNECTION FAILED");
else
    ui->label->setText("CONNECTED. . . . .");

}

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

void Login::on_pushButton_clicked()
{
QString username, password;
username=ui->lineEdit_username->text();
password=ui->lineEdit_Password->text();

    if(!mydb.isOpen()){
        qDebug()<<"Connection Failed";
        return;
    }
    QSqlQuery qry;

// IF MYDB.OPEN

    if (mydb.open())
       
    {
    
     if(qry.exec&#40;"select * from TEST_Login_99 where UserName=' "+username+" '  and PassWord=' " +password+" ' " &#41;&#41;
       
    {

        int count=0;
        while (qry.next(&#41;&#41;
        {
                count++;
        }

        if (count==1)
            ui->label->setText("Username and Password are CORRECT");

        if (count>1)
            ui->label->setText("DUPLICATE Username and Password");

        if (count<1)
            ui->label->setText(" INVALID Username and Password");


         qDebug() << "LAST ERROR: QRY SELECT:   "<<qry.lastError().text();

    }

}
}

Fahmy

Try to remove " from word username and password in query. If you still need " , try add "

nHx4U

Hi Fahmy:

Thanks for your reply. I changed the query to:

if(qry.exec("select * from employee"))

to generate a complete list - but still nothing.

Here's the table:

*empid | name | title

"101", "John Smith", "CEO"
"102", "Raj Reddy", "Sysadmin"
"103", "Jason Bourne", "Developer"

Here's the error message:

Starting C:\Users\Steve\Desktop\Qt_Tut-008\build-SQLIte_Login-Desktop_Qt_5_1_1_MinGW_32bit-Debug\debug\SQLite_Login.exe...
QRY_SIZE -1
LAST ERROR: QRY SELECT: " "

ALSO: Not clear on your previous answer: “ , try add \”

Thanks.

panosk

Please format your code so it is readable and more people can help.

nHx4U

Here's another copy of code: (I just figured out how to insert formatted code into the forum. I hope this helps. Thanks.). Did you need the ui? As a picture or HTML?

SQLite_Login.PRO

@#

Project created by QtCreator 2013-12-09T18:18:23

#-------------------------------------------------

QT += core gui sql

greaterThan(QT_MAJOR_VERSION, 4): QT += widgets

TARGET = SQLite_Login
TEMPLATE = app

SOURCES += main.cpp
login.cpp

HEADERS += login.h

FORMS += login.ui
@

Login.h

@#ifndef LOGIN_H
#define LOGIN_H

#include <QMainWindow>
#include<QtSql>
#include<QDebug>
#include<QFileInfo>

namespace Ui {
class Login;
}

class Login : public QMainWindow
{
Q_OBJECT

public:
explicit Login(QWidget *parent = 0);
~Login();

private slots:
void on_pushButton_clicked();

private:
Ui::Login *ui;
QSqlDatabase mydb;

};

#endif // LOGIN_H
@

main.cpp

@#include "login.h"
#include <QApplication>

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

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

}
@

Login.cpp

@#include "login.h"
#include "ui_login.h"

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

 mydb=QSqlDatabase::addDatabase("QSQLITE");
 
 mydb.setDatabaseName("C:/Users/Steve/Desktop/Qt_TEST_db.db3");

if (!mydb.open())
    ui->label->setText("CONNECTION FAILED");
else
    ui->label->setText("CONNECTED. . . . .");

}

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

void Login::on_pushButton_clicked()
{
QString username, password;
username=ui->lineEdit_username->text();
password=ui->lineEdit_Password->text();

    if(!mydb.isOpen()){
        qDebug()<<"Connection Failed";
        return;
    }
    QSqlQuery qry;

// IF MYDB.OPEN

    if (mydb.open())
       
    {
    
     if(qry.exec&#40;"select * from TEST_Login_99 where UserName=' "+username+" '  and PassWord=' " +password+" ' " &#41;)
       
    {

        int count=0;
        while (qry.next())
        {
                count++;
        }

        if (count==1)
            ui->label->setText("Username and Password are CORRECT");

        if (count>1)
            ui->label->setText("DUPLICATE Username and Password");

        if (count<1)
            ui->label->setText(" INVALID Username and Password");


         qDebug() << "LAST ERROR: QRY SELECT:   "<<qry.lastError().text();

    }

}
}
@

Fahmy

If you need to use variable don't write like that instead use binding. Try this.

@
qry.prepare("SELECT * FROM TEST_Login_99 where UserName = ? and PassWord = ?");
qry.addBindValue(username);
qry.addBindValue(password);
qry.exec();
@

panosk

Follow Fahmy's advice and you should be fine.
Also, a few more things you can do to improve your class:

You don't need to reopen the connection at line 39 in Login.cpp
@
if (mydb.open())
@

since you open the connection in the constructor and then you check it at line 31 in Login.cpp.

You should also do some cleanup, so close the database and remove the connection at the destructor.