Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. Qt prevent to insert duplicate data SQLITE Problem!
Forum Updated to NodeBB v4.3 + New Features

Qt prevent to insert duplicate data SQLITE Problem!

Scheduled Pinned Locked Moved Solved General and Desktop
6 Posts 4 Posters 1.1k Views 2 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • S Offline
    S Offline
    Siamak Royal
    wrote on 23 Sept 2020, 07:23 last edited by Siamak Royal
    #1

    Hi there . i have a problem in login and register form that i built in qt ( I am beginner ) . so i wrote a code to prevent inserting duplicate data in sqlite and i write this part of code in buttonClicked of registering.

    if(data.open()){
            QString username,password,email,phone;
            username = ui->line_Edit_username_Registery->text();
            password = ui->line_Edit_password_Registery->text();
            email = ui->line_edit_email->text();
            phone = ui->line_edit_phone->text();
    
            //Query Check
            QSqlQuery queryCheck;
            queryCheck.prepare(QString("SELECT * FROM register where username = :username AND        password = :password AND email = :email AND phone = :phone"));
            queryCheck.bindValue(":username", username);
            queryCheck.bindValue(":password", password);
            queryCheck.bindValue(":email",email);
            queryCheck.bindValue(":phone",phone);
    
    
            bool check = true;
            if ( queryCheck.exec())
            {
    
                while (queryCheck.next()) {
                    QString usernameDB,passwordDB,emailDB,phoneDB;
                    username_database = queryCheck.value(1).toString();
                    password_database = queryCheck.value(2).toString();
                    email_database = queryCheck.value(3).toString();
                    phone_database = queryCheck.value(4).toString();
                    if(username == username_database || phone == phone_database || email == email_database){
                        QMessageBox::information(this,"Error!","Duplicated!");
                        check = false;
    
                    }
    
                }
            }// End Query Check
    

    so as you can see i wrote a condition
    if(username == username_database || phone == phone_database || email == email_database).
    to prevent duplicate data and when i insert all four data ( username , password , email , phone ) data won't insert to SQLITE (Duplicate error QMessageBox::information(this,"Error!","Duplicated!"); ).
    but when i for example changing password and then insert it . data is going to insert in database while there is duplicate user name and phone and password.

    how can i solve this problem.

    Whole Code of Button_Clicked Register Form.

    void mainwindow::on_pushButton_register_clicked()
    {
    
       if(data.open()){
           QString username,password,email,phone;
           username = ui->line_Edit_username_Registery->text();
           password = ui->line_Edit_password_Registery->text();
           email = ui->line_edit_email->text();
           phone = ui->line_edit_phone->text();
    
           //Query Check
           QSqlQuery queryCheck;
           queryCheck.prepare(QString("SELECT * FROM register where username = :username AND        password = :password AND email = :email AND phone = :phone"));
           queryCheck.bindValue(":username", username);
           queryCheck.bindValue(":password", password);
           queryCheck.bindValue(":email",email);
           queryCheck.bindValue(":phone",phone);
    
    
           bool check = true;
           if ( queryCheck.exec())
           {
    
               while (queryCheck.next()) {
                   QString usernameDB,passwordDB,emailDB,phoneDB;
                   username_database = queryCheck.value(1).toString();
                   password_database = queryCheck.value(2).toString();
                   email_database = queryCheck.value(3).toString();
                   phone_database = queryCheck.value(4).toString();
                   if(username == username_database || phone == phone_database || email == email_database){
                       QMessageBox::information(this,"Error!","Duplicated!");
                       check = false;
    
                   }
    
               }
           }// End Query Check
    
    
           // query insert
           if(check){
               QSqlQuery query;
               query.prepare("INSERT INTO register (username, password , email , phone) "
                             "VALUES (:username, :password, :email , :phone)");
               query.bindValue(":username", username);
               query.bindValue(":password", password);
               query.bindValue(":email", email);
               query.bindValue(":phone", phone);
    
    
                   bool chkbox = ui->checkBox->isChecked();
                   //Checking checkbox condition
                   if (chkbox){
                       if(query.exec())
                           QMessageBox::information(this,"this","yes");
                       else
                           QMessageBox::information(this,"this","no");
                   }
                   else {
                       QMessageBox::information(this,"title","Please accept the rules");
                   }//Checking checkbox condition
           }
       }
       else{
           QMessageBox::information(this,"this","database didn't connect");
       }
    }
    
    
    G 1 Reply Last reply 23 Sept 2020, 09:03
    0
    • S Offline
      S Offline
      Siamak Royal
      wrote on 23 Sept 2020, 18:44 last edited by
      #6

      i find the solution . the problem was in selecting table from database and i replace AND by OR and then it checks them one by one .

      thank you .

      1 Reply Last reply
      1
      • S Siamak Royal
        23 Sept 2020, 07:23

        Hi there . i have a problem in login and register form that i built in qt ( I am beginner ) . so i wrote a code to prevent inserting duplicate data in sqlite and i write this part of code in buttonClicked of registering.

        if(data.open()){
                QString username,password,email,phone;
                username = ui->line_Edit_username_Registery->text();
                password = ui->line_Edit_password_Registery->text();
                email = ui->line_edit_email->text();
                phone = ui->line_edit_phone->text();
        
                //Query Check
                QSqlQuery queryCheck;
                queryCheck.prepare(QString("SELECT * FROM register where username = :username AND        password = :password AND email = :email AND phone = :phone"));
                queryCheck.bindValue(":username", username);
                queryCheck.bindValue(":password", password);
                queryCheck.bindValue(":email",email);
                queryCheck.bindValue(":phone",phone);
        
        
                bool check = true;
                if ( queryCheck.exec())
                {
        
                    while (queryCheck.next()) {
                        QString usernameDB,passwordDB,emailDB,phoneDB;
                        username_database = queryCheck.value(1).toString();
                        password_database = queryCheck.value(2).toString();
                        email_database = queryCheck.value(3).toString();
                        phone_database = queryCheck.value(4).toString();
                        if(username == username_database || phone == phone_database || email == email_database){
                            QMessageBox::information(this,"Error!","Duplicated!");
                            check = false;
        
                        }
        
                    }
                }// End Query Check
        

        so as you can see i wrote a condition
        if(username == username_database || phone == phone_database || email == email_database).
        to prevent duplicate data and when i insert all four data ( username , password , email , phone ) data won't insert to SQLITE (Duplicate error QMessageBox::information(this,"Error!","Duplicated!"); ).
        but when i for example changing password and then insert it . data is going to insert in database while there is duplicate user name and phone and password.

        how can i solve this problem.

        Whole Code of Button_Clicked Register Form.

        void mainwindow::on_pushButton_register_clicked()
        {
        
           if(data.open()){
               QString username,password,email,phone;
               username = ui->line_Edit_username_Registery->text();
               password = ui->line_Edit_password_Registery->text();
               email = ui->line_edit_email->text();
               phone = ui->line_edit_phone->text();
        
               //Query Check
               QSqlQuery queryCheck;
               queryCheck.prepare(QString("SELECT * FROM register where username = :username AND        password = :password AND email = :email AND phone = :phone"));
               queryCheck.bindValue(":username", username);
               queryCheck.bindValue(":password", password);
               queryCheck.bindValue(":email",email);
               queryCheck.bindValue(":phone",phone);
        
        
               bool check = true;
               if ( queryCheck.exec())
               {
        
                   while (queryCheck.next()) {
                       QString usernameDB,passwordDB,emailDB,phoneDB;
                       username_database = queryCheck.value(1).toString();
                       password_database = queryCheck.value(2).toString();
                       email_database = queryCheck.value(3).toString();
                       phone_database = queryCheck.value(4).toString();
                       if(username == username_database || phone == phone_database || email == email_database){
                           QMessageBox::information(this,"Error!","Duplicated!");
                           check = false;
        
                       }
        
                   }
               }// End Query Check
        
        
               // query insert
               if(check){
                   QSqlQuery query;
                   query.prepare("INSERT INTO register (username, password , email , phone) "
                                 "VALUES (:username, :password, :email , :phone)");
                   query.bindValue(":username", username);
                   query.bindValue(":password", password);
                   query.bindValue(":email", email);
                   query.bindValue(":phone", phone);
        
        
                       bool chkbox = ui->checkBox->isChecked();
                       //Checking checkbox condition
                       if (chkbox){
                           if(query.exec())
                               QMessageBox::information(this,"this","yes");
                           else
                               QMessageBox::information(this,"this","no");
                       }
                       else {
                           QMessageBox::information(this,"title","Please accept the rules");
                       }//Checking checkbox condition
               }
           }
           else{
               QMessageBox::information(this,"this","database didn't connect");
           }
        }
        
        
        G Offline
        G Offline
        Gojir4
        wrote on 23 Sept 2020, 09:03 last edited by
        #2

        @Siamak-Royal said in Qt prevent to insert duplicate data SQLITE Problem!:

        queryCheck.prepare(QString("SELECT * FROM register where username = :username AND password = :password AND email = :email AND phone = :phone"));

        Hi, I don't know SQL, but here password = :password you are selecting only records with specific password, so I guess you retrieve only records from the database with this specific password and not the others, which have different password but same name, email or phone. That's probably why you don't detect the duplicated data in your loop, because you never iterate on them.

        1 Reply Last reply
        1
        • artwawA Offline
          artwawA Offline
          artwaw
          wrote on 23 Sept 2020, 10:20 last edited by
          #3

          Hi,
          have you considered marking a column(s) in the database unique? Having that set any insert will fail - you can examine error type afterwards to eventually display notification to the user. Seems like less work?

          For more information please re-read.

          Kind Regards,
          Artur

          JonBJ 1 Reply Last reply 23 Sept 2020, 12:50
          3
          • artwawA artwaw
            23 Sept 2020, 10:20

            Hi,
            have you considered marking a column(s) in the database unique? Having that set any insert will fail - you can examine error type afterwards to eventually display notification to the user. Seems like less work?

            JonBJ Offline
            JonBJ Offline
            JonB
            wrote on 23 Sept 2020, 12:50 last edited by JonB
            #4

            @artwaw
            I guess OP's username is already primary key(?) One could add email as unique too. I'm thinking that password certainly must not be unique(!), and users may be allowed to share phone numbers....

            artwawA 1 Reply Last reply 23 Sept 2020, 18:32
            2
            • JonBJ JonB
              23 Sept 2020, 12:50

              @artwaw
              I guess OP's username is already primary key(?) One could add email as unique too. I'm thinking that password certainly must not be unique(!), and users may be allowed to share phone numbers....

              artwawA Offline
              artwawA Offline
              artwaw
              wrote on 23 Sept 2020, 18:32 last edited by
              #5

              @JonB Hard to say, OP didn't share the table definition. However, sqlite is very fast backend so if something can be done there I prefer to do it there.

              For more information please re-read.

              Kind Regards,
              Artur

              1 Reply Last reply
              1
              • S Offline
                S Offline
                Siamak Royal
                wrote on 23 Sept 2020, 18:44 last edited by
                #6

                i find the solution . the problem was in selecting table from database and i replace AND by OR and then it checks them one by one .

                thank you .

                1 Reply Last reply
                1

                1/6

                23 Sept 2020, 07:23

                • Login

                • Login or register to search.
                1 out of 6
                • First post
                  1/6
                  Last post
                0
                • Categories
                • Recent
                • Tags
                • Popular
                • Users
                • Groups
                • Search
                • Get Qt Extensions
                • Unsolved