[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 = appSOURCES += main.cpp
login.cppHEADERS += 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_OBJECTpublic:
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();
}
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("select * from TEST_Login_99 where UserName=' "+username+" ' and PassWord=' " +password+" ' " )) { 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(); }
}
}
-
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 = appSOURCES += main.cpp
login.cppHEADERS += 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_OBJECTpublic:
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();
}
@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("select * from TEST_Login_99 where UserName=' "+username+" ' and PassWord=' " +password+" ' " )) { 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.
- You don't need to reopen the connection at line 39 in Login.cpp