Urgent!! Unable to create and insert values into database

Jocelyn

//MY code here
#include "register.h"
#include "ui_register.h"
#include <QTime>
#include <QDebug>
#include <QtSql>
#include <QMessageBox>
#include <QApplication>
#include <QSqlQuery>
#include <QSqlError>
#include <QMessageBox>
#include <QTableView>
#include <QLabel>
#include <QComboBox>
#include <QPushButton>
#include <QGroupBox>
#include <QLineEdit>
#include <QString>
#include <QDate>
#include <QDateTime>


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

    QSqlDatabase dbSaya= QSqlDatabase::addDatabase("QSQLITE");

    dbSaya.setDatabaseName
            ("C:/Users/user/Documents/BITI Sem 3/Bengkel 1 Qt/shop.db");

}

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

void Register::on_registerPushButton_clicked()
{
    QString userId,userName,password,confirmPassword,contactNumber,
            email;

    userId = ui->userIdLineEdit->text();
    userName = ui->userNameLineEdit->text();
    password = ui->passwordLineEdit->text();
    confirmPassword = ui->confirmPswdLineEdit->text();
    contactNumber = ui->contactNumberLineEdit->text();
    email = ui->emailLineEdit->text();

    ui->facultyComboBox->setCurrentIndex(0);

    ui->userIdLineEdit->clear();
    ui->userNameLineEdit->clear();
    ui->passwordLineEdit->clear();
    ui->confirmPswdLineEdit->clear();
    ui->contactNumberLineEdit->clear();
    ui->emailLineEdit->clear();

    QSqlQuery querySaya;
    querySaya.prepare("CREATE TABLE IF NOT EXISTS Account(UserId VARCHAR(10)UNIQUE PRIMARY KEY,UserName VARCHAR(30),Password VARCHAR(30),ConfirmPassword VARCHAR(30),ContactNumber VARCHAR(12),Email VARCHAR(15),PRIMARY KEY (UserId,UserName)");
    querySaya.exec();

    querySaya.prepare("INSERT INTO Account(UserId,UserName,Password,ConfirmPassword,ContactNumber,Email)VALUES('"+userId+"','"+userName+"','"+password+"','"+confirmPassword+"','"+contactNumber+"','"+email+"')");

    querySaya.exec();

}

SGaist

Hi,

You haven't opened the database connection before executing your queries.

Jocelyn

@SGaist
thanks in advance..
but my problem now is that l cant even create my table Account and insert data into it...
that's why l put database connection..
erm, l'll remove it after this...

SGaist

You are also not checking if your queries are running successfully. Add them and if they fail print the error string to have some clues about what is going wrong.

Jocelyn

@SGaist
actually error comes out..l cant detect where's my problem..sry...ahha
is my query sth wrong??

SGaist

Care to share the error message(s) ?

Jocelyn

@SGaist
thanks =]

Starting C:\Users\user\Documents\BITI Sem 3\Bengkel 1 Qt\build-SIDEACC-Desktop_Qt_5_5_0_MSVC2013_64bit-Debug\debug\SIDEACC.exe...

QSqlDatabasePrivate::addDatabase: duplicate connection name 'qt_sql_default_connection', old connection removed.

QSqlDatabasePrivate::addDatabase: duplicate connection name 'qt_sql_default_connection', old connection removed.

SGaist

That's a warning, not an error. You are calling addDatabase several time so you are replacing the current connection (in this case the default connection) each time.

Jocelyn

@SGaist
or is it that l didnt declare my header of Account in other place else??

SGaist

It's not a declaration problem, are you instantiating Register several times ?

Jocelyn

@SGaist
erm...no, l think..
maybe l show you my register.h, main.cpp and logininterface.cpp

//register.h
#ifndef REGISTER_H
#define REGISTER_H

#include <QDialog>
#include<QtSQL>

namespace Ui {
class Register;
}

class Register : public QDialog
{
    Q_OBJECT

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

private slots:
    void on_registerPushButton_clicked();


private:
    Ui::Register *ui;

};

#endif // REGISTER_H

//main.cpp
#include "logininterface.h"
#include "register.h"
#include <QTime>
#include <QDebug>
#include <QtSql>
#include <QMessageBox>
#include <QApplication>
#include <QSqlQuery>
#include <QSqlError>
#include <QMessageBox>
#include <QSplashScreen>
#include <QTimer>

