Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

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



  • [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();
    
        }
    

    }
    }




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



  • 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.



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



  • 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();
    
        }
    

    }
    }
    @



  • 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();
    @



  • 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.

Log in to reply