[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
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.