bool buatConnection();

int main(int argc, char *argv[])
{
    QApplication a(argc, argv);

    QSplashScreen *splash = new QSplashScreen;
    splash->setPixmap(QPixmap("‪C:/Users/user/Documents/"
                              "BITI Sem 3/Bengkel 1 Qt/Demo/shop.png"));
    splash->show();

    LoginInterface w;
    //w.show();

    QTimer::singleShot(5000,splash,SLOT(close()));
    QTimer::singleShot(5000,&w,SLOT(show()));

    /*LoginInterface w;
    w.show();
    if(!buatConnection())
        return false;
    return a.exec();*/

    if(!buatConnection())
        return false;

    return a.exec();
}

bool buatConnection()
{
    QSqlDatabase dbSaya = QSqlDatabase::addDatabase("QSQLITE");

    dbSaya.setDatabaseName("C:/Users/user/Documents/BITI Sem 3/Bengkel 1 Qt/shop.db");

    if(!dbSaya.open())
    {
        QMessageBox::critical(0, QObject::tr("Database failed to open..."),
                              dbSaya.lastError().text());
        return false;
    }

    else

    //no need to show, for not to spoilt
    /*else
        QMessageBox::information(0,"Database Connection","Database is connected successfully!");*/
    return true;
}

//logininterface.cpp
#include "logininterface.h"
#include "ui_logininterface.h"
#include <QTime>
#include <QDebug>
#include <QtSql>
#include <QMessageBox>
#include <QApplication>
#include <QSqlQuery>
#include <QSqlError>
#include <QMessageBox>
#include <QSqlError>


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

    QPixmap pix
            ("C:/Users/user/Documents/BITI Sem 3/Bengkel 1 Qt/article-image-welcome.jpg");
    ui->picLabel->setPixmap(pix);
    //C:/Users/user/Documents/BITI Sem 3/Bengkel 1 Qt/article-image-welcome.jpg

    QSqlDatabase dbSaya= QSqlDatabase::addDatabase("QSQLITE");

    dbSaya.setDatabaseName
            ("C:/Users/user/Documents/BITI Sem 3/Bengkel 1 Qt/shop.db");

    if(!dbSaya.open())
        QMessageBox::critical(0, QObject::tr("Login failed to open..."),
                              dbSaya.lastError().text());
    //no need to show, for not to spoilt
    /*else
    {

        QMessageBox::information(0,"Database Interface",
                                 "Welcome to this system,"
                                 "\nplease login before proceeding...\t");
    }*/
}


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

void LoginInterface::on_loginPushButton_clicked()
{
    QString userId,passWord;
    userId=ui->userIdLineEdit->text();
    passWord=ui->passwordLineEdit->text();

    ui->userIdLineEdit->setText(userId);
    ui->passwordLineEdit->setText(passWord);

    QSqlQuery querySaya;
   /*querySaya.prepare("CREATE TABLE IF NOT EXISTS Login
    * (userId VARCHAR(12)PRIMARY KEY, passWord VARCHAR(20))");

    if (!querySaya.exec())
    {
        //QSqlError lastError();
        //QSqlQuery::lastError();
        //QSqlError lastError()

        qDebug()<<querySaya.lastError().text();
    }
    else
        qDebug()<<"Table created";*/

    querySaya.prepare("SELECT * FROM Login where "
                      "userId='"+userId+"' and passWord='"+passWord+"')");

    if(querySaya.next())
    {
        QMessageBox::warning(this,"Login successfully",
                             querySaya.lastError().text());
    }
    else
    {
        ui->statusLabel->setText("[!] Invalid UserID "
                                 "and Password..\n  "
                                 "Please enter again! ");
        ui->userIdLineEdit->setText(userId);
        ui->passwordLineEdit->setText(passWord);
    }


    /*if (querySaya.exec("SELECT * FROM Login WHERE
     * userID='"+userId+"'and passWord='"+passWord+"'"))
    {

        if(passWord == passWord)
        {
            if (userId == userId)
            {
                QMessageBox::information(this,"USER ID","Matched");
                this->hide();
                tetingkapSideacc.show();

            }


            else
            {
                QMessageBox::critical(this,"ERROR",querySaya.lastError().text());
            }
            //QMessageBox::information(this,"PASSWORD","Password Matched, Login!");
        }

        else
        {

            QMessageBox::critical(this,"PASSWORD ERROR",
                querySaya.lastError().text());
        }

    }*/


    if(querySaya.exec("SELECT * FROM Login WHERE "
                      "userID='"+userId+"'and passWord='"+passWord+"'"))
    {
        int count = 0;
        while (querySaya.next())
        {
            count++;
        }
        if(count==1)
        {
            //ui->userIdLineEdit->setText(userId);
            //ui->passwordLineEdit->setText(passWord);
            ui->statusLabel->setText("User ID and Password are correct. "
                                     "Login successfully!");
            ui->userIdLineEdit->setText(userId);
            ui->passwordLineEdit->setText(passWord);
            this->hide();
            tetingkapSideacc.show();
        }

        if(count>1)
        {
            ui->statusLabel->setText("Duplicate User ID and Password. "
                                     "Please reset.");
            ui->userIdLineEdit->clear();
            ui->passwordLineEdit->clear();
        }

        if(count<1)
        {
            ui->statusLabel->setText("User ID and Password are not correct.\n"
                                     "Please reset.");
            ui->userIdLineEdit->clear();
            ui->passwordLineEdit->clear();            
        }
    }
}

void LoginInterface::on_resetPushButton_clicked()
{
    QString userId,passWord;
    ui->userIdLineEdit->clear();
    ui->passwordLineEdit->clear();
    ui->userIdLineEdit->setText(userId);
    ui->passwordLineEdit->setText(passWord);
}

/*void LoginInterface::on_commandLinkButton_clicked()
{
    QString userId,passWord;
    userId=ui->userIdLineEdit->text();
    passWord=ui->passwordLineEdit->text();

    ui->userIdLineEdit->setText(userId);
    ui->passwordLineEdit->setText(passWord);

    if(querySaya.exec("SELECT * FROM Login WHERE "
                      "userID='"+userId+"'and passWord='"+passWord+"'"))
    {
        int count = 0;
        while (querySaya.next())
        {
            count++;
        }

        if(count<1)
        {
            ui->statusLabel->setText("User ID and Password are not correct.\n"
                                     "Please reset.");
            ui->userIdLineEdit->clear();
            ui->passwordLineEdit->clear();
        }
}*/


void LoginInterface::on_forgotPswdCommandLinkButton_clicked()
{
    tetingkapResetPassword.show();

    /*QString userId,passWord;
    ui->userIdLineEdit->setText(userId);
    ui->passwordLineEdit->setText(passWord);

    QSqlQuery querySaya;
    if(querySaya.exec("SELECT * FROM Login WHERE "
                      "userID='"+userId+"'"))
    {
        int count = 0;
        while (querySaya.next())
        {
            count++;
        }
        if(count==1)
        {
            QMessageBox::information
                    (this,"Reset password",
                     "Reset here");
            QMessageBox ::StandardButton reply;
            if (querySaya.exec("SELECT * FROM Login "
                           "WHERE Password='"+passWord+"'"))
            {
                reply = QMessageBox::information
                            (this,"Change Password",
                             "Password is changed successfully!");
            }
        }
    }*/
}

void LoginInterface::on_createAccPushButton_clicked()
{
    tetingkapRegister.show();
}

//logininterface.h
#ifndef LOGININTERFACE_H
#define LOGININTERFACE_H

#include <QDialog>
#include <QtSQL>
#include "sideacc.h"
#include "register.h"
#include "resetpassword.h"

namespace Ui {
class LoginInterface;
}

class LoginInterface : public QDialog
{
    Q_OBJECT

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

private slots:
    void on_loginPushButton_clicked();

    void on_resetPushButton_clicked();

    void on_forgotPswdCommandLinkButton_clicked();

    void on_createAccPushButton_clicked();

private:
    Ui::LoginInterface *ui;
protected:
    SIDEACC tetingkapSideacc;
    Register tetingkapRegister;
    ResetPassword tetingkapResetPassword;

};

#endif // LOGININTERFACE_H

SGaist

You have at least three calls to addDatabase. That's not useful. Do it once e.g. in your main function. There's no need to call addDatabase several time if you are only going to use one connection.

Jocelyn

@SGaist
oh l see..l've removed them..thanks =]
but my problem is that my table Account is unable to be created in my databse..

SGaist

Again, check the return value of exec and print the content of errorString if the query failed. That will give hints about what is going on.